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

 

[Norie]

xenou

I'm pretty sure there isn't anything like BULK INSERT in Access SQL if that's what you were thinking.

 

[goss]

I saw BULK INSERT in TSQL while I was searching for help on Google
I have the Insert working from a named range and it is fast
I'm just trying to figure our how to do it with an array to expand my tool set

Are there other databases that will only take an array and not a name range from Excel?

 

[xenou]

Sorry, got me on this one. I don't think its a typical Excel-Access thing. I *think* have seen some kind of TSQL thing where you reference an Excel sheet as a "table" but it's really hazy right now and that's not Access anyway (nor is it arrays).

 

[Norie]

Access doesn't really do T-SQL.