ADO From Excel to Access

[happyhungarian]

Hi all,

I've been using the following code to send data from my excel workbook to an access database. The thing I wanted to change about it (and hopefully you all can help) is that currently it sends data from the currently active excel sheet. I would like to add a line of code in there where I can tell it what sheet sheet to pull from.

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\saundersj\Desktop\test.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Aircraft_Delivery", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 8 ' the start row in the worksheet
Do While Len(Range("t" & r).Formula) > 0
With rs
.AddNew ' create a new record
.Fields("Delivery Date") = Range("t" & r).Value
.Fields("Award/Revision") = Range("u" & r).Value
.Fields("Opportunity") = Range("v" & r).Value
.Fields("Deliveries") = Range("w" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Norie]

How are you going to tell the code which worksheet to work with?

An inputbox or some other method?

 

[happyhungarian]

I would just write it into the code like I do with all the field and table names.

 

[Norie]

Well that should be quite straightforward.

Try this.

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim wsData As Worksheet
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=C:\Documents and Settings\saundersj\Desktop\test.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "Aircraft_Delivery", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 8    ' the start row in the worksheet
    Set wsData = Worksheets("AWorksheet")    ' change name of worksheet here to the one
                                                               ' you want to export data from into Excel
    With wsData
        Do While Len(.Range("t" & r).Formula) > 0
            With rs
                .AddNew    ' create a new record
                .Fields("Delivery Date") = .Range("t" & r).Value
                .Fields("Award/Revision") = .Range("u" & r).Value
                .Fields("Opportunity") = .Range("v" & r).Value
                .Fields("Deliveries") = .Range("w" & r).Value
                .Update    ' stores the new record
            End With
            r = r + 1    ' next row
        Loop
    End With
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Sub

 

[happyhungarian]

Excellent! One more question... I have this code in two seperate modules so that i can send data from seperate sheets to seperate tables in my database... I would like to string both together and have them both reside within one module so that both data sets would get sent with one click of a macro button (i know i could just create a seperate code to run the two modules sequentially but i'm afraid that might mess up auto-timestamps i have set up in my access tables). but i have been unable to figure out how to string them together without it erroring out because of multiple attempts to create a connection

 

[Norie]

I don't think you need multiple connections, or recordsets.

Just open a connection, open a recordset for the first set of data, close the recordset once finished.

Then re-open the recordset, with the arguments needed for the second set of data, transfer the data, close the recordset.

Now that you've finished with both sets of data close the connection.

 

[happyhungarian]

Actually I am running into an issue with the code your provided... It says "Method or Data Member Not Found" and highlights the .Range in:


.Fields("Delivery Date") = .Range("t" & r).Value