Creating a Query Def

To create a QueryDef, execute the CreateQueryDef method against the Jet Database object, or in the case of ODBCDirect workspaces, the Database or Connection objects.

In Microsoft Jet workspaces, if you set a QueryDef's Name property to something other than a zero-length string, it is automatically appended to the QueryDefs collection, and saved to disk. Omitting the Name property, or explicitly setting it to a zero-length string, will result in a temporary (unsaved) QueryDef.

In ODBCDirect workspaces, QueryDefs are always temporary, and so, are unsaved. In ODBCDirect, the QueryDefs collection only ever contains open QueryDefs, which when closed, are automatically removed from the collection.

The following code demonstrates how to create a QueryDef in a Microsoft Jet workspace:

Public Sub CreateQuery (strName As String, strSQL As String) Dim dbs As Database Dim qdf As QueryDef

Set dbs = CurrentDb

'Create the QueryDef

'If the user supplies a name, the QueryDef will be 'automatically appended to the QueryDefs collection Set qdf = dbs.CreateQueryDef(strName, strSQL)

Set qdf = Nothing Set dbs = Nothing End Sub

You can create a pass-through query to an ODBC data source by setting the QueryDef's Connect property to a valid connection string, after the query has been created.

qdf.Connect = strConnectionString

0 0

Post a comment