Splitting Linked DBs (1 Viewer)

ErinL · Jun 26, 2019

Hello -

I have three separate databases that are all linked to each other and that are all used by multiple users. We have been using them for several years without any real issues. However, my company recently started updating PCs from Windows 7 to Windows 10. As they rolled out PCs with W10 we started seeing corruption issues. As the mix of W7 and W10 increased so did our corruption problems.

We have temporarily suspended the use of the databases until all W10 machines are put out on the floor. In my research I read about splitting databases and how that would help with corruption issues.

If I split all three databases but still have the links between the three will this still help with corruption? Each of the three use information from the others so linking is necessary.

I am also hoping that once we have all user PCs on the same operating system we will not have the issues we were seeing.

Any information anyone has on this subject would be greatly appreciated.

Thank you!

theDBguy · Jun 26, 2019

Hi Erin. I think we'll have to clarify a couple of things and try to manage your expectation a little bit. First, if you're saying you have "linked" database, it sounds like you already have a "split" database. However, to be clear, a split database means having a front end and a back end file. The front end contains the forms and queries and report; whereas, the back end only contains the tables (data). So, we'll need to understand what the three databases do, so we can better advise what a proper split configuration will be for your situation.

As for upgrading all machines to Windows 10; unfortunately, there is a known issue with using Access on Windows 10 (version 1803). This issue only affect Windows 10 machines. The only current solution is to use a registry fix as a workaround.

ErinL · Jun 26, 2019

Hello -

Currently (and how they have always been set up) each database has it's own set of tables saved in the .accdb file but also have links to tables in the other databases. For example, the database we use for loading trucks has it's own set of tables saved in the actual .accdb file which reference the information relevant to the inbound and outbound loads for our facility. This same database also three linked tables in it which are linked to the database we use for auditing to pull customer and order information for use in the loading process.

I have tested splitting all three databases on a separate drive that I have at my desk. Here, by splitting, I mean I actually did the split database wizard in Access to create front end and back end databases. I do not have any issues using the databases individually but I'm concerned that when we move them back out to the network and multiple people are using them all at the same time we will see problems arise again. I guess I don't have much faith in them at this point but am hoping I am wrong.

theDBguy · Jun 26, 2019

ErinL said:
Hello -

Currently (and how they have always been set up) each database has it's own set of tables saved in the .accdb file but also have links to tables in the other databases. For example, the database we use for loading trucks has it's own set of tables saved in the actual .accdb file which reference the information relevant to the inbound and outbound loads for our facility. This same database also three linked tables in it which are linked to the database we use for auditing to pull customer and order information for use in the loading process.

I have tested splitting all three databases on a separate drive that I have at my desk. Here, by splitting, I mean I actually did the split database wizard in Access to create front end and back end databases. I do not have any issues using the databases individually but I'm concerned that when we move them back out to the network and multiple people are using them all at the same time we will see problems arise again. I guess I don't have much faith in them at this point but am hoping I am wrong.

Hi. I just realized I forgot to mention one important "fact" about a properly split database. Aside from having a front end (with forms only) and a back end (with data only) files, each user "must" have their own copy of the front end for their own use (preferably on their local hard drive). Doing it this way will eliminate almost all the problems you get when sharing a single (non-split) Access database file.

So, for your situation, I would say you can put all the tables into one back end database file, and simply have three different front end files, but make sure each user uses his or her own copy of the front end on their Desktop.

ErinL · Jun 26, 2019

Thank you for your quick responses. Yes I am working with our IT dept and will have the back end databases on the network drive but will copy the front ends to the local drive of each PC. I am just about to test this scenario by putting the back ends that I created on my external drive out on the network and then running the front ends locally from my PC to make sure everything works. I will have to refresh the links through the linked table manager to the new location but that should be the only adjustment I need to make on the front end. If everything works for me, we will try it out on the floor and cross our fingers.

theDBguy · Jun 26, 2019

