Automatically update query design based on table names

wsteinbe

Board Regular
Joined
Feb 16, 2006
Messages
58
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Why not dynamically create the queries using VBA?
 

wsteinbe

Board Regular
Joined
Feb 16, 2006
Messages
58
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Take a look at CreateQueryDef.

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

wsteinbe

Board Regular
Joined
Feb 16, 2006
Messages
58

ADVERTISEMENT

Awesome, this will work perfectly. Thanks!
 

wsteinbe

Board Regular
Joined
Feb 16, 2006
Messages
58
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:
Code:
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)
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,153
Members
410,666
Latest member
forzasec
Top