copying contents of an excel vba array into existing access database table

[mp3909]

Hi,



I have a spreadsheet with the following sample data:



I have managed to populate this data into an array.
I have also managed to connect to my access database using excel vba.
Please note I have purposely left column B (Age) blank because my real data set which consists of thousands of records and many columns will also have blank fields.
All I am trying to do right now is get the principle working which is to populate the data into an array and then insert the whole contents of the array into an existing access database table without using loops because the actual data set is huge and looping will be inefficient.

This is my code so far:

Sub test()

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset


Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset


Dim conStr As String
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\mfil\proddfs\AppSense\Profiles\Users\patma01\Desktop\sample.accdb;"
oConn.Open conStr


Dim x As Variant
x = ThisWorkbook.Sheets(1).UsedRange


With rs
    .Open "Table1", oConn, adOpenDynamic, adLockOptimistic
    .AddNew Array(x)     ----------------------------------------------------------> this is where my code breaks 
    .Update
    .Close
End With




oConn.Close
Set oConn = Nothing
Set rs = Nothing


End Sub

As per my comment in the code above, this is the error message I get:




Please can you help.
Thank you in advance.

[ArviLaanemets]

Much easier way:
Link your Excel table into Access Database;
Run a procedure in Access, which reads data from linked table, makes any wanted transformations when there is a need for this, and then inserts correct data into Access DB tables (NB! Linked Excel table(s) are used only for data import!).

In case import process must be repeated in future, you leave linked table present in Access DB, and data there will be updated manually, or copied over, or the Excel file is copied over with new one with same table structure, using name which was used by linked table earlier. In case the process is used only once, to delete the linked table afterwards.

A good idea will be to have the Access import procedure checking that imported data are really new - e.g. using queries like (on fly - I'm not sure about exact syntax, as for years now I use SQL Server as BE) Insert Into AccessTable at (FieldList) SELECT let.FieldList FROM LinkedExcelTable let LEFT JOIN AccessTable at0 ON at0.SomeFields = let.SomeFields WHERE at0.PrimaryKey Is Null