Insert Excel Array Into Access Table

[goss]

Hi all,

Using Excel 2010.

I would like to load an Excel Array into an Access Table
Code below bombs out, bebugger points here:

 .Open "tblTransactions", CurrentProject.Connection, acDynamicCursor, acLockOptimistic

Error is

Run-time error '424': Object required

Not sure what the problem is?
The path and database are correct.
The name of the table, "tblTransactions" is correct
The database is closed and not locked

Thanks,
goss
Full code:

Sub Excel2AccessArray()
     '**********************************************************************
     'Author: goss
     'Date: 08/20/2011
     'Purpose: Insert Records From Excel To Access Using Array
     'Reference: Microsoft ActiveX Data Objects 2.5 Library
     '**********************************************************************
     
    Dim cnt As ADODB.Connection
    Dim rec As ADODB.Recordset
    Dim stSQL As String
    Dim stCon As String
    Dim stDB As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strDb As String
    Dim MyTimer As Double
    Dim strTempAry As Variant
    Dim lngRows As Long
    Dim lngCols As Long
    Dim lngCount As Long

    MyTimer = Timer

    'Assumes data resides in this workbook
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    
    'Database
    strDb = "c:\Data\Nwind.mdb"

    '***********************************************
    'Find last row and last column
    '***********************************************

    'Last row on ws (Assumes data in Col A)
    lngRows = ws.Cells(Rows.Count, 1).End(xlUp).Row

    'Last column on ws(Assumes header in row 1)
    With ws
        lngCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    '***********************************************
    'Named Range - Header
    '***********************************************
    
    'Add dynamic named range for headers
    With ws
        strStart = .Cells(1, 1).Address 'Assumes header begins at A1
        strEnd = .Cells(1, lngCols).Address
    End With

    wb.Names.Add Name:="lstHeadings", RefersTo:= _
    "=" & strStart & ":" & strEnd

    '***********************************************
    'Named Range - Data
    '***********************************************

    'Add dynamic named range for records
    With ws
        strStart = .Cells(2, 1).Address  'Assumes data begins at A2
        strEnd = .Cells(lngRows, lngCols).Address
    End With

    wb.Names.Add Name:="tblRecords", RefersTo:= _
    "=" & strStart & ":" & strEnd
    
    '***********************************************
    'Load Array From Named Range
    '***********************************************

    strTempAry = Range(ActiveWorkbook.Names("tblRecords").RefersToRange.Address)
    lngCount = UBound(strTempAry) 'Record count
    
    '***********************************************
    'Insert Array Into Access Table
    '***********************************************
    'Connection String
        stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDb & ";"
    
    'Set Connection Variables
        Set cnt = New ADODB.Connection
        Set rec = New ADODB.Recordset

    'Open Connection To Access Database
        With cnt
            .Open stCon
            .CursorLocation = adUseClient
        End With
        
    'Add Array To Database Table
        With rec
            .Open "tblTransactions", CurrentProject.Connection, acDynamicCursor, acLockOptimistic
            .AddNew Array(strTempAry)
            .Update
            .Close
        End With
     
    'Tidy Up
        cnt.Close
        Set cnt = Nothing
        Set rec = Nothing
        Set wb = Nothing
        Set ws = Nothing
        MsgBox Timer - MyTimer
End Sub

 

[Derek Brown]

Have you tried using "cnt" instead of "CurrentProject.Connection"?

 

[goss]

Thanks Derek,

I tried to replace CurrentProject.Connection with cnt and received this error:

Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

 

[Derek Brown]

I was giving a quick reply and did not notice that you were using Office 2010 and a .mdb database.
I now only work with the .accdb database, so don't have the precise answer - only a few ideas.
Below is the skeleton of the code that I use to add new records:

'The following code, writes a new record to an Access 2007/2010 (.accdb) database.

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim objRS_Clone As ADODB.Recordset
Dim strDatabase As String
'
strDatabase = "My Database.accdb"
'
Set objConn = New ADODB.Connection
objConn.Provider = "Microsoft.ACE.OLEDB.12.0"
objConn.Open strDatabase
'
Set objRS = New ADODB.Recordset
' Select ALL records before adding new record:
objRS.Open "SELECT [MyTable].* FROM [MyTable];", objConn, adOpenKeyset, adLockOptimistic
' Add record:
objRS.AddNew
objRS.Fields("MyField1") = strMyString
' etc.
objRS.Update
'
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

I don't know whether you need to use the same 'Provider' or the reference to 'Jet' that you used.
You will also notice that I am updating the fields individually, so cannot advise you regarding using an 'array'.
You may find the following artical of interest:
http://msdn.microsoft.com/en-us/library/ff965871.aspx
Hopefully an Access expert may notice this post and give further advice.

 

[Norie]

Are you sure they array has the correct dimensions and that the items in it are if the correct data type?

PS Why are you using Array? strTempAry is already an array.

 

[goss]

Hi Norie,

I passed a named range to the array
The arraydata type is variant
I ran a few tests to see if the array was loaded as expected UBound returned 30,000.
These tests returned the correct results

Debug.print strTempAry(1,1)
Debug.print strTempAry(1,2)
Debug.print strTempAry(1,3)
Debug.print strTempAry(1,4)
Debug.print strTempAry(1,5)

So it seems the array is loaded correctly now I'm trying to figure out if I can do a BULK INSERT or orther to load the entire array to an Access database table rather than loop through the array.

 

[Norie]

So the original code is now working?

 

[goss]

Hi Norie,

Not quite working
I've loaded the array.
I'm try to figure out if I can load the entire array to a table in the database w/o looping through each record/element of the array.

Something like

INSERT strTempAry INTO tblTransactions

Where tblTransactions is a predefined table with 5 fields

 

[Norie]

goss

What is it you are trying to do?

If it's just transfer data from Excel to Access there are other, perhaps easier, ways to do it.

If you are trying to see if it can be done using arrays then using SQL could work.

You'd need code to construct the correct SQL statement using the arrays.

That could be done but might not be too straightforward.

 

[xenou]

"bulk-loading an array" into a database is not something I've ever seen precisely as such. As Norie said, other methods are more typical: TransferSpreadsheet from Access would be one. Or writing to a text file as an intermediate step and loading from the text file. XML is a new option - if you created an XML document instead of an array you could pass the XML to your database. Or as mentioned, pass the array to your DB and script the inserts from it.