Nested dynamic SQL Query Question

[Jon Lewis]

Hi All

I've a tblCompanies, tblCategories (many to many) and a junction table
tblCompaniesCategories. I need to open frmCompanies (bound to tblCompanies)
apply a filter and then delete all records from tblCompaniesCategories for a
specified Category and the Companies in the filtered recordset.

So some thing like this:

dbs.Execute "Delete FROM tblCompaniesCategories WHERE CategoryID = " &
Me.lstCategories & " AND CompanyID IN (SELECT CompanyID FROM
Forms!frmCompanies.RecordsetClone)", dbFailOnError

I get a syntax error in CompanyID IN (SELECT CompanyID FROM ....)

The filtered recordset is derifed from a complex multi-parameter filtering
process so it would be difficult to reproduce the SQL for this so can I use
a RecordsetClone in this way or are there any other suggestions (other than
looping through the RecordsetClone and checking for/deleting the Category
record in the junction table, which is slow)?

Many thanks

 

[Dirk Goldgar]

Hi All

I've a tblCompanies, tblCategories (many to many) and a junction table
tblCompaniesCategories. I need to open frmCompanies (bound to
tblCompanies) apply a filter and then delete all records from
tblCompaniesCategories for a specified Category and the Companies in the
filtered recordset.

So some thing like this:

dbs.Execute "Delete FROM tblCompaniesCategories WHERE CategoryID = " &
Me.lstCategories & " AND CompanyID IN (SELECT CompanyID FROM
Forms!frmCompanies.RecordsetClone)", dbFailOnError

I get a syntax error in CompanyID IN (SELECT CompanyID FROM ....)

The filtered recordset is derifed from a complex multi-parameter filtering
process so it would be difficult to reproduce the SQL for this so can I
use a RecordsetClone in this way or are there any other suggestions (other
than looping through the RecordsetClone and checking for/deleting the
Category record in the junction table, which is slow)?

No, you can't refer to a recordset in a SQL statement, so your first
approach won't work. You might either:

(a) Incorporate the form's Filter property in the SQL statement to identify
the companies to be included, something like this (air code):

Dim strSQL As String

strSQL = _
"Delete FROM tblCompaniesCategories WHERE CategoryID = " & _
Me.lstCategories & _
" AND CompanyID IN (SELECT CompanyID FROM tblCompanies WHERE " & _
Me.Filter & ")"

dbs.Execute strSQL, dbFailOnError

.... or ...

(b) Build up a list of company IDs by looping through the recordsetclone,
and then use that list in the SQL statement:

Dim strSQL As String
Dim strCompanies As String

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strCompanies = strCompanies & ", " & !CompanyID
.MoveNext
Loop
End If
End With

If Len(strCompanies) > 0 Then
MsgBox "No companies!"
Else
strCompanies = Mid(strCompanies, 3) ' drop leading comma+space

strSQL = _
"Delete FROM tblCompaniesCategories WHERE CategoryID = " & _
Me.lstCategories & _
" AND CompanyID IN (" & strcompanies & ")"

dbs.Execute strSQL, dbFailOnError
End If

 

[Jon Lewis]

Thanks Dirk
Plan (b) looks ideal

Jon

No, you can't refer to a recordset in a SQL statement, so your first
approach won't work. You might either:

(a) Incorporate the form's Filter property in the SQL statement to
identify the companies to be included, something like this (air code):

Dim strSQL As String

strSQL = _
"Delete FROM tblCompaniesCategories WHERE CategoryID = " & _
Me.lstCategories & _
" AND CompanyID IN (SELECT CompanyID FROM tblCompanies WHERE " & _
Me.Filter & ")"

dbs.Execute strSQL, dbFailOnError

... or ...

(b) Build up a list of company IDs by looping through the recordsetclone,
and then use that list in the SQL statement:

Dim strSQL As String
Dim strCompanies As String

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strCompanies = strCompanies & ", " & !CompanyID
.MoveNext
Loop
End If
End With

If Len(strCompanies) > 0 Then
MsgBox "No companies!"
Else
strCompanies = Mid(strCompanies, 3) ' drop leading comma+space

strSQL = _
"Delete FROM tblCompaniesCategories WHERE CategoryID = " & _
Me.lstCategories & _
" AND CompanyID IN (" & strcompanies & ")"

dbs.Execute strSQL, dbFailOnError
End If



--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)