Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I pass an SQL statement to a select query?

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can I pass an SQL statement to a select query?

    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.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I pass an SQL statement to a select query?

    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

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I pass an SQL statement to a select query?

    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

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    Beware of the MSys tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •