Running a MS Access query from Excel

rmccafferty · August 27th, 2009, 11:03 PM

I need to run an append query in MS Access from Excel.

The research I have done goes on about doing far more complex things in Access from an Excel macro, but not how to just run a query.

I want to use DAO (I think).

I think I start with

dim db as DAO.workspace
dim qry as DAO.queryDef

Then I get really shaky. Maybe something like:
Set db = "C/desktop/richard/rdb.mdb" 'set the name of the Access db
Set qry = "TempRP" 'set the name of the query
Run qry

I have little doubt that this will not work as set out but cannot find in my books or on line how to run a query in Access from Excel.

Am I close? What changes will make this actually work?

Does Access need to be open for this to work?

If I need to first open the Access application, do I need to worry about the standard warning that security is set high and deal with options to say it is okay to trust this database?

Shasur · August 28th, 2009, 01:42 AM

Hi

Can you check if the following gives you some hint:

http://vbadud.blogspot.com/2009/03/h...using-vba.html

Cheers
Shasur

allenm · August 28th, 2009, 06:01 PM

Set reference to Microsoft DAO 3.6 Object Library
You need to know your connection string options which are somewhat finicky but the code would look something like:

Code:

Dim dbConnect As Database, wsEngine As Workspace, rsSource as Recordset
Set wsEngine = CreateWorkspace("ODBCWorkspace","[User Name]","[Password]",dbUseODBC)
Set dbConnect = wsEngine.OpenDatabase("[Name]",[Options],[ReadOnly] -True Or False-,"[Connect]" -The DB connection String-)
Set rsSource = dbConnect.OpenRecordset("Select * From TableName Where Conditions")
Do While Not rsSource.EOF
  msgbox rsSource("FieldName1").value
  ***Whatever else you want to do with recordset***
  rsSource.MoveNext
Loop
rsSource.Close
dbConnect.Close
wsEngine.Close
Set dbConnect = Nothing
Set wsEngine = Nothing

Hope this helps point in the right direction. Be sure to read up on createworkspace command in the office help file.

SheriV · September 14th, 2009, 12:02 PM

I like code in the most simple form possible. Here is a simple version of DAO that I use:

Code:

Dim dba As Database 
Set dba = OpenDatabase("C:\desktop\richard\rdb.mdb") - This opens the database
dba.Execute "TempRP" -Executes the query
dba.close - Closes the database

If you get the security warning you can simply deal with it as is happens or insert a line of code before the set line that reduces the security level. Research the following: msoAutomationSecurityLow

Hope this helps

kiwidancer · September 14th, 2009, 12:39 PM

I have done this recently and was having problems with using DBO .... what I ended up doing was this:
Start recording a macro
Go to your Data tab and Get External Data -> from other sources -> MS Query
go through and specify your data, etc
select post to excel
after it's done doing its thing, stop recording
go into VBA and, if necessary modify the code to get the exact data you're looking for

I'm definitely no Expert in VBA or Excel, but that's what has worked for me :)