ADO Delete Of Record In A Specific Table 4

[Jdoggers]

Hi,
I was just wondering how to delete a specific record out of only one table in a database that has multiple tables. Is there any way to do that? Ive tried it but the only thing that i figured out how to do is delete a record out of an entire recordset...recordsetname.delete . This, however, deletes the first record of all of the other tables, not just the record in the specific table that im interested in deleting.

suppose i have a table called table1 and a record in that table that i want to delete,

thanks guys

 

[Sheco]

It sounds like there is some confusion between a recordset and a table. It might help to think of a recordset as a database view instead of a table, because the recordset can contain fields joined in from multiple tables.

The easiest way to delete a row might be to find the key value of the record you wish to delete and execute a SQL statement to get rid of it.

Something like:
DELETE FROM MyTable WHERE KeyField = XYZ

 

[JerryKlmns]

Jdoggers

Is there a referential integrity rule that would cascade delete related records from the rest tables?

 

[zemp]

I would not trust the cascading deletes or updates. If there is a related record in another table (foreign key) then you will usually get an error (multiple step operation... I believe). I find it best to first delete any records that contain a foreign key in other tables and then the main record in the main table.

It might reqire two or more delete statements but they run quickly.

zemp

 

[Jdoggers]

Hi All,
There is not referencial integrity between the primary table and one of the others, however the ones that are getting uncalled for deletions are just simply related but dont have any referencial integrity. I think they are getting deleted because they are part of the recordset and im deleting a record in the recordset. Oh, by the way, isnt there some special code that i need to be able to execute sql code statements. I cant remember but it is something that tells the compiler that it is an sql statement. Thanks

 

[zemp]

It all depends on the tools that you are using. Give an example of your code and list the tools you are using.

zemp

 

[BobRodes]

I'm getting that you have a recordset that has tables joined in it, and didn't understand completely the first response.

If you had a recordset whose SQL value was "select * from sometable" and you deleted a record in it, you'd only delete from sometable. Look at your SQL statement.

Now, your last q makes me wonder: are you using DAO or ADO?

Bob