Query input must contain at least one table or query error

[graviz]

All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:

Public Function test_sql()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn

MySQL = "SELECT RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM INTO TBL_5_7_RFCs" & _
"INTO TBL_5_7_RFCs" & _
"FROM RFC_STAGING_3" & _
"WHERE (((RFC_STAGING_3.DATE_TARGET_AFB) = #5/7/2015#) And ((RFC_STAGING_3.CCA_Project) = 1))" & _
"GROUP BY RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM" & _
"ORDER BY RFC_STAGING_3.PM;"
RS.Open MySQL
RS.Close
Set Conn = Nothing
End Function

I'll add the loop and array once I get this working but I'm stuck. Any ideas?

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/ImmediateWindow.htm

Hint, spaces between rows. Emptying and repopulating the table would likely be more efficient than recreating the table.

 

[graviz]

See if this helps:

http://www.baldyweb.com/ImmediateWindow.htm

Hint, spaces between rows. Emptying and repopulating the table would likely be more efficient than recreating the table.

Thanks. I used it and noticed a couple issues. (I should use debug more) Now I'm getting the table already exists error. Here's my updated code:

Public Function test_sql()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
 
MySQL = "SELECT RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM" & " " & _
"INTO TBL_5_7_RFCs" & " " & _
"FROM RFC_STAGING_3" & " " & _
"WHERE (((RFC_STAGING_3.DATE_TARGET_AFB) = #5/7/2015#) And ((RFC_STAGING_3.CCA_Project) = 1))" & " " & _
"GROUP BY RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM" & " " & _
"ORDER BY RFC_STAGING_3.PM;"
Debug.Print MySQL
RS.Open MySQL
RS.Close
Set Conn = Nothing
End Function

The other thing I noticed is it ran when the table didn't exist but it didn't show up in the access objects pane. I had to exit the db and go back in and it showed up.

 

[pbaldy]

If memory serves, you have to delete the existing table before creating it again:

DoCmd.DeleteObject...

You may also need to account for it not being there to delete (like I said, easier to empty/refill the table).

 

[graviz]

If memory serves, you have to delete the existing table before creating it again:

DoCmd.DeleteObject...

You may also need to account for it not being there to delete (like I said, easier to empty/refill the table).

I'm closer but now I get the object type argument for the action or method is blank or invalid.

DoCmd.DeleteObject acDefault, "TBL_5_7_RFCs"

The reason why I don't want to do a clear of the tables is because new ones will have to be made as time goes by.

 

[pbaldy]

Try

DoCmd.DeleteObject acTable, "TBL_5_7_RFCs"

 

[graviz]

Try

DoCmd.DeleteObject acTable, "TBL_5_7_RFCs"

That did the trick, thanks!

I'll post my final code with the loop.

 

[pbaldy]

Happy to help!