Free Lessons
Fast Tips
Topic Index
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Split Database < Nested Subforms | Update Query >
Split Your Database
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   2 years ago

Back to Split Your Database

Problem with Split DB Upload Images   Link  
Ibrahim Hasouna 
11 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
11 months ago
Do you have many tables or linked excel files?
Ibrahim Hasouna
11 months ago
37 table, no linked excel file
Richard Rost
11 months ago
Did you run down the Troubleshooter?
Ibrahim Hasouna
11 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
11 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
11 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
11 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
11 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
11 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
11 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
11 months ago
Adam -
Great info to know.  Where did you find that?
Scott Axton
11 months ago
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
11 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
11 months ago
Thank you very much, Much appreciated
Ibrahim Hasouna
11 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
11 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.
Ibrahim Hasouna
5 months ago
Okay, I re-build my entire database and split it as per instruction above, thank you
I shard the BE and FE on my mapped drive on my local network (I have a workgroup network) as per your instruction, employee copied the FE, all things works fine
However; the over all performance is not good, opening/Closing the forms, of course I have code and quires running with opening and closing, but when I was the only one working on it - before splitting - it was super fast and normal, what I am missing ? is it hardware requirement ?
Richard Rost
5 months ago
That depends on a lot of factors. A database that runs well on a standalone machine may run real slow over the network. I'd bet splitting it wasn't the problem. If you try running the DB yourself from across the network even as one file, it's still going to run slow. Time to optimize. Reduce queries and the number of records you load. I could do a whole class on just network-database optimization.
Ibrahim Hasouna
5 months ago
Yes Please, this is the most important step after splitting DB for Multi-User environment, It make all the lessons you taught us come to real life, I can across some factors when I searched for this topic, I want you sir to validate it, like using a wired network cable instead of wireless, related combo box in a form to be on got focus event instead of on current because it load all the quires when the form open, indexing and finally using SQL Server Express (Free version). is these factor will really have the biggest effect ? or rather buying newer faster hardware ? it's really frustrating after going this far and not reaching a satisfying performance, and it seems the performance is really an issue with even advance users, check this thread please and tell me what do you think
Richard Rost
5 months ago
We're having this same discussion in another thread. See: Wireless Networking and Access

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Split Your Database Comments

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/27/2022Rounding Errors
6/26/2022Calendar Seminar Students Only
6/24/2022Multi Combo One Field
6/23/2022Modal & Popup
6/21/2022Week of Year
6/16/2022Value List Combo
6/14/2022Project Budgets
6/14/2022Find Record

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn