DELETE Query Headache!

[Avatar]

Greetings,

I have the following DELETE query called qry_DELETEOldRecords:

DELETE tbl_RenewalImport.RenewalImport_ID, *
FROM tbl_RenewalImport
WHERE (((tbl_RenewalImport.RenewalImport_ID) In (SELECT RenewalImport_ID FROM qry_TEMPSelectQuery )));

qry_TEMPSelectQuery is:

SELECT tbl_RenewalImport.RenewalImport_ID
FROM (tbl_RenewalImport LEFT JOIN tbl_FormUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID) LEFT JOIN tbl_SubRtdUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE (((tbl_FormUpdate.[FormRecieved?]) Is Not Null)) OR (((tbl_SubRtdUpdate.[SubReturned?]) Is Not Null));

qry_TEMPSelectQuery returns 185175 records.

When i run or view qry_DELETEOldRecords, it is returning 278753 records - which is the entire contents of the table...

Copying the SELECT statement out of qry_DELETEOldRecords (marked in green) and createing a new query returns the correct 185175 records.

Making this new query into a make table query, returns the full 278753 records again...

An ideas whats going on here?

 

[Avatar]

Just for the heck of it, what happens if you remove the subquery from the WHERE clause, create a new query from it, and use a join?
On a large recordset I'm assuming it will be much faster. It *may* also select the correct records.

EDIT: Just join qry_TEMPSelectQuery to your delete query (without the subquery).

That makes the DELETE query return the correct number of records, but I get the standard (I assume it's standard..??) error "Specify the table containing the records you want to delete." Since you're not the first person to suggest this, perhaps my understanding of MS Access and SQL is wrong here. I'm under the impression that you can't use LEFT joins directly in a DELETE query...?

Tell us a little bit about the other tables involved, tbl_FormUpdate and tbl_SubRtdUpdate . Maybe something is going on there.
1. What do these tables represent?
2. How/when are they populated?
3. How many records are in each database?

Also, let us know the timing of when these macros are run. I have had issues in the past where I have a criteria selection form with a command button to run my queries/VBA code, and if I didn't refresh/requery before running, it would give me bad results.

Okay here goes.
The application as a whole contains 3 database. Client, Server and Archive.
Client: linked tables, Forms, a few queries and a couple of reports
Server: contains outstanding and ongoing cases
Archive: contains old and completed data

Server & Archive have identical structures.

Server has 7 tables in total.
The primary tables containing data are:
tbl_RenewalImport
tbl_FormUpdate
tbl_Letter1Update
tbl_Letter2Update
tbl_SubRtnUpdate
tbl_Comments

tbl_ImportDetails is a temporary table used during a process that is run weekly that imports approximately 50k records and it not linked to the other tables.

Not sure how to describe the relationships without taking a screenshot, but due to network restrictions that is not possible so here goes:

tbl_RenewalImport is joined to the other data tables (tbl_FormUpdate, tbl_Letter1Update, tbl_Letter2Update, tbl_SubRtnUpdate, tbl_Comments) with 1 to many relationships - tbl_RenewalImport has 1 the other can have many or no corresponding record.

Each join has "Cascade Delete Related Records" enabled.

tbl_RenewalImport_ID:
RenewalImport_ID – AutoNumber (Primary Key)
DateOfReport – Date/Time
AccountNumber – memo
CustomerName – memo
…Other record specific details

tbl_FormUpdate
Form_ID – AutoNumber (Primary Key)
FormReceived? – Yes/No
FormReceivedDate – Date/Time
RenewalImport_ID – Number (Foreign Key)

tbl_SubRtdUpdate
SubRtd_ID – AutoNumber (Primary Key)
SubReturned? – Yes/No
SubReturnedDate – Date/Time
RenewalImport_ID – Number (Foreign Key)

The only part that is updated during the batch file update process (tbl_ImportDetails) as detailed above is the tbl_RenewalImport table. The rest is updated by users. The database is used by approximately 30 users at a time. They update the remaining tables via a series of forms in the client database and Access Runtime 2003.

This Question:
Overview:
Due to the number of entries being added to this database, the queries used on the forms and reports take an extreme length of time once the number of records starts to exceed 250k. Since the vast majority of queries/searches are run against active cases, the Archive DB is used to hold all closed cases, and is only searched if the record can not be found in the live Server.

The DELETE Query:
This is the final stage in removing the records that have been added to the Archive during a weekly archive process.

EDIT:
I should add the archive is done manually at the moment, so this is new functionality on an existing system to improve the process and timings.
Also, since this DB predates me (some 6 years old) the Archive DB has a considerable number of records, so i might end up splitting that DB into different DBs based on year...

I think that is everything.. although I’m bound to have missed something…

 

[Joe4]

Wow, there is a lot going on there!

With the sheer volume of data you have (especially in the Archive database), it may be time to consider a more robust database program, like SQL. You can still use Access as the front-end in such a scenario. SQL tables would just house all your data (and if you like, you can do many of the tasks via SQL - Stored Procedures are nice because you can schedule those to happen automatically). Just something to consider...

Back to your question at hand.
You have your "Temp" query which is identifying the records you want to delete. It seems to do that right until you link it into your Delete query. As you said, maybe the sheer volume, or something else is going on, that is causing the issue. So here is an idea that might remove those factors from the process, and may actually even increase performance of the query.

Try writing the results of your "Temp" query to a new table. All you really need in it is the RenewalImport_ID field. Make this field the Primary Key and Index it. Now, use this new table instead of the Temp query in your delete query. Does that work?

Note, that I use this method with rather larger data sets to avoid ODBC time-out errors, and it usually works well. Typically, what I do is rather than re-create the table each time with a Make Table Query, I just delete the data in it and use an Append Query to write the new records to it. That way, I don't need to manually reset the field Properties and Indexes every time.

You can even create a pretty basic macro to delete all the records out of that table, then open your Append Query to add the new records to it (and then you can open and run your Delete Query also). So the whole process can happen with just a click of a button.

Hope that helps.

 

[Joe4]

In looking at your situation a little more, I wonder if the problem may be that the table that you are deleting the records from (tbl_RenewalImport
) is also used in the Temp query that you are linking to in determining which records to delete. So in a way, the table is being linked to itself. That may be problematic since you are deleting records from that table at the same time.

In any event, the method I described using the Temp table instead of the Temp query removes that variable from the equation. So if that is the problem, using the Temp table method should get around that issue.

I'll be interested in hearing if that works...

 

[xenou]

Or perhaps simpler with two queries:
Code:

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport
INNER JOIN tbl_FormUpdate
ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID
WHERE tbl_FormUpdate.[FormRecieved?] Is Not Null

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport
INNER JOIN tbl_SubRtdUpdate
tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE tbl_SubRtdUpdate.[SubReturned?]) Is Not Null

