Run Make table query from Excel

[RW]

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

[K Dales]

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