The query is below. My database has 109 separate tables. The kinds of things that use database connections include: a) Forms and reports (including subforms and subreports): close any you don't need. I use the qryUnionProjects so as to not have to determine which actual table the key comes from because it can come from any of the tables, therefore combo boxes on
Instead, you can create a single instance of the database object once, the first time you need it in the application, and then use that instance for opening all the recordsets Then load and unload subforms dynamically in Change event. If you have not done this, then it will be very inefficient to pull the information you need from the data, thus leading you to bump into limits more easily. It would be pure luck whether the ceiling would be hit on a database call or a recordset or a table or whatever.
You mention specific query. In particular, if some of the wizards are run, their home database gets set as DBEngine(0)(0). In my >case the error has nothing to do with DLookup (which I don't use), or not >closing recordsets in code, or too many query-sourced comboboxes. Also, more table IDs are allocated in a split database than in a single-file database.
chris_lydon replied May 27, 2014 Hi Zare, Virtually all of the tables are linked with one to many relationships in some form yes, but I think the limit in Access 2003 Having said that If I am wrong about the unique values in your records then it will return duplicates if they exist. Access 2013 Cannot Open Any More Databases I know my queries aren't perfect, so whatever other suggestions you can make to make them more efficient let me know. Too Many Databases Open Ms Access Is there a way to get both pieces of data in a single SQL in the Function?
Recently, i solve the same problem. click site Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- It gives me about 250 spare recordset slots. There are plenty of Foreign Key fields around and about, linking to Primary Keys in other tables, so relationships page is pretty hectic.
b, c, and d are the most common culprits. If so try using ELookUp from Allen Brown Chris Ward Wednesday, May 22, 2013 7:06 PM Reply | Quote 0 Sign in to vote The wording of this error message is I will give the temp tables a go and see what happens. news For one bank, I created some very complex reports that used 2 years worth of transaction data.
A form's recordsource uses a table handle. Insert the results from your unions into temp tables and then use those to limit the number of tables per query. All subforms must be loaded always?
e) Referring to the RecordsetClone of forms (even where you do set the objects to nothing. Guest, Jan 11, 2006, in forum: Microsoft Access VBA Modules Replies: 6 Views: 970 Guest Jan 11, 2006 Error: Can't open any more databases Guest, Mar 19, 2006, in forum: Microsoft However, from my experience and from many others I've seen online, doing so makes you more vulnerable to the "Cannot open any more databases" error due to reaching the Jet limit More about the author I am glad you got your issue solved and thatthe forums are here for us to get help and understanding.
I am using Access2000. So I can change the query structure in the back end, and the front end is already linked through to it. I always learn a lot from your posts. chris_lydon replied May 27, 2014 Hi Jim Right now, the problem seems to be queries used as recordsources, so not being set programmatically at all.
There are only two databases open - the front-end and the back-end.