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 = tdf.name
MsgBox "Table name = " & tdf.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(tdf.name, "~") or instr(tdf.name, "msys") then
'ignore and continue to check other names or process info
end if

Using VBA

In Excel for example you can use VB code to manipulate certain things within the rows/columns and objects or even work with external data sources to import data and display it on the sheet. For example here is a simple bit of code to press a button on the sheet, bring up a dialog box where the user inputs a new sheet name and then a log is written in the spreadsheet of when the name was last changed and to what value. Here I am using 2003 Excel.



'New sub routine created with name reflective of the work to be done


Sub ChangeSheetName()


'Define variable to hold input from user


Dim NewName As String


'Firstly populate the variable with the user input then process


NewName = InputBox("Enter the new name of the sheet")


'Check if the variable is empty if so exit the sub
'If the value input by user is not empty then set the active sheet to this value
'Put a line of text in the first cell of the sheet
'then add current date to next cell
'then add the value that the sheet name was set to in the next cell
'in each of those processes do an autofit for the column so that log note
'looks like a sentence


If NewName = "" Then
 Exit Sub
Else
 With ActiveSheet
  .Name = NewName
  .Range("A1").Select
  Selection.Value = "This sheet's name was last changed on the :"
  Columns("A:A").EntireColumn.AutoFit
  Range("B1").Select
  Selection.Value = "=now()"
  Columns("B:B").EntireColumn.AutoFit
  .Range("C1").Select
  Selection.Value = "To: " & NewName
  Columns("C:C").EntireColumn.AutoFit
 End With
End If
End Sub