Split Your Database
By Richard Rost

9 months ago

How and Why to Split Your Microsoft Access Database

In this video, we'll talk about what splitting your database means, the benefits and drawbacks, and I'll show you how to do it.

Junior from Baton Rouge LA (a Gold Member) asks: I've heard a lot about the benefits of splitting my Access database. Can you tell me what this is all about?

Members

I'll show you how to move or rename your backend file, use the linked table manager, create multiple back end files, and manually create links to tables.

Extended Cut Video - Silver

Silver Members and up get access to an Extended Cut of this video. Gold Members can download the database template from class plus get access to the Code Vault. If you signed up on YouTube you have to contact me so I can set up your account here on my web site. If you're not a member, Join Today!

Links

Access Updater: https://599cd.com/XPBW6
Split Database Seminar: https://599cd.com/XU6JU
Security Seminar: https://599cd.com/SplitSem
Split & ACCDE Files: https://599cd.com/XJ9V4
Search Form: https://youtu.be/HSnHGQeX9so
Compact & Repair: https://599cd.com/1331
Simple Security: https://599cd.com/SimpleSecurity
Database Password: https://599cd.com/DatabasePassword

Other Articles

https://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database
https://support.microsoft.com/en-us/office/split-an-access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc
https://www.yourofficeanywhere.co.uk/info-hub/split-an-access-database/
http://www.databasedev.co.uk/split_a_database.html

Learn More

FREE Access Beginner Level 1
Access Level 2 for $1.00

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Mailing List - Get Emails When New Videos Released!
Consulting - Need Help with Your Database?

Questions?

Please feel free to post your questions or comments below or submit them via the TechHelp page.

problem in splitting Upload Images Link
Ibrahim Hasouna
24 days ago

I split my database, how ever the Front End get message "you cannot open any more databases " when loading, what is that ?

Alex Hedley
24 days ago

Do you have many tables or linked excel files?

Ibrahim Hasouna
24 days ago

37 table, no linked excel file

Show 14 More Replies...

Richard Rost
24 days ago

Did you run down the Troubleshooter?

Ibrahim Hasouna
19 days ago

Yes, I just finished the Troubleshooter but the problem still their, after google search, maybe the following is the case but I am not fully convinced, according to google this problem occurs when opening more that 2000 table at the start, which really is not my case, I attached the main menu form to a aggregate query that count some numbers from 11 sub-query, i used the main menu to track all required activates (due dates, delivery schedules, products shipping schedules, documentations .. etc), in the main menu as well there is several DLOOKUPs from another aggregate query's, the main reason i want central place to track all my actions, however; the number of DLOOKUPS around 20, I am way too far from 2000 tables, can't really figure out the main reason why this problem happening

Scott Axton
19 days ago

Ibrahim -

If I was trying to trouble shoot this - given what you have told us - I would create a little TemporaryMenu form.

The only thing I would put on that form is a button to open your main startup form. Then change out the TempForm to the startup form.

I have an idea that with the number of DLookups you are performing Access is trying to do to many calculations before the DB is fully loaded on startup.

If that works you may need to decide to create a more simple "Welcome" type splash form to get the db loaded then have it go to your main form with all the data included.

Scott Axton
19 days ago

Just curious - Did everything work on startup before you split the db?

Another thing to check is you network - Make sure the network folder is properly shared with read-write access.

Richard Rost
19 days ago

Scott has some valid points. And yes, I'm curious if this happened BEFORE you split it too. Another thing to try is to not run your events in the OnLoad (or OnOpen). Instead, use a Timer and kick off the calculations after the form has loaded completely - maybe after like 500 milliseconds. That sometimes makes a difference. I actually have a TechHelp coming up on this soon. Well, it's on the list, but not sure how soon. :)

Ibrahim Hasouna
18 days ago

hay scott, well, I tried your suggestion, the welcome form is opened without a problem in split database, however; once I click a button to open main menu the same message appears "Cannot open any more databases". And yes; every thing working fine before splitting database, the main menu opened without a problem. for network folder, I didn't share the database yet, I was about to do after splitting, so, i am now the only one working on it on my laptop,
Mr. Richard, about your suggestion, I removed the main menu Auto exec macro and entered the DB with Shift Key, Then I tried to open the main menu but the same problem is happening, I know you are talking about something else, you are talking about delaying the calculation after the form load is done, I wound will that work, waiting your techhelp, on other hand, if you have any other ideas kindly share

Ibrahim Hasouna
18 days ago

amazing, well, I played around a little bit, I opened the FE with Shift Key Down, I opened the aggregate query linked to main menu, try to run it the same message prompt again, I then tried to delete sub queries one at a time and run without luck, after reaching 6 sub queries, the query surprisingly run successfully, and the main menu form run as well, of course with missing info from deleted sub queries but it's a start, what do you think happen ?, is one query in split database can handle only 6 tables/queries or is this a case valid only in aggregate queries in split database, I find it strange, can you test that ?

Adam Schwanz
18 days ago

