By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

Append query as string SQL statement

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. Dim StrSQL As String
  2.  
  3. 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;"
  4.  
  5.  
  6. DoCmd.RunSQL (StrSQL)
  7.  
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!
Dec 11 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,364
Why are you surrounding your table names using single quotes?

Your're doing:
Expand|Select|Wrap|Line Numbers
  1. SELECT 'Table1'.* FROM 'Table1';
  2.  
Do you have spaces in your table names? If you do then you need brackets, [ ], not single quotes.
Dec 11 '07 #2

P: 7
Why are you surrounding your table names using single quotes?

Your're doing:
Expand|Select|Wrap|Line Numbers
  1. SELECT 'Table1'.* FROM 'Table1';
  2.  
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.
Dec 12 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Dec 12 '07 #4

P: 7
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.
Dec 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.