I need data from Access. The Access query is based on a table that must be
created first using a Make Table query. How do I run this query from Excel
VBA?

I tried:
200 Set db = OpenDatabase(Name:=strDbName1, _
Options:=False, ReadOnly:=True)
Set qry = db.QueryDefs(strQryName1)
Set rs = qry.OpenRecordset(dbOpenSnapshot)
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

but it fails at:
Set rs = qry.OpenRecordset(dbOpenSnapshot)

I can get the 2nd query to run when the table is created within Access.
However, I need to do all of this on the Excel side.

Thanks

To run an Action query in DAO you should use the Execute method of the
QueryDef, not OpenRecordset (since no recordset is opened).

ref:
http://msdn.microsoft.com/archive/de.../D2/S5A270.asp
--
- K Dales


"RW" wrote:

I need data from Access. The Access query is based on a table that must be
created first using a Make Table query. How do I run this query from Excel
VBA?

I tried:
200 Set db = OpenDatabase(Name:=strDbName1, _
Options:=False, ReadOnly:=True)
Set qry = db.QueryDefs(strQryName1)
Set rs = qry.OpenRecordset(dbOpenSnapshot)
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

but it fails at:
Set rs = qry.OpenRecordset(dbOpenSnapshot)

I can get the 2nd query to run when the table is created within Access.
However, I need to do all of this on the Excel side.

Thanks