SQL Problem in UPDATE

[isturland]

Hi
Having mastered adding new records to an Access table via ADO, I now need to amend and delete ( Seperately ).

Working on the amend code in my module, I have got the connection working fine and the Select statement is good also I believe, but I keep getting syntax errors on the UPDATE string shown as STRSQL Can someone please point me in the right direction ?

Any hints on adapting for record deletion would be appreciated also ?

Kind Regards

Ian

Sub Modify_Records()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stsql As String
Dim stCon As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Long
Dim vaData As Variant
Dim i As Long
Dim j As Long
Dim strsql As String
Dim ref As String
Dim db As Database

    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Log")
    rnData = wsSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    vaData = rnData
   
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= G:\EBKN_Public\Booking Board\Data Table.mdb"
     
    
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
     
    cnt.Open stCon
     
    For i = 3 To vaData
    ref = wsSheet.Cells(i, 1).Value
    stsql = "SELECT * FROM Log WHERE Booking_Ref='" & ref & "'"    
    With rst
   .Open strsql, cnt, 3, 3, adCmdTable
            
   If .EOF And .BOF Then
   MsgBox "No Data"
   Else
   strsql = "UPDATE Log" & "SET Date = " & wsSheet.Cells(i, 2) & "," & "Time = '" & wsSheet.Cells(i, 3) & "'," & "Bay ='" & wsSheet.Cells(i, 4) & "' " & "WHERE Booking_Ref ='" & wsSheet.Cells(i, 1) & "'"
   End If
   .Close
   End With
   stsql = Empty
   strsql = Empty
   Next
   
   cnt.Close
   'Release objects from memory.
    Set rst = Nothing
    Set cnt = Nothing
End Sub