ErinL said:
Thank you for your quick responses. Yes I am working with our IT dept and will have the back end databases on the network drive but will copy the front ends to the local drive of each PC. I am just about to test this scenario by putting the back ends that I created on my external drive out on the network and then running the front ends locally from my PC to make sure everything works. I will have to refresh the links through the linked table manager to the new location but that should be the only adjustment I need to make on the front end. If everything works for me, we will try it out on the floor and cross our fingers.

Hi. Sounds like a plan. Good luck and please let us know how it goes.

ErinL · Jul 1, 2019

Hello again -

I am still fighting this problem. I split the databases, have the back ends out on the network and the front ends locally on the PCs. We continued to see the "unrecognized format" message and had to compact and repair to get them going again.

I had one linked table in the front end that was still a .mdb and I read an article that this could possibly cause corruption. So today I changed that link to an updated version (.accdb) and sent it back out for them to try again.

This was my last idea. If we continue to see the databases being corrupted I'm not sure what I will do next. :banghead::banghead::banghead:

theDBguy · Jul 1, 2019

ErinL said:
Hello again -

I am still fighting this problem. I split the databases, have the back ends out on the network and the front ends locally on the PCs. We continued to see the "unrecognized format" message and had to compact and repair to get them going again.

I had one linked table in the front end that was still a .mdb and I read an article that this could possibly cause corruption. So today I changed that link to an updated version (.accdb) and sent it back out for them to try again.

This was my last idea. If we continue to see the databases being corrupted I'm not sure what I will do next. :banghead::banghead::banghead:

Hi Erin. There is a "known" issue about this, and you're affected if your Operating System is Windows 10 Build 1803. Is it?

Micron · Jul 1, 2019

Not that this would help to avoid corruption, but no one has asked why you have 3 back end files. Are they so large/contain so many tables that the max file size becomes an issue? If not, aren't you are just complicating things? If a user only needs 2 tables from db A, 3 from db B and 2 from db C, their FE has links to those 7 but from a single BE. You don't allow them to see the tables anyway, right?

Mark_ · Jul 1, 2019

Hi ErinL,

One other question that may be relevant; are all computers connected to your network physically or are some wireless?

ErinL · Jul 1, 2019

Thank you for all the responses.

theDBguy - the latest image going out on our machines is build 1809.

Micron - I just recently split the databases when these issues started and I used the wizard in Access that does the split for you. So that is why there are three.

Mark - They are all a physical cable connection to the network. No wireless.

theDBguy · Jul 1, 2019

ErinL said:
theDBguy - the latest image going out on our machines is build 1809.

I think you may be affected. Were you aware of this issue?

ErinL · Jul 9, 2019

Sorry for the delayed response.

Yes I did see this information and I talked to our IT department about it. Unfortunately I work for a larger company and we are not able to do this work around as our IT is limited in the registry changes they can make and they are not allowed to make any server modifications.

At this point I have narrowed it down to one database that seems to have been causing the issues. We stopped using it and the two remaining databases have not seen the error in over a week. The plan now is to make the one we have stopped using a stand alone (not linked to the other databases and not pulling any info from them) and seeing if it is able to be used on it's own without seeing the error.

Thank you again for all your advice!

theDBguy · Jul 9, 2019

ErinL said:
Sorry for the delayed response.

Yes I did see this information and I talked to our IT department about it. Unfortunately I work for a larger company and we are not able to do this work around as our IT is limited in the registry changes they can make and they are not allowed to make any server modifications.

At this point I have narrowed it down to one database that seems to have been causing the issues. We stopped using it and the two remaining databases have not seen the error in over a week. The plan now is to make the one we have stopped using a stand alone (not linked to the other databases and not pulling any info from them) and seeing if it is able to be used on it's own without seeing the error.

Thank you again for all your advice!

Hi. Good luck!

Splitting Linked DBs (1 Viewer)

ErinL

Registered User.

theDBguy

I’m here to help

ErinL

Registered User.

theDBguy

I’m here to help

ErinL

Registered User.

theDBguy

I’m here to help

ErinL

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

Mark_

Longboard on the internet

ErinL

Registered User.

theDBguy

I’m here to help

ErinL

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread