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()"

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()"

Chris Miller

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.

ASKER CERTIFIED SOLUTION

Sheils

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Miller

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
****************************************************************

Chris Miller

ASKER

See the attached

Chris Miller

ASKER

Table-Info.JPG

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Miller

ASKER

It Worked. Thanks.