Append query via code

[GGW]

I'm attempting to build a table (Schedule) by append querying a table called "Master Question List". I think I need to use code because I want to loop through the operation several times, based on the value of another variable (the new table will be 2x, 3x etc. of the size of the initial append query). As I loop through I will change the value of "expr2" in the SQL statement.

Running this code generates a "Run-time error 3067, Query input must contain at least one table or query."

The attached code is the most recent version of what I tried - I'm sure it's filled with lots of errors (As you can guess, I'm a newbie also). I can't for the life of me figure out how to get VBA to recognize the query.


Dim MyDB As Database
Dim MyQDef As QueryDef
Dim SQLString As String
Dim rstSchedule As Recordset

Set MyDB = CurrentDb
Set rstSchedule = MyDB.OpenRecordset("Schedule", dbOpenDynaset)
SQLString = "INSERT INTO Schedule ( [Q#], Frequency, Applicability, Importance, [Entity ID] )" & _
"SELECT [Master Question List].[Q#], [Master Question List].[Checking Frequency]," & _
"1 AS Expr1, [Master Question List].[Overall Importance], 5 AS Expr2" & _
"FROM [Master Question List]" & _
"WHERE ((([Master Question List].Q)=""q"" Or ([Master Question List].Q)=""Q"&quot);"

Set MyQDef = MyDB.CreateQueryDef("", SQLString)

MyQDef.Execute
MyQDef.Close

Set MyQDef = Nothing [sig][/sig]

 

[KMITCH]

GGW,

I'm thinking the modifications below will come closer
to accomplishing what you are after.

Hope this helps,
Keith

Dim MyDB As Database
Dim MyQDef As QueryDef
Dim SQLString As String
Dim rstSchedule As Recordset
Dim rstMasterQuestionList As Recordset
Dim Expr1 As Long
Dim Expr2 As Long


Set MyDB = CurrentDb
Set rstSchedule = MyDB.OpenRecordset("Schedule", dbOpenDynaset)
SQLString = "SELECT [Master Question List].[Q#], [Master Question List].[Checking Frequency]," & _
" [Master Question List].[Overall Importance]" & _
"FROM [Master Question List]" & _
"WHERE ((([Master Question List].Q)=""q"" Or ([Master Question List].Q)=""Q"&quot)"

Set rstMasterQuestionList = MyDB.OpenRecordset(SQLString)

Expr2 = 5
rstMasterQuestionList.MoveFirst
Do Until rstMasterQuestionList.EOF
rstSchedule.AddNew
rstSchedule![Q#] = rstMasterQuestionList![Q#]
rstSchedule![Frequency] = rstMasterQuestionList![Checking Frequency]
rstSchedule![Applicability] = 1
rstSchedule![Importance] = rstMasterQuestionList![Importance]
rstSchedule![Entity ID] = Expr2
rstSchedule.Update
Expr2 = Expr2 + 1
rstMasterQuestionList.MoveNext
Loop
rstSchedule.Close
rstMasterQuestionList.Close
MyDB.Close [sig][/sig]