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:
[CODE=vb]
Dim StrSQL As String

StrSQL = "INSERT INTO tbl_All ( EquipName, AmtRequest, [Min], [Max], AmtReceived, Region, [Zone], Woreda, HealthCenter, ManualExp, AmtShipped, Notes, Supplier ) SELECT '" & EquipmentTbl & "'.EquipNam e, '" & EquipmentTbl & "'.AmtReque st, '" & EquipmentTbl & "'.Min, '" & EquipmentTbl & "'.Max, '" & EquipmentTbl & "'.AmtRecei ved, tbl_selectsite. Region, tbl_selectsite. Zone, tbl_selectsite. Woreda, tbl_selectsite. HealthCenter, '" & EquipmentTbl & "'.ManualEx p, '" & EquipmentTbl & "'.AmtShipp ed, '" & EquipmentTbl & "'.Notes, '" & EquipmentTbl & "'.Supplier FROM '" & EquipmentTbl & "', tbl_selectsite; "


DoCmd.RunSQL (StrSQL)
[/CODE]

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:
[Code=sql]
SELECT 'Table1'.* FROM 'Table1';
[/Code]

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:
[Code=sql]
SELECT 'Table1'.* FROM 'Table1';
[/Code]

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.