Hi Chris,

First of all, you don't need the Recordsets at all if you're using this
method to delete the records. The SQL statement is executed against the
Connection object, so opening the Recordset is unnecessary overhead. So you
can change your code to this:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=P:\test.msd"

If testcondition1=whatever then
sSQL = "DELETE FROM Table1 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If

If testcondition2=whatever then
sSQL = "DELETE FROM Table2 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If

'/ make sure you close your Connection object when you're done
cn.Close
Set cn = Nothing


The error message you're getting indicates that perhaps the Field "Month" in
Table2 is not a Character or Text field. Try it without the single quotes
to see if it works.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Chris Dunigan wrote:
Thanks for all your help. All is becoming clearer!!
I am still having one problem that i wondered if you's help me with.

I have the following code:

-----
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=P:\test.msd"

If testcondition1=whatever then
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
sSQL = "DELETE FROM Table1 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If
rs.Close

If testcondition2=whatever then
rs.Open "Table2", cn, asOpenKeyset, adLockOptimistic, adCmdTable
sSQL = "DELETE FROM Table2 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If
rs.Close
-----

In the majority of cases i would expect both testcondition1 and
testcondition2 to be met, therefore there will be deletions from
Table1 an Table2. The code initially works, when testcondition1 is
met the data is deleted from Table1, however when the code gets down
to delete data from Table2 i get the following error:

----
Run-time error '-2147217913(80040e07)':
Data type mismatch in criteria expression
----

This error occurs when the line cn.Execute sSQL, lRecordset, adCmdText
is tried.

Do you have any ideas what is going wrong??

Many thanks in advance.
Chris