Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,
    When doing a one-off large task, I regularly create a number of queries with names like:

    Q:01 Create Table
    Q:02 Input Rates
    Q:02a Fix rates
    Q:03 Insert totals
    etc
    etc

    Normally I just run them in sequence, using the mouse. If they are very slow, I'll create code that runs them sequentially till I get them all right.

    Is there a way, in code, to run all the queries between Q:000 and Q:999 in alphabetical order?

    Don't need the whole code written, a pointer in the right direction should do.

    Thanks
    Jim
    Shetland Isles

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can open a recordset on the following SQL statement:

    SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Like "Q:##*" AND MSysObjects.Type=5 ORDER BY MSysObjects.Name

    Loop through the records of the recordset and use the Name field torun the query of that name.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    You can open a recordset on the following SQL statement:

    SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Like "Q:##*" AND MSysObjects.Type=5 ORDER BY MSysObjects.Name

    Loop through the records of the recordset and use the Name field torun the query of that name.

    Hi,

    I do understand how the above line works & I thought I could write the surrounding code but I'm afraid it's beyond me.

    Any help to get started would be much appreciated, even a link to where I could learn.

    Thanks
    Jim MacLeod
    Shetland Isles

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Something like this. It requires a reference to the Microsoft DAO 3.6 Object Library.

    Code:
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT MSysObjects.Name FROM MSysObjects " & _
      "WHERE MSysObjects.Name Like 'Q:##*' AND " & _
      "MSysObjects.Type=5 ORDER BY MSysObjects.Name", dbOpenForwardOnly
    
    Do While Not rst.EOF
      dbs.Execute rst!Name, dbFailOnError
    rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Fantastic!

    And I think I understand it!!

Posting Permissions

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