Delete query lockup Access 2003

[allochthonous]

I built a multiple user front end/back end database for work. The front end is data entry with a couple dozen users, the back end contains the tables, queries, and reports. There are a dozen or so potential users of the back end, but realistically only a few use it. The back end exists on the network, with users shorcutting to it. The front end is a local application for all users.

There is a daily maintenance procedure that has to be executed to keep tables up to date. Some data is imported from an outside source. Part of this procedure involves a delete query which deletes marked records from a certain date range and then refills them back in.

The other day, for no apparent reason, this delete query would lock up every time it was run. There was no error message. I tried renaming the query, importing the query, and rebuilding the query, all to no avail.

My next solution was to be a Compact and Repair, but in order to do that, I needed to get everyone out of the database.

In the meantime, I copied the entire back end to my local machine, and the query ran fine.

A few minutes later, I went back to the network version, and the query ran. I assume that some (though I know not all) users had exited the database.

Any ideas what would cause a simple delete query to lock up?

PK

 

[Hap007]

Dear allochthonous,

A couple of comments.

1) I would not have any users using 'Shortcuts' to open the backend.
Instead, I would create a little Access mdb that sits on each users' PC. Inside this access DB, I would link to all the needed tables on the shared Access Backend.

2) Depending how you wrote the 'FrontEnd' having more than one user open a shared 'FrontEnd' can cause problems.

I might suggest that you copy the FrontEnd to each user's local PC, or to their own 'Network' folder and have each user open their own seperate copy.

Just my 2 cents from past experiences,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[allochthonous]

Thank you for replying.

1) The "back end" contains many queries and reports, so if I were to create a seperate db for them, it would not be so "little." I did not think there would be many reporting users, so I decided to make it shared via shortcut. I was also trying to avoid having to keep track of another "front end." As stated, this was my first major database and I am sure there are several things that I should have done differently. I can think of a few myself.

2) My "front end" (the data entry module) is not shared. It is copied down to each user's desktop.


What kind, if any, of problems can arise from sharing the "back end"/reporting db?

PK

 

[Hap007]

Dear allochthonous,

If you have reports and queries in the 'Backend' and the users open that mdb file directly, then it is not a backend.

The concept of a FrontEnd/Backend is the 'Frontend' has the Programs, queries, reports, etc and the BackEnd has only Tables and relationship definitions.

You can have several differsnt types of FrontEnds, but only one Backend with just data.

So, you are using a Hybrid concept... bad idea.

At worst, creat a new DB, link all the Tables in your backend, then import all the queries and reports from the backend, and if you must, leave this new DB on the server and have your user's shortcut open this new DB.
Then, delete all the queries and reports from the Backend and compact the backend.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[PHV]

I'm with Hap.
A BackEnd should only have Tables and RelationShips and NO USER should be able to open it.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[allochthonous]

OK. Perhaps I will do this when I get some time. Your method does not sound terribly difficult. The DB has been doing some odd things lately. Perhaps it is due to this "hybrid" design? Though I really do not understand what difference it would make.

Not to sound like a complete noob, but what do you mean by "relationship definitions?"

PK

 

[PHV]

Menu tools -> RelationShips ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hap007]

Dear allochthonous,

An example might be that you have 3 tables:
1) Customer
2) Invoice Header
3) Invoice Detail

Relationship might be.
a) Can not create/insert an invoice Header record unless it is related to a customer record
b) Can not create/insert an invoice detail record unless it is related to an Invoice Header record

Also, if setup properly, If you deleted an invoice header, all related Invoice detail records would then also be deleted automaticly.


Regarding the backend suggestions:
1) You asked why the lock up was possibly happening.
2) For my part, I have had this problem and I am telling you what caused it and how I solved it.
3) I/you could have spilt the queries and reports from the database in less time than it has taken to answer your questions.

Bottom line, if anybody opens a backend access db directly, they are asking for problems. Certainly if the users are using different versions of Access. Maybe today, maybe tomorrow, or maybe never minus one day. Bottom line, someday you will have a problem if multiple users open a backend shared DB and have full access.

I could have a lit cigarette and be working in a firecracker factory and tell you I never had an accident. Would that make me safe today or tomorrow?

Access is a great tool, but if your data gets corrupt or lost, that can be priceless, and never completely recovered.

Hope This Helps, Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[allochthonous]

Yeah, I do not use relationships like that in this database.

Now, should I have set them up anyway? Maybe. When building it, I saw no reason. I would have to really sit and think whether they would prove beneficial.

OK, you have made a fairly good point in rearranging my tables, if not for solving problems but preventing them. However, I get that it makes a difference, but to be honest I still don't really understand WHY it makes a difference.

So you think that pulling out the tables into to a new DB and pointing the reporting db (what i was calling the back end) to them, and then having reporting users shortcut to this will solve (prevent) issues?

I will need to look into it and make sure that everything will still work OK with the tables. It should not make any diffence, should it? Any action (update, make, append, etc) that was taking place on the table locally should still take place just fine as a linked table.

PK

 

[Hap007]

Dear PK,

Without seeing the code, I can not tell you for sure it will work, but most queries, Like 'Update' and 'Append' should work fine. I would guess that a 'Make' table query would need to be revised to create the table in a remote DB, so that type of query would need to be examined.

Delete and Append 'Records', should be no problem
Make or Modify or Delete 'Tables', could require some tweaking.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121