Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Design Issue: Delete/append A Table That Is Used For Reports

Posted by: Ub78Nx Aug 15 2019, 01:56 PM

I recently inherited a database used by our shipping department. They have constant issues with it and I've been trying to update it for them. I think I have finally traced the source of the errors but I'm not sure how to fix it.

The database connects to our manufacturing database and pulls information about shipments that need to be made. This data runs through several queries that perform different cleanup tasks and is eventually put into a table called copy. The copy table is used as the basis of several reports in this database, and as the main data table for a different multi-user database. As part of the import process, the copy table is emptied of old data and the new import data is appended.

Data is imported 3 or 4 times a day. I've only known about this database for 2-3 weeks, and I've had to restore it from a major crash at least 4 times. My guess is that someone has one of the reports open or the multi-user database open during an import which causes the import process to fail on the empty table step (although I haven't 100% confirmed this, I was able to determine that none of the query tasks after the delete were completed after the most recent crash).

I don't know why the database is set up this way and I don't really have a ton of time to spend to redo it. I just need to get it to work without crashing. Is there any way I can keep the import process and the copy table, but set it up so that having a report open doesn't lock the table from being emptied and appended to? If that's not possible, does anyone have any design suggestions that allow the delete/append and a single data table for reports and the other database to link to.

Thanks for taking the time to look at this.

Posted by: theDBguy Aug 15 2019, 02:01 PM

Hi. Deleting and appending to a table shouldn't be affected by an open report bound to it. Which one was crashing, the database with the data or the "other multi user" database connected to it?

Posted by: Ub78Nx Aug 15 2019, 02:12 PM

It was the main database that crashed and had to be restored this morning and the person that contacted me said they were using that one and not the multi-user database when it happened.

I tried to run the delete query independently with a report open and it did let me. I guess I should have tried that earlier. Now I'm even more confused though.

Posted by: theDBguy Aug 15 2019, 02:30 PM

Okay, the next question would be, is the main database "split?" What that means is, do you have a separate file for the data and all users have their own file to access it?

Posted by: Ub78Nx Aug 15 2019, 02:44 PM

The database has 7 linked tables for some common tables we use, 4 ODBC tables for the manufacturing data, and the rest are local to the database. This is where most of the cleanup is done I believe. It's possible more than 1 person uses this at the same time (from a shared network location) even though I've told them not to.

It's called the planning database. The shipping planner is supposed to use it for reports and importing data. The 'multi-user' part is for the people assign pieces to individual trucks. They all use separate databases that link to the planning database for the 1 copy table I mentioned in my original post.

*edit* theDBguy - Thank you for your efforts so far. It's the end of my shift so I won't be checking this until the morning. I'll continue trouble shooting then, just didn't want you to spend extra time checking this when I can't provide answers.

Posted by: theDBguy Aug 15 2019, 02:49 PM

Hi. Not sure I understood all that you said but it sounds like you're saying you have a database (let's call it DB1) with ODBC links to production tables and in this database, you have some local tables, including the "copy" table you mentioned earlier. Then, you have another database (let's call this one DB2) linked to the "copy" table in DB1. I think you're also saying each user has their own copy of DB2, which is good, but everyone "shares" DB1, which is not good. If this is the case, and the database that was crashing was DB1, then yes, sharing a single copy of DB1 over the network probably contributes to the crashes. What you could do is "split" DB1 into separate data and logic files. Give each user their own copy of the logic (front end) file. Hope it helps...