sql UPDATE with variables access vba

0
votes

I'm trying to update a table in Access VBA and it keeps failing, so I really hope one of you can help. Basicly I want to update table tblLog, the field LogOut with the variable logOutTime, where LogID is equal to LogID from the current form.

The first one gave error "3075"

Dim mySQL As String
Dim logOutTime As String
logOutTime = Now()

    mySQL = "UPDATE [tblLog] SET [LogUd]=" & logOutTime & " WHERE [Logid] =" & Me.LogID & ""
    DoCmd.RunSQL mySQL

and the second gave me error "3464"

DoCmd.RunSQL ("UPDATE tblLog " _
& "SET LogUD='" & logOutTime & "' " _
& "WHERE (((LogID)='" & Me.LogID & "'))")

Hope you can help, just don't understand when to put the ' or the ".

/Camilla

2
sqlms-accessvba
Important to remember is that when using an number value like an ID, either in the WHERE clause or SET clause, you will never need to uses the apostrophe (') but when you use a string or a date or a number as an string, you should use the apostrophe ('). – martijn

2 Answers

1
votes

Camila, you said you want to update the field LogOut but no where in your SQL code you have mentioned this field ;)

try this:

mySQL = "UPDATE [tblLog] SET [LogOut]='" & vba.format(logOutTime,"yyyy-mm-dd hh:mm:ss") & "' WHERE [Logid] =" & Me.LogID
2
votes

Dynamic SQL, the process of "gluing together" SQL statements in the manner you have suggested, should be avoided wherever possible. It is fussy, error-prone, and potentially dangerous. (Do a web search for "SQL Injection" for more information.)

Instead, you should use a parameterized query. You create a query with placeholders representing the parameters, you assign the actual properly-typed values to the parameters, and then you execute the query.

The advantage for you in this particular case is you don't have to worry about date formatting. Access has its own peculiarities when it comes to interpreting dates formatted as dd-mm-yyyy, and that has caused a certain amount of confusion over the years.

So, you should really use a parameterized query, which in Access is done by using a QueryDef object, like so:

Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("", _
        "PARAMETERS prmLogOutTime DATETIME, prmLogId LONG; " & _
        "UPDATE tblLog SET LogUd = [prmLogOutTime] WHERE LogId = [prmLogId]")
qdf!prmLogOutTime = Now()
qdf!prmLogId = Me.LogId
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing

Yes, it's a bit more work, but it's worth it.