Exporting from Excel to Access

[dmac]

I am trying to use the code shown below to get information to transfer from
Excel to Access. When I hit "Run", I receive a Run Time Error with this code
'-2147467259(80004005)': Automation Error, Unspecified Error
In my references, I have selected: VBA, Excel 11.0 Object Library, OLE
Automation, MS Office 11.0 Object Library, MS Forms 2.0 Object Library, MS
ActiveX Data Objects 2.0 Library.
I don't know where to choose between ADO and DAO for data import or export,
too.
If anyone has any ideas, please help!

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=L:\Access\CostTracking.mdb;"
Set rs = New ADODB.Recordset
rs.Open "TotalCostSummary", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 2
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew
.Fields(Quote) = Range(A & r).Value
.Fields(Job) = Range(B & r).Value
.Fields(Est) = Range(C & r).Value
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Ralph]

Your references look okay. However you are missing some quotes in you Do
Loop,Fields and Range unless Quote and A are variables?? Also you will want
to add the .Update to save your records.

Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote") = Range("A" & r).Value
.Fields("Job") = Range("B" & r).Value
.Fields("Est") = Range("C" & r).Value
.Update

End With
r = r + 1
Loop

 

[dmac]

Thanks Ralph, I wasn't sure about the quotes.

Your references look okay. However you are missing some quotes in you Do
Loop,Fields and Range unless Quote and A are variables?? Also you will want
to add the .Update to save your records.

Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote") = Range("A" & r).Value
.Fields("Job") = Range("B" & r).Value
.Fields("Est") = Range("C" & r).Value
.Update

End With
r = r + 1
Loop