Avatar of CMILLER
CMILLER
Flag for United States of America asked on

Access 2007 DoCmd.RunSQL Issue

I have an Access 2007 DB that has an Admin menu. On the admin menu I have macro buttons that will delete and update tables.

I have created a table "tbl_Uploads" with two fields. The first field lists all the tables that get updated. The second field is called "Timestamp".

How do I use the Run SQL to update the timestamp field for a given table listed in the tbl_Uploads.

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = DATE()"
Microsoft Access

Avatar of undefined
Last Comment
CMILLER

8/22/2022 - Mon
Sheils

Use the following code:

CurrentDb.Execute "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = DATE()"
Rey Obrero (Capricorn1)

@CMILLER

what is your problem with the codes you posted?

docmd.setwarnings false
DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = DATE()"

docmd.setwarnings true

if your field "timestamp" Format is set to General Date(includes time), use Now() instead of Date()

DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = Now()"
CMILLER

ASKER
"tbl_Uploads" has two fields.

Field1 is Table Name (Lists the names of all my update tables)

Field2 is Timestamp (one row for each update table)

It works fine if all I have in my tbl_Uploads is just the Timestamp field but I have added a field that lists all my update table names.

So, when I update a table, I need the timestamp to populate Field1 for the correct table.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Sheils

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
CMILLER

ASKER
I am getting an error on the DoCmd.RunSQL

****************************************************************
Function ImportTechnicianTable()

Dim strFile As String
strFile = Environ("userprofile") & "\Documents\Import\Technicians.xlsx"

'complete this line for the TransferSpreadsheet command
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE-Technician Table"
DoCmd.TransferSpreadsheet acImport, 9, "Technicians", strFile, True, "Technicians!"
DoCmd.RunSQL "UPDATE tbl_Uploads SET tbl_Uploads.timestamp = DATE() WHERE Table Name="Technicians"
DoCmd.SetWarnings False


MsgBox "Technicians Table was updated!"
End Function
****************************************************************
CMILLER

ASKER
See the attached
CMILLER

ASKER
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CMILLER

ASKER
It Worked. Thanks.