mschmidt14
asked on
Compile Error: User-defined type not defined - ADODB
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!
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
on which line do you get this error?
ASKER
The first line right after the Sub upload()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long.
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long.
There are several MS ADO libraries available. Make sure only one of them is checked.Also, are any references listed as missing?
Can you post a screenshot of your References listing?
ASKER
Here is a listing of my references. As far as I can see only one ADO library is checked.
20100705064920957.pdf
20100705064920957.pdf
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
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
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9
ASKER
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?
Any other ideas?
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.
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.
ASKER
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. :-)
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. :-)
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.
After doing this then move a copy to your workstation and check the refernces there - not on the server.
ASKER
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.
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.
"full path to msaccess.exe" "full path to your database" /decompile
Run that and then Compact/Compile/Compact.
ASKER
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...
I feel like we are grasping at straws here. This is doomed...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd suggest you close this question out and post a new one.