SQL Update Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to update a table with the date that a file was created. I have
the following code, but it will not update the table with the date.

Please advise what I have done wrong

Dim oFSO As Object
Dim oF As Object
Dim strTest1 As Date
Dim SQL_QUERY As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile("C:\Users\Kevin Bradbury\Documents\Work\update.xls")
strTest1 = oF.DateCreated
Test = oF.DateLastModified

DoCmd.SetWarnings False
MsgBox strTest1
SQL_QUERY = "UPDATE [Update Dates] SET [SheetCr] = strTest1;"
DoCmd.RunSQL SQL_QUERY
'DoCmd.RunSQL "UPDATE [Update Dates] SET [SheetCr]= me.Test1"
'DoCmd.RunSQL "UPDATE [Update Dates] SET [Sheet 1]= Date()"

DoCmd.SetWarnings True

Debug.Print oF.DateCreated
Debug.Print oF.DateLastModified

Set oF = Nothing
Set oFSO = Nothing
 
After locing most of my hair and spending all day on this, I have finally
sussed it. The problem was in the date format. The below code works fine!

Private Sub Command0_Click()
Dim oFSO As Object
Dim oF As Object
Dim strTest1 As Date
Dim SQL_QUERY As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile("C:\Users\Kevin Bradbury\Documents\Work\update.xls")

strTest1 = oF.DateCreated

DoCmd.SetWarnings False

SQL_QUERY = "UPDATE [Update Dates] SET [SheetCr]= #" & Format(strTest1,
"mm/dd/yyyy") & "#"
DoCmd.RunSQL SQL_QUERY


DoCmd.SetWarnings True

Set oF = Nothing
Set oFSO = Nothing

End Sub
 
Back
Top