VB to update an Access Query

[brendanolear]

I have a current Access Query which produces a table of 500 different companies, and thereafter has columns Week 01 to Week 53 with a Sum(IIF( formula that captures all "Sales" in any given week for each.

I wish to copy the query and replace "Sales" in all 53 columns with "Contacts" but only way I know how to do is manually across all 53 columns.

Would a VB script allow, or any other process to quickly change.

Thank You

 

[boblarson]

You can use a querydef to do this. Let's say you save your SQL strings into a memo field in a table along with an ID field. Let's say the table name is tblSQLStrings and the fields are SQLID (autonumber PK) and sSQL (Memo), this is something you could do (you would put this function into a STANDARD module and not a form, report or class module and you would name the module something other than the function name):

Function SwapSQL(strQueryName As String, lngID As Long)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
 
strSQL = Nz(DLookup("sSQL", "tblSQLStrings", "SQLID = " & lngID), vbNullString)
 
If strSQL <> vbNullString Then
   Set db = Currentdb
   Set qdf = Currentdb.QueryDefs(strQueryName)
 
   qdf.SQL = strSQL
   qdf.Close
   Set qdf = Nothing
Else
   MsgBox "There is no SQL String available from ID: " & lngID & vbCrLF & _
              "Please contact your administrator for assistance", vbInformation, "Missing ID"
End If
 
End Function

Then to call this you just use

SwapSQL "qryMyBaseQuery", 4

and so you would have a base query and then you could use this to modify the SQL any time.

Hope that helps.

 

[mdmilner]

I'm thinking maybe you just need a quick and dirty way to change this.

How about converting it to SQL view, copying it to Word and using the Find & Replace utility there?