Update table from another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Samantha - 17 Mar 2005 09:17 GMT

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.

Reply to this Message

JaRa - 17 Mar 2005 14:11 GMT

What's the error message?

- Raoul

> 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.
[quoted text clipped - 14 lines]
>
> Any help is very much appreciated! thanks in advance.

Reply to this Message

Samantha - 17 Mar 2005 18:23 GMT

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.

> What's the error message?
>
[quoted text clipped - 18 lines]
> >
> > Any help is very much appreciated! thanks in advance.

Reply to this Message

JaRa - 17 Mar 2005 19:49 GMT

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

> Raoul,
> the error message is:
[quoted text clipped - 26 lines]
> > >
> > > Any help is very much appreciated! thanks in advance.

Reply to this Message

sparker - 17 Mar 2005 23:20 GMT

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

Reply to this Message