Compile Error involving Microsoft ActiveX Data Objects 2.x Library

[MJester]

I am trying to use VBA to pull data from Access into Excel using MS Query. I found a good bit of code to use in doing this here: http://www.excelguru.ca/node/23

However, when I paste this code into my module and then make the path modifications to use the database I'm working with, I get an error when I run the code. The error is: "Compile Error: User-defined type not defined" and it highlights the first dim statement "Dim cnt As New ADODB.Connection". If I take out that statement, it also doesn't like the second.

I'm not really sure how to check for Microsoft ActiveX Data Objects 2.x Library that is apparently necessary for the code to work.

I'm running Excel 2000.

Any help would be greatly appreciated.

 

[VoG]

In the Visual Basic Editor, Tools > References and tick the missing library then click OK.

 

[ChrisM]

Hello, you need to go into your VBA editor, go up to Tools, then References and add the ADO file.

However, based on the tone of your post, you are new to this type of coding, in which case I would recommend not using ADO for this. Excel already has built in tools to extract data from Access, without using VBA. Have you explored these already? Under the Data,Get External Data menu options?

Just run that menu command, point to your database, select your table or query, tell Excel where to put the results, click button and your data is there.

Later, right click anywhere inside the data and choose "refresh" to re-query.

 

[MJester]

Thanks for the help.

I found out that I cannot have any spaces in my table names in order for this to work. Once I changed all the spaces to underscores, it worked just fine.