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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
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
76,305
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
76,305
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.
 

Forum statistics

Threads
1,144,161
Messages
5,722,838
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top