Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the field
names.
Thanks for any advice.

RBS

OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the field
names.
Thanks for any advice.

RBS

You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

No, that won't worked without access. I assumed you were just doing that to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

So how do I show the .mdb file if Access isn't installed?
I suppose I could show it as text, but then I might as well dump the array
to text.

RBS


"Tom Ogilvy" wrote in message
...
No, that won't worked without access. I assumed you were just doing that
to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS