Access VBA SQL Update not working

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am using the following Access VBA code and for some reason no updates are hitting the database. I a getting no errors.
I have included the SQL held  in the variable S further down

  Dim S As String
    S = "Update t_Licence Set LastRenewalDate = #" & Me.RenewalDate.value & "#"
    S = S & " And ExpiryDate = #" & Me.ExpiryDate.value & "#"
    S = S & " Where LicenceNumberPK = " & oLicenceNumberPK
   
    CurrentDb.Execute S

Update t_Licence Set LastRenewalDate = #7/11/2019# And ExpiryDate = #6/11/2019# Where LicenceNumberPK = 2
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Whenever you are using CurrentDB.Execute always include the optional parameter dbFailOnError:
CurrentDB.Execute SQL,dbFailOnError

Open in new window

That way it will throw an error and tell you what is wrong
Actually in this case I think its a matter of formatting the date. The engine only wants US formatted dates, i.e mm/dd/yyyy
So the engine thinks you mean the 11th of June and not the 6th of November.
Format(Date,”mm\/dd\/yyyy”)
I think that is the correct syntax though I can’t check it right now
ste5anSenior Developer
Commented:
Well, it's your syntax. The columns to be updated must be separated by comma.
You should use functions to encapsulate the date formatting as Anders said, e.g.

Option Compare Database
Option Explicit

Public Sub Test()

  Const SQL_UPDATE As String = _
    "UPDATE t_Licence " & _
    "SET    LastRenewalDate = {0}, " _
    "       ExpiryDate = {1} "_
    "WHERE  LicenceNumberPK = {2};"

  SqlExecuteFmt SQL_UPDATE, SqlDate(RenewalDate.Value), SqlDate(ExpiryDate.Value), oLicenceNumberPK

End Sub

Open in new window

Using this kind of line breaks makes imho concatenated SQL strings better readable.

with

Option Compare Database
Option Explicit

Public Enum EnumMousePointerConstants
  ccDefault = 0
  ccHourglass = 11
End Enum

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

  If m_CurrentDb Is Nothing Then
    Set m_CurrentDb = CurrentDb
  End If

  Set CurrentDbC = m_CurrentDb

End Property

Public Function SqlDate(ADate As Date) As String

  SqlDate = Format(ADate, "\#m\/d\/yyyy#")

End Function

Public Function SqlExecute(AStatement As String, Optional ARecordsAffected As Long) As Boolean

  On Local Error GoTo LocalError

  Dim DBEngineError As Variant
  Dim ErrorDescription As String
  Dim OldMousePointer  As Long

  OldMousePointer = Screen.MousePointer
  Screen.MousePointer = ccHourglass
  SqlExecute = False
  CurrentDbC.Execute AStatement, dbFailOnError Or dbSeeChanges
  ARecordsAffected = CurrentDbC.RecordsAffected
  SqlExecute = True
  Screen.MousePointer = OldMousePointer
  Exit Function

LocalError:
  Screen.MousePointer = OldMousePointer
  Debug.Print "--- Error ---"
  Debug.Print "Source: " & Err.Source
  Debug.Print "Number: " & Err.Number
  ErrorDescription = Err.Description
  If DBEngine.Errors.Count > 0 Then
    ErrorDescription = ErrorDescription & vbCrLf
  End If

  For Each DBEngineError In DBEngine.Errors
    If DBEngineError <> Err.Description Then
      ErrorDescription = ErrorDescription & vbCrLf & Replace(DBEngineError, ".0][SQL Server]", ".0][SQL Server]" & vbCrLf & vbCrLf)
    End If
  Next DBEngineError

  Debug.Print "Description: " & ErrorDescription
  Debug.Print "SQL: " & AStatement

End Function

Public Function SqlExecuteFmt(AStatement As String, ParamArray AValues() As Variant) As Boolean

  Dim Count As Long
  Dim Result As String
  Dim Statement As String

  Statement = AStatement
  For Count = 0 To UBound(AValues())
    Statement = Replace(Statement, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count

  SqlExecuteFmt = SqlExecute(Statement)

End Function

Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
When you are performing operations in MSSQL and you have "doubts" the easy way to catch what is wrong is to go to SSMS and execute it from there...if its OK ...maybe something trivial slipped in but in your case
Update t_Licence 
Set LastRenewalDate = CAST('7-11-2019' AS DATETIME) 
,
 ExpiryDate = CAST('6-11-2019' AS DATETIME) 
WHERE  LicenceNumberPK = 2

Open in new window

Distinguished Expert 2017

Commented:
The string should end up looking like this:

Update t_Licence Set LastRenewalDate = #11/7/2019#, ExpiryDate = #11/6/2019# Where LicenceNumberPK = 2

When you create SQL strings in VBA (I almost never do this),  put a stop in the code on the execute line.  print the SQL String in S to the immediate window and paste it into the SQL view of the QBE.  That lets you test the string as it is built.  There are TWO errors that I see.  The first is dates as mentioned by Anders.  When you pass a date STRING using embedded SQL, it MUST be either in standard US format as mm/dd/yyyy or in a non-ambiguous format such as yyyy/mm/dd.  When you use saved querydefs with arguments, you don't need to worry about formatting dates as long as your argument is defined as a date data type, either because the form control is bound to a date field or its format is set to a valid date format or if you have added PARAMETER definitions to the querydef and specifically defined the parameter as a date.  So a querydef might look like:

Update t_Licence Set LastRenewalDate = Forms!yourform!RenewalDate, ExpiryDate = Forms!yourform!ExpiryDate Where LicenceNumberPK = Forms!yourform!oLicenceNumberPK

The second error is the use of "and" between the update fields.  this is the syntax error referred to by ste5an.

And finally, if you are using a bound form, you shouldn't even need to run an update query.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Oh, BTW, in case you have not already heard, there was a bug introduced into Access 2010, 2013, 2016, and 2019 by a recent (12 Nov) Office update.  This bug is specific to UPDATE queries that attempt to update data directly in a table.  For more info, check out this website.

Dale
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
The date formatting and commas made all the difference so thanks for that.I appreciate the additional advice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial