CODEWITHSUNDEEP
ms-access
Joanne Cho
Joanne Cho

Reputation: 9

Export Excel Range to Access table VBA

I want to have a button on the Excel spreadsheet and have the data copied to the Access table. The range is an auto-populated field from another sheet in the same workbook.

I tried few codes to make this happen, but I either get an error 1004: application-defined or object-defined error, or no error but data not being copied in Access DB.

My code is copied below. 

Sub Export_Data()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset 
    Dim dbPath
    Dim x As Long, i As Long

    dbPath = "H:\RFD\RequestForData.accdb"

    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    Set rst = New ADODB.Recordset 'assign memory to the recordset

    rst.Open Source:="tblRequests", ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable

    rst.AddNew
    For i = 1 To 13
        rst(Cells(1, i).Value) = Cells(x, i).Value
    Next i
    rst.Update

    rst.Close
    cnn.Close

    Set rst = Nothing
    Set cnn = Nothing

    MsgBox " The data has been successfully sent to the access database"

    Set rst = Nothing
    Set cnn = Nothing

End Sub

Upvotes: 0

Views: 1830

Answers (1)

Sergio Prats
Sergio Prats

Reputation: 1213

Looking at your Subroutine I see two things that can make it not to work:

  • rst(Cells(1, i).Value) = Cells(x, i).Value <- Where is 'x' initialized?

  • There is only one loop that moves over the fields but I think it should be another loop for the rows in the Excel.

With this two changes, the loop when the records are save could become something like this:

    For x = 1 TO lastRow   ' Last row has to be calculated somehow
        rst.AddNew
        For i = 1 To 13
            rst(Cells(1, i).Value) = Cells(x, i).Value
        Next i
        rst.Update
    Next x

Hope it helps.

Upvotes: 0

Related Questions