Since these don't work, I've had luck with Ansi-89 syntax where Ansi-92 fails. You can try that instead. Also check that the relevant fields are indexed. This sometimes helps.

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport, tbl_FormUpdate 
WHERE tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID
AND 
tbl_FormUpdate.[FormRecieved?] Is Not Null

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport, tbl_SubRtdUpdate 
WHERE tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
AND 
tbl_SubRtdUpdate.[SubReturned?]) Is Not Null

I think spelling errors are undesirable as users won't know if the field is actually spelled wrong or if its a typo in the documentation. But feel free to (mis)spell all you wish.

 

[xenou]

In any event, the method I described using the Temp table instead of the Temp query removes that variable from the equation. So if that is the problem, using the Temp table method should get around that issue.

I also use this strategy to make it less complicated for Access (Access can sometimes fall flat in updates and deletes, especially when nested queries get into play).

 

[Avatar]

With the sheer volume of data you have (especially in the Archive database), it may be time to consider a more robust database program, like SQL. You can still use Access as the front-end in such a scenario. SQL tables would just house all your data (and if you like, you can do many of the tasks via SQL - Stored Procedures are nice because you can schedule those to happen automatically). Just something to consider...

Personally, i would love that option. Unfortunately the people that control the SQL Servers and infrastructure (The Gate Keepers!) won't let anyone outside of their department have access unless they raise a project to have a webservice and aspx frontend created by them at a cost - something i cannot do.

Try writing the results of your "Temp" query to a new table. All you really need in it is the RenewalImport_ID field. Make this field the Primary Key and Index it. Now, use this new table instead of the Temp query in your delete query. Does that work?

Unfortunately that was one of the first things i tried. The make table returns all records just like the delete query.

Last Minute Edit:
Just tried removing IN and JOINing the queries. The datasheet view returns the correct number of entries, but as soon as you run it, it creates the table with all 278K records.. GRR