Compile Error involving Microsoft ActiveX Data Objects 2.x Library

MJester

New Member
Joined
Jan 6, 2011
Messages
2
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
In the Visual Basic Editor, Tools > References and tick the missing library then click OK.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jan 6, 2011
Messages
2
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.
 

Forum statistics

Threads
1,141,707
Messages
5,707,983
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top