error in delete query

M

Maria

Hi!
This has problably been up before... I treid searching i previos posts
but I couldn't find an answer to my problem.

I have a database consisting from a few related tables. My whole
database works as a file registry to keep track of a large number of
symbols on my computer. There´s the main table with the ID set as
primary key and some descriptive words about every symbol.

Then theres other tables containing hyperlinks to the related files on
each symbol.

I want to make a delete query that deletes the symbol from the main
table and also all the related fields containing the files and other
data.

I have created a query that looks like this:

DELETE [Huvudtabell-Symboler].ID, [Huvudtabell-Symboler].*,
Bildoriginal.*, Sökordskoppling.*, Stenciler.*, [PDF-er].*
FROM ((([Huvudtabell-Symboler] LEFT JOIN Sökordskoppling ON
[Huvudtabell-Symboler].ID = Sökordskoppling.ID) INNER JOIN [PDF-er] ON
[Huvudtabell-Symboler].ID = [PDF-er].ID) LEFT JOIN Bildoriginal ON
[Huvudtabell-Symboler].ID = Bildoriginal.ID) LEFT JOIN Stenciler ON
[Huvudtabell-Symboler].ID = Stenciler.ID
WHERE ((([Huvudtabell-Symboler].ID)=136));

When I try to run the query I get the error message "Can not delete
data from specified tables". In some earlier post I saw that it could
be the result from a lack of primary keys although I have the ID as a
primary key in the main tables but I can't have the ID as a primary key
in the rest of the tables because there's duplicates, several fields
with the same ID.

Any ideas are welocome!
 
N

Nick 'The Database Guy'

Hi Maria,

Not quite sure what you mean about having duplicate IDs?

Sorry,

Nick
 
J

John Spencer

You can only delete records from one table at a time. UNLESS you set up a
relationship between the "main" table and all the other tables and set
Cascade Delete to true. If you don't do that, then you need to delete the
records in the subordinate table(s) and then delete the record(s) in the
"main" table.

DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

Without a relationship set up and cascade delete set to true, you would need
to do something like the following separate queries

DELETE DistinctRow BildOriginal.*
FROM BildOriginal
WHERE ID = 136

DELETE DistinctRow Sökordskoppling.*
FROM Sökordskoppling
WHERE ID = 136

<<< Repeat for all the subordinate (other) tables >>>

Finally
DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

With the relationships set up and cascade delete set to true, all you would
need is the last query.

Hi!
This has problably been up before... I treid searching i previos posts
but I couldn't find an answer to my problem.

I have a database consisting from a few related tables. My whole
database works as a file registry to keep track of a large number of
symbols on my computer. There´s the main table with the ID set as
primary key and some descriptive words about every symbol.

Then theres other tables containing hyperlinks to the related files on
each symbol.

I want to make a delete query that deletes the symbol from the main
table and also all the related fields containing the files and other
data.

I have created a query that looks like this:

DELETE [Huvudtabell-Symboler].ID, [Huvudtabell-Symboler].*,
Bildoriginal.*, Sökordskoppling.*, Stenciler.*, [PDF-er].*
FROM ((([Huvudtabell-Symboler] LEFT JOIN Sökordskoppling ON
[Huvudtabell-Symboler].ID = Sökordskoppling.ID) INNER JOIN [PDF-er] ON
[Huvudtabell-Symboler].ID = [PDF-er].ID) LEFT JOIN Bildoriginal ON
[Huvudtabell-Symboler].ID = Bildoriginal.ID) LEFT JOIN Stenciler ON
[Huvudtabell-Symboler].ID = Stenciler.ID
WHERE ((([Huvudtabell-Symboler].ID)=136));

When I try to run the query I get the error message "Can not delete
data from specified tables". In some earlier post I saw that it could
be the result from a lack of primary keys although I have the ID as a
primary key in the main tables but I can't have the ID as a primary key
in the rest of the tables because there's duplicates, several fields
with the same ID.

Any ideas are welocome!
 
M

Maria

Thankyou John,
The tables are related allready but I'm not sure where I set "Cascade
Delete" to true...

To the person who was wondering what I ment with duplicate ID's: In
table A theres ID=1 Name = "xxx" and so on...

In table B I have a relation to IDb that relates to ID in table A and
in table b there could be several files with the same ID. The same name
with several files

/Maria


John Spencer skrev:
You can only delete records from one table at a time. UNLESS you set up a
relationship between the "main" table and all the other tables and set
Cascade Delete to true. If you don't do that, then you need to delete the
records in the subordinate table(s) and then delete the record(s) in the
"main" table.

DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

Without a relationship set up and cascade delete set to true, you would need
to do something like the following separate queries

DELETE DistinctRow BildOriginal.*
FROM BildOriginal
WHERE ID = 136

DELETE DistinctRow Sökordskoppling.*
FROM Sökordskoppling
WHERE ID = 136

<<< Repeat for all the subordinate (other) tables >>>

Finally
DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

With the relationships set up and cascade delete set to true, all you would
need is the last query.

Hi!
This has problably been up before... I treid searching i previos posts
but I couldn't find an answer to my problem.

I have a database consisting from a few related tables. My whole
database works as a file registry to keep track of a large number of
symbols on my computer. There´s the main table with the ID set as
primary key and some descriptive words about every symbol.

Then theres other tables containing hyperlinks to the related files on
each symbol.

I want to make a delete query that deletes the symbol from the main
table and also all the related fields containing the files and other
data.

I have created a query that looks like this:

DELETE [Huvudtabell-Symboler].ID, [Huvudtabell-Symboler].*,
Bildoriginal.*, Sökordskoppling.*, Stenciler.*, [PDF-er].*
FROM ((([Huvudtabell-Symboler] LEFT JOIN Sökordskoppling ON
[Huvudtabell-Symboler].ID = Sökordskoppling.ID) INNER JOIN [PDF-er] ON
[Huvudtabell-Symboler].ID = [PDF-er].ID) LEFT JOIN Bildoriginal ON
[Huvudtabell-Symboler].ID = Bildoriginal.ID) LEFT JOIN Stenciler ON
[Huvudtabell-Symboler].ID = Stenciler.ID
WHERE ((([Huvudtabell-Symboler].ID)=136));

When I try to run the query I get the error message "Can not delete
data from specified tables". In some earlier post I saw that it could
be the result from a lack of primary keys although I have the ID as a
primary key in the main tables but I can't have the ID as a primary key
in the rest of the tables because there's duplicates, several fields
with the same ID.

Any ideas are welocome!
Click to expand...
 
J

John Spencer

Open up the data base in design view
Select TOOLS; Relationships from the menu.
Add in the tables you wish to use.
Drag from the main table ID to the Subordinate table ID
When the relationship is created, Access should open a dialog box where you
can set cascade delete. If it doesn't allow you to do that, then your ID in
the main table is not set to be the primary key (or at least doesn't have a
unique index set on it).

If the relationship is already set, double-click on the join line. That
should bring up the dialog box that will allow you to set cascade delete.


Thankyou John,
The tables are related allready but I'm not sure where I set "Cascade
Delete" to true...

To the person who was wondering what I ment with duplicate ID's: In
table A theres ID=1 Name = "xxx" and so on...

In table B I have a relation to IDb that relates to ID in table A and
in table b there could be several files with the same ID. The same name
with several files

/Maria


John Spencer skrev:
You can only delete records from one table at a time. UNLESS you set up a
relationship between the "main" table and all the other tables and set
Cascade Delete to true. If you don't do that, then you need to delete the
records in the subordinate table(s) and then delete the record(s) in the
"main" table.

DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

Without a relationship set up and cascade delete set to true, you would
need
to do something like the following separate queries

DELETE DistinctRow BildOriginal.*
FROM BildOriginal
WHERE ID = 136

DELETE DistinctRow Sökordskoppling.*
FROM Sökordskoppling
WHERE ID = 136

<<< Repeat for all the subordinate (other) tables >>>

Finally
DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

With the relationships set up and cascade delete set to true, all you
would
need is the last query.

Hi!
This has problably been up before... I treid searching i previos posts
but I couldn't find an answer to my problem.

I have a database consisting from a few related tables. My whole
database works as a file registry to keep track of a large number of
symbols on my computer. There´s the main table with the ID set as
primary key and some descriptive words about every symbol.

Then theres other tables containing hyperlinks to the related files on
each symbol.

I want to make a delete query that deletes the symbol from the main
table and also all the related fields containing the files and other
data.

I have created a query that looks like this:

