Search code examples
vbams-accessdao

Create a passthrough QueryDef with DAO and a ODBC-Connection by VBA

My goal is to create a QueryDef by vba, that is the same as I can create in the GUI.

This are the Steps, that I use in the GUI:

  1. Create the query
  2. Set it as passthrough
  3. Set the ODBC connection string
  4. Enter the statement

enter image description here

How can I set this by VBA and use the queryDef as RecordSource in the current form?

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("test", "select * from test;")

'passthrough?
'connection string?
'set as RecordSource?

I found out, that there is a Properties-Collection. When I investigate a GUI-made query, I see that there the connection-String is in item 6:

Debug.Print qdfTmp.Properties.Item(6).Name
> Connect

You can find the other setting alos, but I do not know how to set them with VBA.

Solution

  • The easy method is to create and check a PT query, then save it, reserve it for this form, and set the form to have this query as its RecordSource.

    To use it, all you need is to adjust the SQL as needed:

    Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("ThisFormPTQuery")
qdf.SQL = "select * from test;"

    and then open the form.

    Edit:

    To create a copy of a "master" query:

    DoCmd.CopyObject , "NewQuery", acQuery, "MasterQuery"