Compile Error: User-defined type not defined - ADODB

mschmidt14
mschmidt14 used Ask the Experts™
on
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?
Thanks!
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
        
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    Range("A2").Select
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
on which line do you get this error?

Author

Commented:
The first line right after the Sub upload()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long.
Top Expert 2010

Commented:
There are several MS ADO libraries available.  Make sure only one of them is checked.Also, are any references listed as missing?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Can you post a screenshot of your References listing?

Author

Commented:
Here is a listing of my references.  As far as I can see only one ADO library is checked.  
20100705064920957.pdf
Top Expert 2010

Commented:
If you have a more recent version of ADO on your computer (for example, the most recent version I have is 2.8) then try:1) Removing the ADO 2.1 reference2) Adding the most recent ADO library you have in its place3) Debug | Compile the code
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
If that doesn't work, you might try running Component Checker, which will check your MDAC installation on your machine. ADO is a part of MDAC, and it's not uncommon for MDAC installations to get out of whack.

http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9

Author

Commented:
Ok, this is frustrating.  I tried updating to the latest ADO library, still a no go.  I ran the Component Checker and this is what it came with.  MDAC 2.8 SPI on Windows XP SP3.

Any other ideas?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Did you try Patrick's suggestion?

You might also try Compacting the database (make a backup first, just in case).

You might also try building a new, blank database and importing everything into that new database.

Author

Commented:
Yes, I tried Patrick's suggestion.  I also tried compacting the database and I just now tried building a new one and importing everything into it and it is still stopping at that same spot.

The really irritating thing is that I have another program that uses this same code and it runs just fine.  The only difference is where the database resides.  They are on the same server, just in different directories.  That database uses the ADO 2.1 library.

If I thought it would help, I would shake my monitor like an Etch A Sketch.  :-)
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
The server has no importance to the references in your database. It's the workstation that manages the references. Are you running the database directly from the server? If so, then you should not be doing that - you should instead install split it into a Backend (tables only) and a Frontend (everything else), place the BackEnd on the server and relink to the FE to that location. Then, deploy a copy of the FE to each user's workstation. This is a recommended practice even with only one user in the database, simply for performance reasons.

After doing this then move a copy to your workstation and check the refernces there - not on the server.



Author

Commented:
Oh yeah, I know about splitting the database and using only the FE on my machine.  That's what I've done.  I was just saying that this code is working in another project so it doesn't make any sense to me why it isn't working here.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Maybe try a Decompile. Build a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your database" /decompile

Run that and then Compact/Compile/Compact.

Author

Commented:
Well, I tried that and didn't have much luck.  The first part with the decompile went fine.  But then when I went to do the Compact/Compile/Compact, it failed at the Compile step.  I tried doing them separately too and it still failed at Compile.

I feel like we are grasping at straws here.  This is doomed...
Ok, I did a file save as and copied my spreadsheet into the working workbook and used that code...just re-writing to my needs for this project.  So it got past that ADO mess but now I'm not clear on how to finish it.  I don't want to add new records to the table, I just want to be able to edit and save them.  Can that be done the way I am doing it?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'd suggest you close this question out and post a new one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial