If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Deleting from MSDE - Help!!
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 |
#2
|
|||
|
|||
hi Tom,
Tom wrote: 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 |
#3
|
|||
|
|||
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" wrote in message ... hi Tom, Tom wrote: 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 |
#4
|
|||
|
|||
hi Tom,
Tom wrote: 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 "Tom" wrote 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" wrote in message ... hi Tom, Tom wrote: 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 |
#7
|
|||
|
|||
For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000 http://support.microsoft.com/default...b;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. " |
#8
|
|||
|
|||
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 "Gary Walter" wrote in message ... For "Access part," ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000 http://support.microsoft.com/default...b;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. " |
#9
|
|||
|
|||
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 "Andrea Montanari" wrote in message ... hi Tom, Tom wrote: 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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Book recommendations, please | Top Spin | New Users | 2 | March 1st, 2005 12:43 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Access Front End with MSDE SQL Server Instance Back end | Andy | General Discussion | 3 | August 31st, 2004 03:58 PM |