Run time error?

[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
.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

 

[Jim Thomlinson]

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...

 

[dmac]

I get a compile error, "User defined type not defined" and it highlights
cn As ADODB.Connection

Thanks

 

[Chip Pearson]

I get a compile error, "User defined type not defined" and it highlights

cn As ADODB.Connection

You'll get that if you don't have the ADO library selected in the References
(Tools menu, References).


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[dmac]

I have it checked, now, thanks for the help. I'm still having other problems
now. I'm a little over my head.
Now I get "Application-defined or object defined error"
Any thoughts?

 

[Chip Pearson]

Now I get "Application-defined or object defined error"

That error message, typically with error number 1004, is Excel/VBA's way of
telling you the *something* went wrong without shedding any light on *what*
went wrong. Without seeing the code that is causing the error, it is
impossible to diagnose and provide a fix for the problem.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[dmac]

Here's my code. I'm pulling my hair out trying to get this to work. Thanks.

Sub ExcelToAccess()
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=E:\Databases\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
'multiple fields follow these two
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub