vba - Trying to update table in ms Access using VBA but getting failed , used multiple option but table not getting updated

Question

Trying to update table in ms Access using VBA but getting failed , used multiple option but table not getting updated

Dim bod As Date
Dim assets As String
Dim emname As String
Dim ecode As String
Dim Astatus As String
      
Astatus = "SOLD"    
        
msaved = True
DoCmd.SetWarnings False            
bod = Me.datetime.Caption
price1k = Me.cmbox1_1AID.Value
emname = Me.empname.Value
ecod = Nz(Me.code.Value, 0)
        
//tried using this but not getting updated no error
DoCmd.RunSQL "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
      
// Tried using this no update no error 
CurrentDb.Execute "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
    
// Tried this method no data update no error
Dim strSQL As String
strSQL = "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
DoCmd.RunSQL strSQL
         
MsgBox " Booking Request Accepted ", vbInformation
        

标签： vbams-accessms-access-2016

Answer 1

If [Date & Time] is a date/time type field, use # delimiter instead of '. If field is a number type, don't use any delimiter.

Remove the comma in front of WHERE clause.

Need [ ] around Asset-ID field name because of the hyphen.

Advise not to use spaces nor punctuation/special characters in naming convention.