Sunday, 29 April 2012

Access Look Through Query Code

Generally I just post topics where I have been doing some research for work and self-learning purposes so today we will look at how to manipulate the SQL code of an Access Database Query and also how to change/view table names in VBA code. While working as an IT developer I had to do the following:

Replicate a database and change all the table names in queries to suit the new requirements; the code was to stay the same but only the data sources were to change.
In this instance a lot of the queries referenced a linked table and so by copying the DB I have to take the old table offline, clear the data and then create a new link to the other table; I cannot use the old table reference.

If you have a local table (not linked) in the Access database and you rename it, the renamed table will appear in all queries that reference it.

So with a linked table even when you delete it it will still appear in the query design, but the query will usually exhibit a "can't represent the join expression between LinkedTable.ID = LocalTable.ID" when you go to design view.

To get round this go into the visual basic editor (Usually tools > macro > visual basic editor in 2003 Access or Developer ribbon > visual basic editor in 2007/10 Access)

Create a new module, or use an existing module and enter the code below to get a taster of what you can do:

Simply create a new subroutine with any name you like - respecting design rules of course

Public Sub TableNamesInQueries()
Dim qdf As QueryDef
Dim sqlCode As String
Dim db As Database
Set db = CurrentDb()

For Each qdf In db.QueryDefs
sqlCode = qdf.sql
MsgBox "Query code = " & qdf.sql
Next qdf

End Sub

So here you can loop through each query and display the code in a mesage box. If you want to manipulate the code and say replace the table name you can use this in place of the message box:

If InStr(sqlCode, "Dates") Then
qdf.sql = Replace(sqlCode, "Dates", "New_Table_Name")
msgbox "New Code = " & qdf.sql
End If

 It is best to put a message box in at first to display your new SQL code so that you are confident that what you are designing is performing exactly the changes you want. Then when you are confident it is correct you can run the code with out performing a check every time.

You can also do a similar bit of code if you want to rename local table names just change it to:

Public Sub tableNames()
Dim tdf As TableDef
Dim tblName As String
Dim db As Database
Set db = CurrentDb()

For Each tdf In db.TableDefs
tblName =
MsgBox "Table name = " &
Next tdf

End Sub
 You can also exclude tables with system names and also linked tables if you want to only loop through local tables.

To exclude linked tables check for a connection string larger than 0, which is what a linked table will have:

If Len(tdf.connect) > 0 then
'Ignore this table
End if

To exclude system tables and temp tables check for "msys" or "~" characters in the name:

if instr(, "~") or instr(, "msys") then
'ignore and continue to check other names or process info
end if