win 2k and xp
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date
MyDate = Format(Date, "MM/DD/YYYY")
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft® Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
Set cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.
...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).
But I really think the problem is in your methodology, not your syntax...
If it's not practically useful, then it's practically useless.