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
.Fields(Materials) = Range(D & r).Value
.Fields(MaterialAct) = Range(E & r).Value
.Fields(BrokerTruck) = Range(F & r).Value
.Fields(BrokerAct) = Range(G & r).Value
.Fields(CarusoTruck) = Range(H & r).Value
.Fields(CarusoTruckAct) = Range(I & r).Value
.Fields(CarusoDriver) = Range(J & r).Value
.Fields(CarusoDriverAct) = Range(K & r).Value
.Fields(Labor) = Range(L & r).Value
.Fields(LaborAct) = Range(M & r).Value
.Fields(OwnedEquip) = Range(N & r).Value
.Fields(OwnedEquipAct) = Range(O & r).Value
.Fields(RentedEquip) = Range(P & r).Value
.Fields(RentedEquipAct) = Range(Q & r).Value
.Fields(SrvcsSub) = Range(r & r).Value
.Fields(SvcsSubAct) = Range(S & r).Value
.Fields(BondCost) = Range(T & r).Value
.Fields(TotalCost) = Range(U & r).Value
.Fields(TotalAct) = Range(V & r).Value
.Fields(Profit) = Range(W & r).Value
.Fields(ContractAmt) = Range(X & r).Value
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub