SQL Server

[Perfac]

A question. I read a bit about the error "Can Not open any more databases." We work everyday and keep multiple back ups. We got this error and learnt about it but did not solve the problem. We solved it by loosing a days work and continue on the latest back up. We do not know if we created an infinite loop. We accept that we still fall short on experience but we learn everywhere to make the best choices in building the whole system on good strategy for the obvious reasons. The specific page has one subform, no combo boxes.



It is all about the weekly wagers. It needs information about employees(Addresses), a table to connect to calendar, the Paye Tax, other employee benefits and more. It does depend on many tables and queries to bring together the wage slip. I assume that the limit of 2048 is reached. Even after reading about it I battle to understand how we get to 2048. I don't think we got the error for that reason. For the moment we continue on the backup, but I guess we will have that error soon again.

From the start we learnt that if we set up SQL server as the back end and MS Access as the front end it will prevent some capacity issues Access has on itself. Can someone confirm these assumptions for me. From tomorrow we will put in time to connect SQL Server as back end to MS Access front end.

[isladogs]

You may be running out of available connections depending on how your database is designed
E.g recordsets opened but not closed after use.

Import the form from the attached database, load it and observe what happens when various items are used.
This may guide you into improvements needed.

As for SQL Server, it has many advantages in terms of scalability and stability.
However if your database design isn't good, converting to SQL Server won't fix it

Do all users have their own copy of the Access FE on their own computers?

[ranman256]

The error,Can Not open any more databases, is not you opening tAbles, it is external files (databases)
does this mean you already have an external source NOT internal to access?
no query is going to open 2048 tables.

Are all your tables internal? Is the database split?
what is your current setup?

yes SQL server does remove the limit on data size, and nothing I ever did needed to open 2000 tables.
Access makes a great Front end for this, but it can also accomplish a lot with out SQL server and use only internal tables (depending on your usage).

[isladogs]

The error,Can Not open any more databases, is not you opening tAbles, it is external files (databases)

The message is often a very misleading one.
Despite what it says, from my experience of the issue it isn't necessarily about external files.
In fact it can happen in databases with no external links if the design is flawed.
Having said that the issue is actually more likely to happen with split databases as these require more connections for each item in use

A google search will throw up a large number of hits on this topic from various forums

This recent thread may well be similar to your situation https://www.accessforums.net/showthread.php?t=71741

[Perfac]

Ranman
The database is split, our time will come when many users will use it on a network, and a couple of threads I read refer to that as the possible issue. You ask Are all your tables internal? It is split but no tables outside the backend if I understand correct. We will do more development tomorrow. Hope there is not the same error, but I expect it.