FN-FORUM: SQL help

date posted 9th July 2007 18:51

Hi

I have a database containing tables structured like so:

tblVendors
ID
VendorTitle


tblCertifications
ID
VendorID


tblCertificationListings
ID
CertificationID

So I have Vendors that have Certifications. And those Certifications
are listed in a Certification Listings table (so they can be linked to
Courses =96 I haven't shown that above as it's not relevant to what I'm
stuck on). I need to write a script that deletes Vendors, and also
deletes everything associated with them. So I need to delete any
entries in tblCertificationListings that are linked to the Vendors
(through tblCertifications).

If I run this SQL statement:

sql1 =3D "SELECT tblVendors.ID, tblCertifications.VendorID,
tblCertifications.ID, tblCertificationListings.CertificationID FROM
(tblVendors INNER JOIN tblCertifications ON tblVendors.ID =3D
tblCertifications.VendorID) INNER JOIN tblCertificationListings ON
tblCertifications.ID =3D tblCertificationListings.CertificationID WHERE
tblVendors.ID=3D" & strID & ";"

I can correctly loop through all the records. However when I loop
through them and try to delete them using =85

Do While Not rsDeleteCertificationListings.EOF
rsDeleteCertificationListings.Delete
rsDeleteCertificationListings.MoveNext
Loop

I get this error =85

Microsoft OLE DB Provider for ODBC Drivers (0x8000FFFF)
Query cannot be updated because the FROM clause is not a single simple
table name.

I know you can be 'delete' statements with SQL so tried changing the
'SELECT' part of my statement to 'DELETE' as in the below =85.

sql1 =3D "DELETE tblVendors.ID, tblCertifications.VendorID,
tblCertifications.ID, tblCertificationListings.CertificationID FROM
(tblVendors INNER JOIN tblCertifications ON tblVendors.ID =3D
tblCertifications.VendorID) INNER JOIN tblCertificationListings ON
tblCertifications.ID =3D tblCertificationListings.CertificationID WHERE
tblVendors.ID=3D" & strID & ";"

But I get this error message =85

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Specify the table containing
the records you want to delete.

Can anyone suggest what I need to change?
TIA
Paul