Update table from another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to update a table (Table1) from a temp table (Temp1) that I imported
data into. I'm getting an error on the db.Execute line.
What's wrong with these codes?

Dim db As Database, rsCust As Recordset
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE SampleTest"
'strSQL = strSQL & "SET SampleTest.POID = " & Me![test]
strSQL = strSQL & "SET [POID] = Order![PO ID]"
strSQL = strSQL & "WHERE SampleTest![PartNum]= Order![Part Number] AND
SampleTest!PO=[Order!PO]"
MsgBox strSQL

db.Execute strSQL, dbFailOnError

Any help is very much appreciated! thanks in advance.
 
Raoul,
the error message is:
Run-time error '3144'. Syntax error in UPDATE statement.

I'm thinking that something's missing, like quotation marks. But on a
deeper level, does the sql make sense? I'm a beginner in sql with vba. Any
help is very much appreciated. thank you.

JaRa said:
What's the error message?

- Raoul

Samantha said:
I need to update a table (Table1) from a temp table (Temp1) that I imported
data into. I'm getting an error on the db.Execute line.
What's wrong with these codes?

Dim db As Database, rsCust As Recordset
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE SampleTest"
'strSQL = strSQL & "SET SampleTest.POID = " & Me![test]
strSQL = strSQL & "SET [POID] = Order![PO ID]"
strSQL = strSQL & "WHERE SampleTest![PartNum]= Order![Part Number] AND
SampleTest!PO=[Order!PO]"
MsgBox strSQL

db.Execute strSQL, dbFailOnError

Any help is very much appreciated! thanks in advance.
Click to expand...
Click to expand...
 
Indeed syntax problem mainly missing blanks always add a blank add the end of
a concatenation.
I also suggest to build your queries first in a querybuilder e.g. the
acccess one and then copy the sql statement into vba. This gives you a basic
template which you can customize in vba.

strSQL = "UPDATE SampleTest "
strSQL = strSQL & "SET [POID] = Order![PO ID] "
strSQL = strSQL & "FROM SampleTest INNER JOIN Order ON SampleTest![PartNum]=
Order![Part Number] AND SampleTest!PO=[Order!PO]"

MsgBox strSQL


- Raoul

Samantha said:
Raoul,
the error message is:
Run-time error '3144'. Syntax error in UPDATE statement.

I'm thinking that something's missing, like quotation marks. But on a
deeper level, does the sql make sense? I'm a beginner in sql with vba. Any
help is very much appreciated. thank you.

JaRa said:
What's the error message?

- Raoul

Samantha said:
I need to update a table (Table1) from a temp table (Temp1) that I imported
data into. I'm getting an error on the db.Execute line.
What's wrong with these codes?

Dim db As Database, rsCust As Recordset
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE SampleTest"
'strSQL = strSQL & "SET SampleTest.POID = " & Me![test]
strSQL = strSQL & "SET [POID] = Order![PO ID]"
strSQL = strSQL & "WHERE SampleTest![PartNum]= Order![Part Number] AND
SampleTest!PO=[Order!PO]"
MsgBox strSQL

db.Execute strSQL, dbFailOnError

Any help is very much appreciated! thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Samantha,
if you make the following minor changes and add a reference to Microsoft ADO
your code will be functional, maybe not pretty, but it will work!

Dim db As DAO.Database
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE SampleTest " & _
"SET [POID] = Order![PO ID] " & _
"WHERE SampleTest![PartNum]= Order![Part Number] AND " & _
"SampleTest!PO=[Order!PO];"

MsgBox strSQL

db.Execute strSQL, dbFailOnError

Also Note: If you do not know how to add a reference in Access do this:
Aa reference to Microsoft DAO 3.6 Object Library
To add the reference to the Microsoft DAO 3.6 Object Library:
While you have the module open in the database click on
Tools then
References then
Scroll Down until you find
Microsoft DAO 3.6 Object Library
then add it so the following code will work.

- SPARKER
 
Back
Top