Help !!!!! Using the (SELECT....IN) query with ADO?

[JonnyCab]

I have a problem with the following query.

Dim strSQL As String
Dim Temp As New ADODB.Recordset

strSQL = "DELETE * FROM tblTemp WHERE Contract IN (SELECT Contract FROM tblWOStatus WHERE (Status = Null))"

Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

Set Temp = Nothing

This code works if tblWOStatus (in the SELECT part of query) is in the database that Connection1 links to. However it isn't in real life. The table is in another database which I have linked using a second ADO connection (connection2).

I tried...the following where .rscmdProjStatus is the ADO recordset I trying to use the 'IN' with but doesn't seem to work.

strSQL = "DELETE * FROM tblTemp WHERE Contract IN '(" & DataEnvironment1.rscmdProjStatus & ")'"

Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

Set Temp = Nothing

Any body with some ideas please !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[vbuser1976]

Have you tried to use JOIN/UNION in the string? From what I get from you these two tables are in two separate db's. If that is true maybe you have to join/union them before you can use them together.

I hope this helps.