More than likely you have a query that is just too complicated causing issues.

I'd suggest two things

I would look into that last query you deleted that made it start working and see if there's any problems in it.

If not, looking around online there also is a limit to 2048 open table connections. "Note that every reference to a local table or query object uses 1 connection. A reference to a linked table uses 2 connections. A query that joins two linked tables will use 5 connections. If that query is getting called by many other queries in your union, the number adds up fast." Basically, saying your query is too complex. Looks like a frequently suggested fix is to use tabs or subforms and set the source objects with VBA when you are going to use it, otherwise set the source object back to nothing to limit complexity.

Scott Axton
18 days ago

Adam -
Great info to know. Where did you find that?

Scott Axton
18 days ago

Ibrahim
Even though you didn't share the database with other people yet the FOLDER permissions will have to be for Everyone with read & write permissions

To rule out network issues, you could try and move both the Front End and Back End into the same folder on your computer. You will have to re-link the tables but that will take the network out of the equation.

Adam Schwanz
18 days ago

Scott - I found it just by searching the cannot open database error, there's a few pages that talk about this. I also found a very interesting tool for this error. I'm kind of torn if I should even link it here but I think this may be a rare time it's worth it, it may help Ibrahim solve his problem.

DISCLAIMER: I don't know anything about this site or file, use at your own risk I've only just downloaded it to see if it could be a useful tool to keep around, it does work, and appears to be safe, but you're on your own. Here

Ibrahim Hasouna
18 days ago

Thank you very much, Much appreciated

Ibrahim Hasouna
17 days ago

One final thought since we came this far in this fruitful conversion, I just wonder; why the DB worked fine before splitting, is this limit only for splitting database ?! just curious

Richard Rost
14 days ago

Once the database is split, it has to open up connections to the other file. There is a limit to the number of connections although I've never hit it.

Remote Access Upload Images Link
Ibrahim Hasouna
25 days ago

I am trying to have a solution for remote access without converting to SQL server, I have small office with a few users who access the database on the local network, every pc have full Microsoft access, I want to be able to remote access the database, will NAS drive like synology will workout ? I mean; can I link the Frontend to the backend remotely, the BE of course will be on the NAS drive however the FE will be with each user outside the office

Adam Schwanz
25 days ago

I don't have any experience with access with a NAS drive. But you could look into VPNs or other remote software (remotepc or the hundred other options) to get to the internal server.

Richard Rost
24 days ago

I answered your question in this thread.

Split Database Info Upload Images Link
Richard Rost
6 months ago

OVERVIEW:
Splitting your database is dividing it into two or more files. One file, called the Front End, contains your forms, reports, queries, macros, and modules. The other, called the Back End, contains just your tables. Splitting your database makes it: Read More...

Relinking Tables Upload Images Link
Elaine Heltman
9 months ago

Hi Richard. I have the admin copy of the split database on my desktop, with the backend and encrypted frontend on the server. I'll be distributing the encrypted frontend to users. After I make changes to the admin copy, I'm having issues relinking the new encrypted frontend (SavedAs from the admin desktop copy to the server) to the backend on the server folder. I do not receive the right-hand options to relink, etc., only to refresh. What am I missing in this process? Thank you!

Richard Rost
9 months ago

You can't make that kind of a change in the encrypted ACCDE front-end. You need to relink the tables in YOUR ACCDB then make a new ACCDE file.

Elaine Heltman
9 months ago

So, if I understand correctly, I relink the frontend admin desktop version with the Server BE, then make the encrypted ACCDE file savedAs to the server?

Show 7 More Replies...

Richard Rost
9 months ago

You shouldn't really have to RELINK anything unless you MOVE the backend database. When you split the database, specify your SHARED SERVER FOLDER as the location for the backend tables. Once that's done, you can change your frontend all you want. Make a new ACCDE and the linked table references will stay the same.

Elaine Heltman
8 months ago

I'm having problems again with the double-clk DoCmd.openform code working in ACCDE files. The link address is correct, I double check it, and I've also tried linking using the IP address for the server. The code works flawlessly on the ACCDB file, regardless of its location - server folder or desktop. Thanks again!

Richard Rost
8 months ago

Interesting. What are you trying to open? Does it work on ANY system or is it a machine thing?

Elaine Heltman
8 months ago

From a form, I'm trying to open another form by double-clicking a selection in a combo box. The ACCDE file does not work on any system. I've tested with both the FE and BE on my desktop; linking FE on Desktop to BE on the server (via VPN); and from a Citrix server (not via VPN). Thanks!

Richard Rost
8 months ago

Can you MsgBox something from the same event? Do other events work?

Elaine Heltman
8 months ago

Good call. Looks like it's only VBA code that's not running. Certain forms do not open to a blank record, via DoCmd.GoToRecord , , acNewRec, as well as the many DoCmd.OpenForm codes I have in most forms. But, a MsgBox that runs from a Command button that prints a report of a file, does work.

Richard Rost
8 months ago

Trust issues? Are you running the database from a Trusted Folder?