update table via VBA

[Axyl]

Hi all,
i'm having some problem with this code for upgrading a table, my error
message is a syntax error in the query. Can you help me?
Dim fatt As String
Dim ord As String
Dim dbs As Database
Dim fatt_rst As DAO.Recordset
Dim log_rst As DAO.Recordset
Set dbs = CurrentDb
Set fatt_rst = dbs.OpenRecordset("fatt_table", dbOpenDynaset)
Set log_rst = dbs.OpenRecordset("log_table", dbOpenDynaset)
Do Until fatt_rst.EOF
fatt_rst.MoveFirst
fatt = fatt_rst.Fields!fattura
ord = fatt_rst.Fields!ordine
DoCmd.RunSQL "UPDATE log_table SET log_table.field = " & fatt & _
"(where log_table.field2 = " & ord & ")"
fatt_rst.MoveNext
Loop
fatt_rst.Close
dbs.Close

thanx in adv.
regs.

 

[Nikos Yannacopoulos]

Axyl,

To begin with, you most likely have a reference problem: the database
declaration should be changed to:

Dim dbs As DAO.Database

since you are working with DAO. Also make sure you have included DAO in your
references.

Moreover, you are opening a recordset (log_rst) which you are not using (nor
closing). Suggest you comment out that line.

Also, I noticed you have the MoveFirst within your loop, whereas I guess it
should be before the Do; as it is, you are always working with the first
record, and the last one will never be reached so the loop can terminate.

If the error occurs at the DoCmd.RunSQL:
1. Check that the fieldnames are correct
2. Check that the syntax is correct for the field types; the way it is, it
assumes both fields are numeric or date. Is this correct?

HTH. If still having problems, post back with the exact error message and
the line it occurs on.
Nikos

 

[Axyl]

Great!
now it works fine.
Thanks.

Axyl,

To begin with, you most likely have a reference problem: the database
declaration should be changed to:

Dim dbs As DAO.Database

since you are working with DAO. Also make sure you have included DAO in
your
references.

Moreover, you are opening a recordset (log_rst) which you are not using
(nor
closing). Suggest you comment out that line.

Also, I noticed you have the MoveFirst within your loop, whereas I guess
it
should be before the Do; as it is, you are always working with the first
record, and the last one will never be reached so the loop can terminate.

If the error occurs at the DoCmd.RunSQL:
1. Check that the fieldnames are correct
2. Check that the syntax is correct for the field types; the way it is, it
assumes both fields are numeric or date. Is this correct?

HTH. If still having problems, post back with the exact error message and
the line it occurs on.
Nikos

 

[Axyl]

thanks Tom.

Axyl and Nikos,


You really don't need to explicitly declare dbs as DAO.Database. However,
doing so certainly
doesn't hurt anything--in fact, I often times do the same thing in my code
just to help clarify
my intentions. The ADO library does not include the Database object, so
there is no chance of a
priority issue here. In other words, there's nothing wrong with the
original Dim dbs As Database.

I agree with Nikos' other comments, and I have a few of my own:

1) you need to include quotes around the string variables
2) recommend that you use Currentdb.Execute strSQL, dbfailonerror instead
of DoCmd.RunSQL and
3) be careful about using reserved words in Access. It appears as if one
of the fields in
log_table is named "field". Field (and Fields) are reserved words:

List of reserved words in Access
http://support.microsoft.com/?id=209187

Here is my recommended changes, which include adding double quotes around
the string variables:

Dim dbs As Database
Dim fatt_rst As DAO.Recordset
Dim log_rst As DAO.Recordset
Dim fatt As String
Dim ord As String
Dim strSQL As String

Set dbs = CurrentDb
Set fatt_rst = dbs.OpenRecordset("fatt_table", dbOpenDynaset)

fatt_rst.MoveFirst

Do Until fatt_rst.EOF
fatt = fatt_rst.Fields!fattura
ord = fatt_rst.Fields!ordine

strSQL = "UPDATE log_table SET log_table.field = """ & fatt & _
""" where log_table.field2 = """ & ord & """"

CurrentDb.Execute strSQL, dbFailOnError

fatt_rst.MoveNext

Loop

fatt_rst.Close
dbs.Close



Tom
______________________________________________


Axyl,

To begin with, you most likely have a reference problem: the database
declaration should be changed to:

Dim dbs As DAO.Database

since you are working with DAO. Also make sure you have included DAO in
your
references.

Moreover, you are opening a recordset (log_rst) which you are not using
(nor
closing). Suggest you comment out that line.

Also, I noticed you have the MoveFirst within your loop, whereas I guess
it
should be before the Do; as it is, you are always working with the first
record, and the last one will never be reached so the loop can terminate.

If the error occurs at the DoCmd.RunSQL:
1. Check that the fieldnames are correct
2. Check that the syntax is correct for the field types; the way it is, it
assumes both fields are numeric or date. Is this correct?

HTH. If still having problems, post back with the exact error message and
the line it occurs on.
Nikos

 

[Tom Wickerath]

Axyl and Nikos,

the database declaration should be changed to:
Dim dbs As DAO.Database

You really don't need to explicitly declare dbs as DAO.Database. However, doing so certainly
doesn't hurt anything--in fact, I often times do the same thing in my code just to help clarify
my intentions. The ADO library does not include the Database object, so there is no chance of a
priority issue here. In other words, there's nothing wrong with the original Dim dbs As Database.

I agree with Nikos' other comments, and I have a few of my own:

1) you need to include quotes around the string variables
2) recommend that you use Currentdb.Execute strSQL, dbfailonerror instead of DoCmd.RunSQL and
3) be careful about using reserved words in Access. It appears as if one of the fields in
log_table is named "field". Field (and Fields) are reserved words:

List of reserved words in Access
http://support.microsoft.com/?id=209187

Here is my recommended changes, which include adding double quotes around the string variables:

Dim dbs As Database
Dim fatt_rst As DAO.Recordset
Dim log_rst As DAO.Recordset
Dim fatt As String
Dim ord As String
Dim strSQL As String

Set dbs = CurrentDb
Set fatt_rst = dbs.OpenRecordset("fatt_table", dbOpenDynaset)

fatt_rst.MoveFirst

Do Until fatt_rst.EOF
fatt = fatt_rst.Fields!fattura
ord = fatt_rst.Fields!ordine

strSQL = "UPDATE log_table SET log_table.field = """ & fatt & _
""" where log_table.field2 = """ & ord & """"

CurrentDb.Execute strSQL, dbFailOnError

fatt_rst.MoveNext

Loop

fatt_rst.Close
dbs.Close



Tom
______________________________________________


Axyl,

To begin with, you most likely have a reference problem: the database
declaration should be changed to:

Dim dbs As DAO.Database

since you are working with DAO. Also make sure you have included DAO in your
references.

Moreover, you are opening a recordset (log_rst) which you are not using (nor
closing). Suggest you comment out that line.

Also, I noticed you have the MoveFirst within your loop, whereas I guess it
should be before the Do; as it is, you are always working with the first
record, and the last one will never be reached so the loop can terminate.

If the error occurs at the DoCmd.RunSQL:
1. Check that the fieldnames are correct
2. Check that the syntax is correct for the field types; the way it is, it
assumes both fields are numeric or date. Is this correct?

HTH. If still having problems, post back with the exact error message and
the line it occurs on.
Nikos