Append query as string SQL statement

[jcethiopia]

Hi I'm a new user with Access and I need help with some VBA syntax. I am trying to run an append query using a string SQL statement. Basically, I need to append to "tbl_All" various fields from two different tables.

I need this to be dynamic because depending on what the user selects, it needs to take fields from a specific table. I am therefore using a variable to refer to this table: EquipmentTbl is the variable that i have already defined.

This is my code:

Dim StrSQL As String
 
StrSQL = "INSERT INTO tbl_All ( EquipName, AmtRequest, [Min], [Max], AmtReceived, Region, [Zone], Woreda, HealthCenter, ManualExp, AmtShipped, Notes, Supplier ) SELECT '" & EquipmentTbl & "'.EquipName, '" & EquipmentTbl & "'.AmtRequest, '" & EquipmentTbl & "'.Min, '" & EquipmentTbl & "'.Max, '" & EquipmentTbl & "'.AmtReceived, tbl_selectsite.Region, tbl_selectsite.Zone, tbl_selectsite.Woreda, tbl_selectsite.HealthCenter, '" & EquipmentTbl & "'.ManualExp, '" & EquipmentTbl & "'.AmtShipped, '" & EquipmentTbl & "'.Notes, '" & EquipmentTbl & "'.Supplier FROM '" & EquipmentTbl & "', tbl_selectsite;"
 
 
DoCmd.RunSQL (StrSQL)
 

When I run this I get an error that says "Syntax error in query. Incomplete query clause" Can anyone help me decipher what its wrong in the code??

Thanks!

[Rabbit]

Why are you surrounding your table names using single quotes?

Your're doing:

SELECT 'Table1'.* FROM 'Table1';
 

Do you have spaces in your table names? If you do then you need brackets, [ ], not single quotes.

[jcethiopia]

Why are you surrounding your table names using single quotes?

Your're doing:

SELECT 'Table1'.* FROM 'Table1';
 

Do you have spaces in your table names? If you do then you need brackets, [ ], not single quotes.

The actual name of my table is not EquipmentTbl. The value of this variable is the name of my table.

[FishVal]

Hi, there.

What all these single quotes are supposed to mean?
Kill'em to death.

Regards,
Fish

P.S. As Rabbit said - enclose all names breaking Access naming rules in square brackets. If you copypasted exactly the code you have, then the fieldname [Supplier FROM] has a space and must be enclosed in square brackets.

[jcethiopia]

Hi, there.

What all these single quotes are supposed to mean?
Kill'em to death.

Regards,
Fish

P.S. As Rabbit said - enclose all names breaking Access naming rules in square brackets. If you copypasted exactly the code you have, then the fieldname [Supplier FROM] has a space and must be enclosed in square brackets.

Thanks all. It worked.