Error message: "Compile error. User-defined type not defined"

Hi,

I am trying to get an Excel sheet to read/write to an Access DB. My code starts as shown below.
When running it, I get the error: "Compile error. User-defined type not defined" it will not let me proceed until i have sorted out the "Global cnPubs As ADODB.Connection" line.
I am also guessing that the line below it "Global rsPubs As ADODB.Recordset" will also throw up the same error as it generally doesn't like it.

i initially had an error come up run time erro '429' active X component can't create object and figured out that the right library wasn't selected. In tools-references i now have Microsoft ActiveX data objects (multi-dimensional) 2.8 library is selected and that run time error has now gone away and the above has now happened.

Although I have been doing VB coding for some years now I an brand spanking new to the linking into access thing, I am looking after any compatibility issues with excel 2003 sheets moving onto 2010 as we are about to upgrade and this is one that has thrown up a problem so i thought I would try and fix it, I am regretting that now! Just to confirm I didn't write this and the person that did is no longer with the business.

Any thoughts on how to solve this would be welcome

thanks

Please Login or Register  to view this content.

You need a reference set to the Microsoft ActiveX Data Objects 2.n library (the n is a version number, likely 8)

ah ok, I see what I have been missing now, would that be this bit .provider?

Please Login or Register  to view this content.

No, the provider is separate. The ADO library I mentioned is where the ADODB.Connection (and ADODB.Recordset) comes from.

ok, sorry for being a bit slow on this, I am regretting even touching this now but I am being stubbourn and I WILL fix it

where and how would i put that in, would it be in the string I have mentioned, in front of the Global cnPubs As ADODB.Connection

Please Login or Register  to view this content.

or would i put it somewhere in the body of the VB, shown below

Please Login or Register  to view this content.

Open VBE. Go to Tool->References and check Microsoft ActiveX Data Objects 2.n Library (the n is a version number, likely 8)
That's what romperstomper is telling you in his first reply.

Buran

And please use CODE tags around your code. Not doing so is against forum rules.

That is not the same reference. You want:
Microsoft ActiveX Data Objects 2.8 Library
and not:
Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 library.

where does the runtime error occur?

Do you actually have Access installed?

If the code errors there, how does it open fine?

Sounds like a configuration problem on your machine - see if this MSKB article helps. I assume it is failing on the first instance of that line?

No, you weren't.