Split Your Database
By Richard Rost

13 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 view Extended Cut videos, when available. 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
4 months 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
4 months ago

Do you have many tables or linked excel files?

Ibrahim Hasouna
4 months ago

37 table, no linked excel file

Show 14 More Replies...

Richard Rost
4 months ago

Did you run down the Troubleshooter?

Ibrahim Hasouna
4 months 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
4 months 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
4 months 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
4 months 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
4 months 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
4 months 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
4 months 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
4 months ago

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

Scott Axton
4 months 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
4 months 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
4 months ago

Thank you very much, Much appreciated

Ibrahim Hasouna
4 months 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
4 months 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
4 months 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
4 months 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
4 months ago

I answered your question in this thread.

Split Database Info Upload Images Link
Richard Rost
10 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...

Show Older Comments...
View in Table Format