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 shortcutting 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

it may well have been your network. I occassionally get 'lock ups' but only when the be is in a network. As you said move it to a hd for testing and the problem goes away.

I suppose that is possible. Our network is funky sometimes.

The BE exists on the network, but the delete query resides within the BE, so its not like the query was calling out across the network, though.

Could any action by a back end or front end user interfere with the query?

PK

Normally your BE would only contain tables and referntial integrity. Is there a reason why your queries are not in the FE.

I guess the main reason is that I am self taught and this was my first DB project.

In the original design, I guess I had anticipated fewer BE users as well. I also did not want to have to worry about getting updated copies of both a data entry FE and a reporting FE to all users (who may or may not need both). It is hard enough to get updated data entry FE's to everyone.

Like I said, this was my first project, and everything I learned was either from a coworker with a moderate knowledge, the Access Bible, or from experience. I am sure that there are better ways of doing a lot of what i have going on, but all well.

Other than "bad design," could there be issues with the way I have it laid out?

PK

Ah - the joys of learning Access

So do you think it was network related, or poor design related?

What would cause a simple delete query to lock up like this?

PK