I'm not even sure if this is possible, but I have a number of queries I'd like to append to tables (1 table per query) in a split database. I want to make tables so the data and calculations I've made can be accessed by other users. Rather than create 10 append queries I'd like to be able to us a Do Until loop to run through all my queries and append them to their tables. I was thinking I can create a table with the query names and the corresponding table names and then use the loop to read through the table to get the query and append table name.
However, I'm not having any luck getting this done. I've got the following code, but keep getting the error - "syntax error in query expression 'P1CntrLnQ.* Insert'."
This is showing me that it's seeing the query name (P1CntrLnQ), but I'm not sure what else this is telling me - other than it doesn't like how the command is put together.
Code:
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim SQLstr As String
Set rs = CurrentDb.OpenRecordset("AppendTablesT", dbOpenSnapshot)
With rs
.MoveFirst
Do Until .EOF
DoCmd.RunSQL "Select " & rs!queryname & " " _
& "Insert Into " & rs!appendname & " " _
& "FROM & rs!queryname"
'db.Execute strSQL, dbFailOnError - original SQL execution
rs.MoveNext
Loop
End With
I changed to the docmd.runSQL from the remarked out db.Execute... simply because I didn't see a difference in how the script was running.
Am I anywhere close or am I totally off my nut thinking I can use a loop command for this? Any help, even if it's 'give up, it can't be done' would be appreciated.
DD