Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Compile Error: User-defined type not defined - ADODB

Avatar of mschmidt14
mschmidt14 asked on
Microsoft Access
16 Comments1 Solution648 ViewsLast Modified:
I am trying to upload an Excel spreadsheet into an Access table and I'm getting this error.   I have researched this error and everything says to make sure the MS ActiveX Data Objects Library are checked.  I looked, it is.

Here is my code, can you see if there is some other reason I would be getting this error?
Sub upload()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=\\jxeweb\PACLease\PacLeaseDatabase.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "tblDailyList", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .Fields("UnitNo") = Range("A" & r).Value
            .Fields("Type") = Range("B" & r).Value
            .Fields("Color") = Range("C" & r).Value
            .Fields("Year") = Range("D" & r).Value
            .Fields("Model") = Range("E" & r).Value
            .Fields("Odom") = Range("F" & r).Value
            .Fields("CustomerName") = Range("G" & r).Value
            .Fields("LeaseRateNotes") = Range("H" & r).Value
            .Fields("Insurance") = Range("I" & r).Value
            .Fields("1") = Range("J" & r).Value
            .Fields("VIN") = Range("K" & r).Value
            .Fields("Class") = Range("L" & r).Value
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Set rs = Nothing
    Set cn = Nothing
End Sub
Avatar of mschmidt14

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 16 Comments.
See Answers