Hey, maybe you should not blank out the query so we can see what part is erroring out? It's a problem somewhere around here:
VALUES ('" _
& Range("B" & CStr(ActiveCell.Row)).Value & _
"','" & Range("C" & CStr(ActiveCell.Row)).Value & _
Dump that command text to a text box or msgbox so you can see what you're dealing with. It's a problem with the query.
I got the same error with this SQL update statement: The problem is with the date in the last field. I tried it without it and it worked. However, I need the date in order to be specific.
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim AppPath, app As String
Dim MyDate As Date
MyDate = Format(Date, "MM/DD/YYYY")
AppPath = "c:\LOGCALL\LOGCALL.mdb"
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
conn.ConnectionTimeout = 30
Set cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE LOGCALL_table SET ResolvedCall = 'X' WHERE ClientName = '" & Range("B" & CStr(ActiveCell.Row)).Value & "'AND Representative = '" & Range("C1").Value & "' AND DateOnCall = '" & Format(Date, "MM/DD/YYYY") & "';"
cmd.Execute , , adCmdText
I tried to dim MyDate as date variable and replace it :