Automatically update query design based on table names

[wsteinbe]

I have some vba code right now that uses an ifexist module (it checks to see if a file exists) if it does, it imports it. The files are all named Extra1 - Extra10, and are always in order. So if there are 3 files, they will be named Extra1, Extra2, and Extra3.

Here is my issue, I have a cross-tab query that uses a field from each of the Extra tables as a row heading. The problem is I have to manually go in and delete the extra tables from the query every time depening on how many Extra tables there are. And if there are more this week than last week I have to add them in.

I have 135 queries that run every week and I would like to streamline this. Any ideas?

 

[Norie]

Why not dynamically create the queries using VBA?

 

[wsteinbe]

I would love to... don't know how... is there anything in the help files? BTW due to company restrictions I have to use Access97.

 

[Norie]

Take a look at CreateQueryDef.

I'm pretty sure that was available in Access 97.

 

[wsteinbe]

Awesome, this will work perfectly. Thanks!

 

[wsteinbe]

There might be an easier way to do this, but this is what I got to work:
I created a button with the event procedure:

Private Sub Command0_Click()
Call NewQuery
End Sub

Then I have my IfExist script that checks for the file, then depending on where it stops (i.e. when IfExist=False) it will then use the code below (This is if it can't find file 4)

Sub NewQuery()
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT TableAll.ID, Table1.Extra1, Table2.Extra2, Table3.Extra3 FROM Table1 INNER JOIN (Table2 INNER JOIN (Table3 INNER JOIN TableAll ON Table3.Name = TableAll.Name) ON Table2.Name = TableAll.Name) ON Table1.Name = TableAll.Name;"
    Set qdf = db.CreateQueryDef("Whatever I wanna Call it", strSQL)
    DoCmd.OpenQuery qdf.Name
    Set db = Nothing
End Sub

So hope that helps if anyone searches for this.

 

[Norie]

Thanks for posting your solution.

One thing you might want to consider is how you construct the SQL string.

I would probably use something like this.

strSQL = "SELECT TableAll.ID, Table1.Extra1, Table2.Extra2, Table3.Extra3 "
    strSQL = strSQL & "FROM Table1 INNER JOIN (Table2 INNER JOIN (Table3 INNER JOIN TableAll ON Table3.Name = TableAll.Name) ON Table2.Name = TableAll.Name) ON Table1.Name = TableAll.Name;"

In this particular case it doesn't really make a lot of difference, but if the query was more complicated doing it this way it can help.