DELETE [Huvudtabell-Symboler].ID, [Huvudtabell-Symboler].*,
Bildoriginal.*, Sökordskoppling.*, Stenciler.*, [PDF-er].*
FROM ((([Huvudtabell-Symboler] LEFT JOIN Sökordskoppling ON
[Huvudtabell-Symboler].ID = Sökordskoppling.ID) INNER JOIN [PDF-er] ON
[Huvudtabell-Symboler].ID = [PDF-er].ID) LEFT JOIN Bildoriginal ON
[Huvudtabell-Symboler].ID = Bildoriginal.ID) LEFT JOIN Stenciler ON
[Huvudtabell-Symboler].ID = Stenciler.ID
WHERE ((([Huvudtabell-Symboler].ID)=136));

When I try to run the query I get the error message "Can not delete
data from specified tables". In some earlier post I saw that it could
be the result from a lack of primary keys although I have the ID as a
primary key in the main tables but I can't have the ID as a primary key
in the rest of the tables because there's duplicates, several fields
with the same ID.

Any ideas are welocome!
Click to expand...
 
M

Maria

Thankyou,
It works like a charm now! Access was still quite new to me when I set
up the relationships so I didn't quite get ithe meaning of all the
options then and by now I had forgot about that dialogue box. But it
all makes sence now.

/Maria


John Spencer skrev:
Open up the data base in design view
Select TOOLS; Relationships from the menu.
Add in the tables you wish to use.
Drag from the main table ID to the Subordinate table ID
When the relationship is created, Access should open a dialog box where you
can set cascade delete. If it doesn't allow you to do that, then your IDin
the main table is not set to be the primary key (or at least doesn't havea
unique index set on it).

If the relationship is already set, double-click on the join line. That
should bring up the dialog box that will allow you to set cascade delete.


Thankyou John,
The tables are related allready but I'm not sure where I set "Cascade
Delete" to true...

To the person who was wondering what I ment with duplicate ID's: In
table A theres ID=1 Name = "xxx" and so on...

In table B I have a relation to IDb that relates to ID in table A and
in table b there could be several files with the same ID. The same name
with several files

/Maria


John Spencer skrev:
You can only delete records from one table at a time. UNLESS you set up a
relationship between the "main" table and all the other tables and set
Cascade Delete to true. If you don't do that, then you need to delete the
records in the subordinate table(s) and then delete the record(s) in the
"main" table.

DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

Without a relationship set up and cascade delete set to true, you would
need
to do something like the following separate queries

DELETE DistinctRow BildOriginal.*
FROM BildOriginal
WHERE ID = 136

DELETE DistinctRow Sökordskoppling.*
FROM Sökordskoppling
WHERE ID = 136

<<< Repeat for all the subordinate (other) tables >>>

Finally
DELETE DistinctRow [Huvudtabell-Symboler].ID
FROM [Huvudtabell-Symboler]
WHERE ID=136

With the relationships set up and cascade delete set to true, all you
would
need is the last query.

Hi!
This has problably been up before... I treid searching i previos posts
but I couldn't find an answer to my problem.

I have a database consisting from a few related tables. My whole
database works as a file registry to keep track of a large number of
symbols on my computer. There´s the main table with the ID set as
primary key and some descriptive words about every symbol.

Then theres other tables containing hyperlinks to the related files on
each symbol.

I want to make a delete query that deletes the symbol from the main
table and also all the related fields containing the files and other
data.

I have created a query that looks like this:

DELETE [Huvudtabell-Symboler].ID, [Huvudtabell-Symboler].*,
Bildoriginal.*, Sökordskoppling.*, Stenciler.*, [PDF-er].*
FROM ((([Huvudtabell-Symboler] LEFT JOIN Sökordskoppling ON
[Huvudtabell-Symboler].ID = Sökordskoppling.ID) INNER JOIN [PDF-er]ON
[Huvudtabell-Symboler].ID = [PDF-er].ID) LEFT JOIN Bildoriginal ON
[Huvudtabell-Symboler].ID = Bildoriginal.ID) LEFT JOIN Stenciler ON
[Huvudtabell-Symboler].ID = Stenciler.ID
WHERE ((([Huvudtabell-Symboler].ID)=136));

When I try to run the query I get the error message "Can not delete
data from specified tables". In some earlier post I saw that it could
be the result from a lack of primary keys although I have the ID as a
primary key in the main tables but I can't have the ID as a primary key
in the rest of the tables because there's duplicates, several fields
with the same ID.

Any ideas are welocome!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

importing from form to sub table 0
manual deletion of records in datasheet based on joined tables 5
Joins and Query Speed 1
Query joins not created in front end query grid 2
Deleting Duplicate Records in a single table 1
query to filter by current record id? 4
Query for Forecasting Fuel Usage 1
Multiple Table Query Is Blank 1

Top