First off in the VBE select Debug - Compile to see if your code compiles (I
suspect it does but to just to be on the safe side). Assuming all goes well
with the compile then place your cursor anywhere in the procedure and start
stepping through the code using F8. This will allow us to determine which
line is bombing... Post back with your results...
--
HTH...

Jim Thomlinson


"dmac" wrote:

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