UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Design Issue: Delete/append A Table That Is Used For Reports, Access 2016    
 
   
Ub78Nx
post Aug 15 2019, 01:56 PM
Post#1



Posts: 19
Joined: 9-October 17



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.
Go to the top of the page
 
theDBguy
post Aug 15 2019, 02:01 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


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?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Ub78Nx
post Aug 15 2019, 02:12 PM
Post#3



Posts: 19
Joined: 9-October 17



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.
Go to the top of the page
 
theDBguy
post Aug 15 2019, 02:30 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


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?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Ub78Nx
post Aug 15 2019, 02:44 PM
Post#5



Posts: 19
Joined: 9-October 17



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.
This post has been edited by Ub78Nx: Aug 15 2019, 02:50 PM
Go to the top of the page
 
theDBguy
post Aug 15 2019, 02:49 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 10:02 PM