Access Deleting from MSDE - Help!!

[Tom]

Hi

I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.

Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.

Can anyone help with this please?

Regards

Tom

 

[Andrea Montanari]

hi Tom,

Hi

I am a newbie to MSDE and am having a problem deleting records from
an MSDE backend using an Access query front end.

Our main data is stored on a UNIX server (informix type database) and
we link to it using ODBC. We load records to be updated into an
Access database and then use the Access table as a reference for
updating the MSDE database. This means I have a reference table in
Access that is joined to the MSDE table. The records that exist in
the Access table are the ones to be deleted from MSDE. When I use the
Access UI to create a 'delete' query, I get an error message (cannot
delete records from this table) when I try to run the query. I can
delete the records individually.
Can anyone help with this please?

when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Tom]

Hi Andrea

In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.

I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').

Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.

Regards

Tom

 

[Andrea Montanari]

hi Tom,

Hi Andrea

In the Access UI I have the two tables. The local Access table is
called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
[WORKS_ORDER], which is a works order number. It joins to the MSDE
table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
name, [WORKS_ORDER]. The join is set to select all from
'WORKS_ORDER_TO_DELETE', and only those that match on the join in
'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
set the query as a 'delete', so the only field in the UI grid is
labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
is set to 'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing
to do with MSDE: it seems to be when you are identifying the records
to delete through a join. I have also tried specifying all the
columns individually in the records to be deleted, with the same
result. I have also added the single field in the table
'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').

Sorry to be so long winded answering your question, but I thought it
best to give all the details rather than to try and guess at what you
meant.

unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Andrea Montanari]

more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )

INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )

SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID

DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2

is a valid T-SQL syntax...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Gary Walter]

Hi Tom,

I don't know if this will help.....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):

'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)

'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK

'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null

Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.

OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).

If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)

gary

Hi Andrea

In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.

I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually
in the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
it's delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').

Sorry to be so long winded answering your question, but I thought it best
to give all the details rather than to try and guess at what you meant.

Regards

Tom

hi Tom,


when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Gary Walter]

For "Access part,"

ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************

However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "

 

[Tom]

You are a star, Gary.

All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!

Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.

Regards

Tom

 

[Tom]

Hi Andrea

Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.

However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).

Many thanks for that.

Regards

Tom

hi Tom,

Hi Andrea

In the Access UI I have the two tables. The local Access table is
called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
[WORKS_ORDER], which is a works order number. It joins to the MSDE
table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
name, [WORKS_ORDER]. The join is set to select all from
'WORKS_ORDER_TO_DELETE', and only those that match on the join in
'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
set the query as a 'delete', so the only field in the UI grid is
labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
is set to 'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing
to do with MSDE: it seems to be when you are identifying the records
to delete through a join. I have also tried specifying all the
columns individually in the records to be deleted, with the same
result. I have also added the single field in the table
'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').

Sorry to be so long winded answering your question, but I thought it
best to give all the details rather than to try and guess at what you
meant.

unfortunately I'm not an Access guy and I do not feel very confortable
with Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Andrea Montanari]

found another way...
modifying the SQL statement to

DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID

did the trick, even with no primary key
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

 

[Tom]

I think your latest idea is probably the SQL code way of doing what I did in
the UI, ie setting the "unique record" property to 'yes', as the SQL behind
the UI is as follows.

DELETE DISTINCTROW tblLOCAL_WORKS_ORDER.*
FROM WORKS_ORDERS_TO_DELETE LEFT JOIN tblLOCAL_WORKS_ORDER ON
WORKS_ORDERS_TO_DELETE.WORKS_ORDER = tblLOCAL_WORKS_ORDER.WORKS_ORDER;

Regards

Tom

 

[Tom]

I think your latest idea is probably the SQL code way of doing what I did in
the UI, ie setting the "unique record" property to 'yes', as the SQL behind
the UI is as follows.

DELETE DISTINCTROW tblLOCAL_WORKS_ORDER.*
FROM WORKS_ORDERS_TO_DELETE LEFT JOIN tblLOCAL_WORKS_ORDER ON
WORKS_ORDERS_TO_DELETE.WORKS_ORDER = tblLOCAL_WORKS_ORDER.WORKS_ORDER;

Regards

Tom