This completes the task of taking automatically generated SQL previously placed in a table and writes the SQL therein to Query Objects naming them automatically. This has several advantages to cut and paste –
1) Its Lightning Quick
2) Completely consistent naming
3) Cut and Paste can be awkward with the windows
4) Its just fun
To created the NestedIIfs table see this post
MS Access VBA Function – Creating NestedIIFs
This is the post on writing Query Objects directly
MS Access – Automated Single Query Object Creation
It requires that you have a table called T005NestedIIFs
with the populated fields
SQLField
TargTable
TargField
Public Function WriteNIFQueryObjects(LCounter As Long) As String
Dim rstX As DAO.Recordset
Dim QName As String
Dim qdf As Variant
Dim strSQL As String
Dim LCountStart As Long
LCountStart = LCounter
Set rstX = CurrentDb.OpenRecordset("T005NestedIIFs")
Do Until rstX.EOF = True
qdf = rstX!SQLField
QName = "Q" & LCounter & rstX!TargTable & "-" & rstX!Targfield & "-Update"
LCounter = LCounter + 1
Set qdf = CurrentDb.CreateQueryDef(QName, rstX!SQLField)
rstX.MoveNext
Loop
MsgBox "Query objects written to Database numbers starting" & LCountStart
End Function
