Running a query in Access from Excel

[sspicer]

I know there are similar queries to this already in the forum, but I still can't find the answer to my specific query.

I want to be able to use a button in excel to run a query in access. This cannot be done via putting code into excel and running due to the fact that I have to do this several different times.

Does anyone know what VBA would be needed in order to run a query already written in Access?

Many thanks

 

[RoryA]

This cannot be done via putting code into excel and running due to the fact that I have to do this several different times.

You lost me here. How can you have a button in Excel to do this if you can't put code in Excel?

 

[Richard Schollar]

Hi

The following uses DAO to connect to the Access db. It runs a query with the name qry_FullConsolidated and returns the data to A2 on the Activesheet:

Sub ExecuteQuery()
Dim rst As DAO.Recordset, db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = OpenDatabase("Y:\Work\Index Data\IndexData.mdb")
Set qdf = db.QueryDefs("qry_FullConsolidated")
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Range("A1").CopyFromRecordset rst
End Sub

You must set a referemce to the Microsoft DAO Object library in the VBE (via Tools>References) before you can use this.

Amend database name and path and query name to suit.

Native Access functions such as Nz() in your existing query 9or UDFs you have created yourself) will prevent this from working.

 

[sspicer]

Thank you Richard! That is perfect.