0

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.

testo
  • 1,052
  • 2
  • 8
  • 24
  • https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-query/access-vba-how-to-create-a-query-programmatically/ – braX Oct 29 '19 at 08:39
  • 1
    Possible duplicate of [SQL Server Passthrough query as basis for a DAO recordset in Access](https://stackoverflow.com/questions/17241898/sql-server-passthrough-query-as-basis-for-a-dao-recordset-in-access) – Erik A Oct 29 '19 at 08:55
  • 3
    ^^ Setting `.Connect` turns the querydef into a Pass-Through query. – Andre Oct 29 '19 at 09:24
  • Yes, looks like a duplicate. But the given answer is unique. – testo Oct 29 '19 at 10:18

1 Answers1

0

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"
ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I thought about that but the Problem is, to reserve it for the form. Is there a way to use the predefioned query as blueprint for a new one? – testo Oct 29 '19 at 09:14
  • 1
    Shouldn't it be: Set qdf = CurrentDb.QueryDefs("ThisFormPTQuery") to get the prdefined query? – testo Oct 29 '19 at 09:54
  • @doev: Certainly. Thanks. I've corrected the code line. – Gustav Oct 29 '19 at 11:04
  • Working example of create and run Passthrough Query .[link](http://www.utteraccess.com/forum/index.php?showtopic=95209) – xShen Oct 31 '19 at 22:37