Updating Query with code

  • Thread starter Thread starter Gibson
  • Start date Start date
G

Gibson

I have a backend mdb and a frontend mdb. I have added a field to a table in
the backend using code. Now I need to update a query in the front end that
feeds a form to include the new field added to the table in the backend.
Can this be done through code thus the adding of the field and updating of
the query is done in at the same time as it were?
 
This may give you some ideas:

Sub FixSql(strQryName As String, strTblName As String)
'To correct the SQL in a query to avoid having to do it each month by hand
'strQryName = Query Name to be changed
'strTblName = Table to look for Field Name to change in Query

Dim strQry As String
Dim strReplace As String
Dim strFind As String
Dim qdf As QueryDef
Dim strSumMo As String
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field

'Look for Field Name containing "SumOF"
Set dbs = CurrentDb()
Set tdf = dbs(strTblName)
For Each fld In tdf.Fields
If InStr(fld.Name, "SumOf") <> 0 Then
strReplace = "." & fld.Name
Exit For
End If
Next fld

'Strip out the Month Name to do the replace
strFind = "." & Right(strReplace, Len(strReplace) - 6)

'Get the Query to change
Set qdf = dbs.QueryDefs(strQryName)
strQry = qdf.SQL
'Strip out SumOf and replace with empty string
strQry = Replace(strQry, "SumOf", "")
'Add SumOf to field name to align with Table field name
strQry = Replace(strQry, strFind, strReplace)
'But wait! There's more
strFind = Replace(strFind, ".", "[")
strReplace = Replace(strReplace, ".", "[")
'Fix the field name the previous replace changed
strQry = Replace(strQry, "zSumOf", "z")
'But wait! There's more - fix the totals
strFind = Replace(strFind, ".", "[")
strReplace = Replace(strReplace, ".", "[")
strQry = Replace(strQry, strFind, strReplace)
qdf.SQL = strQry
qdf.Close

Set qdf = Nothing
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Sub
 
Back
Top