By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Which table's records get deleted?

P: n/a
MLH
I have two tables with a common field containing
an automobile VIN code. If I create a query putting
both tables in the QBE grid and link the common
field between the two tables and drop one single
field from one ofthe tables for display purposes,
I get a list of records having common values in
both tables.

If I select all rows in the dynaset and press delete
key - records from the same table each time are
deleted. I wanted them to be deleted from the
other table (of course).

How do I control that?

PVIN and SerialNum are the common fields in the
two tables.

SELECT tblPreliminaryVINs.PVIN FROM tblPreliminaryVINs INNER JOIN
tblVehicleJobs ON tblPreliminaryVINs.PVIN = tblVehicleJobs.SerialNum;

Aug 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
MLH
I forgot to mention that if you dbl-click the join in the QBE grid
(between the two tables) the join type is 1...

Only include rows where the joined fields from both tables are equal.

Also, I wanted to ask - since it doesn't matter which table's fields
are brought down for display, when you "select all records" and
press the delete key, Access just decides which table it wants to
delete from and does it. It doesn't delete from both tables. It only
deletes from one and its always the same one in my tests. How
does Access choose which one it wants to delete?
Aug 22 '06 #2

P: n/a
MLH
One more thing worthy of note is that I get an error
telling me "couldn't delete from specified tables" if
I run this delete query...

DELETE tblPreliminaryVINs.* FROM tblPreliminaryVINs INNER JOIN
tblVehicleJobs ON tblPreliminaryVINs.PVIN = tblVehicleJobs.SerialNum;

Access HELP had this to say, but none of it is applicable AFAIK...
Couldn't delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion
couldn't be completed.
Possible causes:

You don't have permission to modify the table. To change your
permissions assignments, see your system administrator or the table's
creator.
The database was opened for read-only access. The database is
read-only for one of these reasons:

You used the OpenDatabase method and opened the database for read-only
access.
The database file is defined as read-only in the database server
operating system or by your network.
In a network environment, you don't have write privileges for the
database file.
In Visual Basic, you used the Data control and set the ReadOnly
property to True.

To delete the data, close the database, resolve the read-only
condition, and then reopen the file for read/write access.
Aug 22 '06 #3

P: n/a
select queries and delete queries are different beasts.

use a subselect if you want to have control over which records get
deleted. Instead of using an inner join to the table you want to
specify the criteria, do something like

DELETE
FROM tblDeleteFromHERE
WHERE tblDeleteFromHere.PrimaryKey IN (SELECT PrimaryKey FROM SomeTable
WHERE....)

Aug 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.