SQL within VBA Question (1 Viewer)

[Tanner65]

I'm attempting to append a table using only a few fields from another table.

Unfortunately, I have to take a generalized approach with the "master" table (the one being appended) since it needs to hold a listing of information from several tables. So my thought process was to add a field, ProjAndIrun, into the "master" table which isn't found in the other tables to differentiate between the several different "projects."

Well, I'm having a hard time adding the information to the master table through an SQL command line. Using a query it'll open up an input box, but when replacing the "expr1" with a variable previously defined in the coding I receive an "Incomplete Query Cause" error (Error 3450).

The line works when I remove the project variable and associated field name, but I need for that information to be added to the "master" each time.

Any ideas?

SQL = "INSERT INTO Master_Test_Table ( Product_Number, Serial_Number, Asset_Number, PO_Number, ProjAndIrun )" _
& " SELECT MODEL_NUMBER, SERIAL_NUMBER, ASSET_NUMBER, PO_NUMBER, ProjAndIrun AS " & strProject _
& " FROM '" & strTestTable & "';"

CurrentDb.Execute SQ

Thanks in advance!

 

[ByteMyzer]

Replace:

& " FROM '" & strTestTable & "';"

...with:

& " FROM [" & strTestTable & "];"

 

[Tanner65]

Thanks ByteMyzer!

If it helps at all, I'm running Office 03 Pro

I hate to be a menace, or at least annoying, but after several takes at it, I'm getting a new error. Error 3061; "Too few parameters. Expected 2." I've tried adding a parenthesis in front of Model_Number and behind the strproject, but with no luck, another error.

Any more ideas?

Current Code:

    SQL = "INSERT INTO Master_Test_Table ( Product_Number, Serial_Number, Asset_Number, PO_Number, ProjAndIrun )" _
    & " SELECT MODEL_NUMBER, SERIAL_NUMBER, ASSET_NUMBER, PO_NUMBER, ProjAndIrun AS [" & strProject & "]" _
    & " FROM [" & strTestTable & "];"
    
    CurrentDb.Execute SQL

Thanks!

 

[ByteMyzer]

Set a breakpoint at the line:

CurrentDb.Execute SQL

In the Debug Window, type:

? SQL

...and press ENTER.

Create a new query and switch to SQL View. Copy the SQL string from the Debug Window and paste it into the SQL Window, then switch to Datasheet View. This will show you what the query thinks is a missing Input Parameter.

It is possible that your SQL statement is referencing a non-existant table or table field. This will help you to track it down.

 

[Tanner65]

Thanks Byte!

All of the tables I'm importing seem to have a different field name and I looked over it on accident. Thanks!