Can I pass an SQL statement to a select query? (Access 2003 / SP1)

Topic

New Reply

I have a simple select query that can be run against dozens of identically structured tables. Its purpose is to display table data on the screen. (It

Reply | Quote

Replies

VBA will be involved at some point, I don’t see how this could be done without any VBA.

Reply | Quote

You must have misunderstood my question. I have no problem writing VBA code. I’ve been doing so since Access Version 1. What I’m looking for is a technique, if there is one, to pass SQL (or any other way to define what source table to use) to a pre-defined select query. (I know that I can Open a recordset and display the rows using a data sheet sub-form, but that is to complicated. I simply want to use a query to display the data.)

Thanks, John

Reply | Quote

Sorry about that, I thought you wanted to bypass VBA altogether.

You can use DAO code; you must have a reference to the Microsoft DAO 3.6 Object Library. In the following, qryMyQuery is the name of a pre-defined select query.

Dim strSQL As String
strSQL = ” SELECT … ”
CurrentDb.QueryDefs(“qryMyQuery”).SQL = strSQL

You can also retrieve the current SQL text, and use the Replace function to replace the table name with a different one, then set the SQL property.

Reply | Quote

Hans,
HooBoy. I haven’t thought about querydefs in years. Consequently, of course, I’d completely forgotten about query properties. (Hey, I’m retired. I forget things.) It looks like the example you’ve given me is exactly what I am looking for. Now for all the querydef reading I can find.

Thanks, John

Reply | Quote

John, I’d try to do this from a form with a multi select list box, selecting the table names from the list box, and modifying your SQL in VBA. To populate a list box with table names, use this code: (Assuming your list name is lstTables)

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim tdf As TableDef

Set db = CurrentDb

For Each tdf In db.TableDefs
lstTables.AddItem tdf.Name
Next tdf

End Sub

Can you post an example of your SQL?

Reply | Quote

Jeremy,
In addition to forgetting all about querydefs, I’d forgotten all about tabledefs also. Thanks for the reminder and for pointing me in yet another direction of reading.

John

Reply | Quote

Beware of the MSys tables.

Reply | Quote