Querying MS Access Tables from Within Excel

[HedgePig]

Hello

I would like to perform some simple SQL queries on an Access database from within Excel VBA code. But I haven't a clue on how to start!

For instance, when I try the following :

Sub QueryDataBase()
Dim dbsMyDB As Database
End Sub

I get an error on compilation which says "User defined type not defined." Is this because the "Database" object type is NOT available from within Excel VBA?

Any clues or poiners to sample code which would help me would be greatly appreciated.

By the way, I'm using Excel 97.

Regards
HedgePig

 

[Tommy Bak]

HI
In the vba-editor under TOOLS/REFERENCES
checkmark Microsoft Access x,0 object library

regards Tommy

 

[Ian Mac]

On 2002-08-27 06:21, HedgePig wrote:
Hello

I would like to perform some simple SQL queries on an Access database from within Excel VBA code. But I haven't a clue on how to start!

For instance, when I try the following :

Sub QueryDataBase()
Dim dbsMyDB As Database
End Sub

I get an error on compilation which says "User defined type not defined." Is this because the "Database" object type is NOT available from within Excel VBA?

Any clues or poiners to sample code which would help me would be greatly appreciated.

By the way, I'm using Excel 97.

Regards
HedgePig

Have you tried recording a Macro of you setting up an External Data Query?

If you don't know how to use the Get External Data from the Data menu, let me know and I'll walk you through it.

 

[HedgePig]

Hello Tommy and Ian - thanks for you replies.

Tommy : [Edited for updates] I did go into References and check the Microsoft Access v8.0 Object Library but this hasn't helped.
(Initially i was trying within Excel Add-Ins!)

Ian - thanks for the suggestion. I have tried this and it works fine. However I want to be able to "control" the recordset from within VBA rather than paste the results directly into Excel. And so I need to set up some sort of Database object - I think!

This may be an installation problem which means calling the tech service guys here and hence sevreal years delay. But thanks for your suggestions, guys.

HedgePig
This message was edited by HedgePig on 2002-08-27 07:26

 

[HedgePig]

Problem Solved!

Tommy - thanks for the ToolsReferences hint. I tried checking off a whole lot of likely candidates and it appears that "Microsoft DAO 3.6 Object library" was the one that was missing.

Once again, thank you all for your time and your suggestions.

 

[Tommy Bak]

hi again
sorry to write this (I think i was wrong before) but try again and this time checkmark Microsoft DAO 3,6 object library

edited
I was to late, you found it yourself
This message was edited by Tommy Bak on 2002-08-27 08:03