Updating a Specific Value in a Table of Values

  • Thread starter Thread starter JohnC
  • Start date Start date
J

JohnC

I have a table of values similar to:

table Name tblValues
Field1 VALUE_NAME (text)
Field2 VALUE (text)

The MDB file is on a LAN file server. I have an import routine (OBDC to
Oracle) that I run occasionally from another MDB that copies the data to the
LAN file server MDB.

When I run the import, I need to update the value for IMPORTDATE in
tblValues to be the date that the import routine is run. (I think this will
need to be a string value because VALUE is text.)

JohnC
 
JohnC said:
I have a table of values similar to:

table Name tblValues
Field1 VALUE_NAME (text)
Field2 VALUE (text)

The MDB file is on a LAN file server. I have an import routine (OBDC
to Oracle) that I run occasionally from another MDB that copies the
data to the LAN file server MDB.

When I run the import, I need to update the value for IMPORTDATE in
tblValues to be the date that the import routine is run. (I think
this will need to be a string value because VALUE is text.)
Click to expand...

We don't know what format should be used for the date when it's stored
in the VALUE field, but code similar to this ought to get you pretty
close:

Dim db As DAO.Database

Set db = CurrentDb

db.Execute _
"UPDATE tblValues SET [VALUE] = '" & _
Format(Date, "Short Date") & _
"' WHERE [VALUE_NAME]='IMPORTDATE'", _
dbFailOnError

Set db = Nothing

I'm not sure, though, which database this statement is going to run in.
The above code assumes that tblValues is in the same database as the
code. If it's not, you must either change it like this:

Set db = DBEngine.OpenDatabase("path to database containing
tblValues")

db.Execute ...

db.Close
Set db = Nothing

or else like this:

Set db = CurrentDb

db.Execute _
"UPDATE tblValues IN 'path to your database' " & _
"SET [VALUE] = '" & _
Format(Date, "Short Date") & _
"' WHERE [VALUE_NAME]='IMPORTDATE'", _
dbFailOnError

Set db = Nothing

That's all air code, but it should be close to what you need..
 
Back
Top