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