If our front end is connected to SQL Server as back end. Does it mean there is no limit to open connections?
If our front end is connected to SQL Server as back end. Does it mean there is no limit to open connections?
No it doesn't …. and why have you started a new thread to ask this question for the second time
Last edited by isladogs; 03-07-2019 at 11:55 AM.
Does it mean there are still a limit of 255 open connections?
Are you talking about the Access connections to the back end or the back end connections available?
Access instance > BE then correct 255 limit
BE Connections for SQL Server according to this thread https://dba.stackexchange.com/questi...er-connections it appears its 32,767
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Thank you Minty and Colin. Since Jan 2018 we developed our application. It was only recently that we decided to split it, and I guess it needs 800 open connections as is. As can be seen on my previous thread the utility counts 15 times more open connections on the same queries when split. I read and assume that access is the most popular database. Our application is an accounting system. To get to our Balance sheet does take many connections. It is disappointing to me that the most popular system falls short so easily. We do have some experience to use other strategies like append queries, but we believe it complicates the system a lot especially for further development. We will throw a party if the reference you showed us means there may be more than 32000 open connections when connected to SQL server.
Colin I apologize to be slow in understanding. Am I right that you say even when our front end is connected to SQL server back end the limit for open connections is 255. If you say yes, then I am confused . Minty can I accept that the limit when connected to SQL is 32767. Colin, help me understand correct. Thank you guys.
There can be up to 32767 connections made to/within SQL Server in total from any source.
However no matter how often you ask, Access has a theoretical limit of 255.
In practice it has far less than that as performance normally slows to a crawl long before you hit the limit.
So no single Accesd FE will ever use all the capacity available in SQL Server.
Your problems are caused/exacerbated by the methods you have used in designing your database.
As I've been saying on and off for months, you need to review the structure of your database at the ground level.
I gave several suggestions in a previous thread including making sure you never use subdatasheets.
Moving the BE to SQL Server will help make your databases more stable and more secure but it will NOT solve your connections issue.
My recommendation would be that you pay an experienced developer to review your database structure and then follow their advice.
And before you think I'm looking for work....i'm not volunteering myself for the job.
Last edited by isladogs; 03-08-2019 at 04:12 PM.
to put it another way, a connection has two ends - one at the front, one ant the back. Each access front end can have up to 255 open connections. In a well designed front end, I would expect a maximum of perhaps 10 -20 open connections at any one time. Each sql server can have up to 32,767 open connections at any one time - so on the basis of an average of say 20 per front end, sql server can be connected to around 1640 front ends at any one time - although the SQL server performance will suffer long before that level is reached and will require extensive management to keep performance at optimum. Some time ago I developed an access front end/SQL server backend app that has around 400 users on a LAN and performance is fine.
So I don't think access falls short at all. Think about how websites work - they interact with rdbms's such as sql server, MySQL etc - they keep the number of connections to a minimum (perhaps only 2 or 3) and return the minimum amount of data. You need to learn about server side processing and optimisation. You are developing an accounting package - have you heard of/used transaction processing? If not, you need to investigate otherwise you run a strong risk of only updating one side of the double entry process in the event of a processing interruption - which is much more likely in a web environment.
The design considerations/limitations for a locally hosted application are significantly different from a web hosted application and different again if multiple clients are sharing the same resource (the back end). So you need to decide what environment you are developing for and use the appropriate tools and design principles. You may find that Access is not the right tool for the job - it is after all, primarily intended to be used for locally hosted applications on a hard wired LAN.
Colin. We have listened to your advice with the best respect. It resulted in our balance sheet to display after 6 seconds in stead of 5 minutes before. We learnt a lot along the way about append queries. We are open every second to learn more. We copy and store advice we get, and will follow it when it makes sense. We split our database in July 2018 temporarily. Now in March 2019 we split it finally for use and learnt of the open connections issues. We do our best to understand, so we will do what we can to have subdatasheets not connected.
Ajax. We hope not to cause frustration and we appreciate your time. Do I understand you correct if we assume that when our Access database front end is connected to SQL server back end. It counts in the front end to a maximum of 255 open connections, not taking into account open connections in SQL server at that moment. Further is our assumption right to understand that it counts open connections in the back end on its own up to 32 767 open connections, not counting the open connections in the front end?
We try to understand Colin exactly and do something in the way he suggests. In an accounting package to get to the Balance sheet, does bring most numbers together that was processed by all documents, and will therefor give us the challenge we have. What are we missing here? Ruben and me just had a discussion and from our experience the way we see to disconnect subdatasheets is through "append queries". I believe you when you say your 400 users runs well, and you don't come close to the challenge we have. Is there something it seems we are missing? Please tell me.
It is a long time since we made ourselves allergic to expressions like DSum and Dlookup that returns data from a different object. We do use it but with focus on not too many open connections.
without looking at yout app in detail, not possible to say. However, like Colin I have made a number of suggestions to which your response has been along the lines of 'we are happy with the way it works'. I note you have now dropped extensive use of domain functions which is a step in the right direction. I don't understand the implications of your comment 'the way we see to disconnect subdatasheets is through "append queries"
We deserve different recognition about our response. We have learnt a lot from you and Colin and we made it part of our life's, and I believe it is going to be of great value. Our system is proof that we mostly do what you guys suggest. My experience is that you give advice and suggestions with a general description, and not specific but as I said we definately apply what you suggest most of the time.
Your last sentence. Like a pyramid, one table can supply the information to a second level of object, maybe to a query, that query to a 3rd tier of query to eventually deliver the numbers to the final report like the Balance sheet. Bringing together previous period numbers and comparing to budget causes a serious amount of open connections, I believe. Lets say at that 3rd level we Append the result to a table, then the balance sheet gets the numbers from there. It will not keep the connections open from the second and first tiers of subdatasheets.
On our invoices there are sub forms, they have 3 combo boxes or selection boxes. We use VBA to reduce open connections. The VBA calculates certain fields through an event like afterupdate or on click. Are we right when we assume that when we click on such a combo box, it does the calculation and close it, it doesnt keep connections open? If ther is anything I seem to miss please tell me. Thank you.
if you have a form open (visible or not) you will have a connection (2 if linked) for each recordsource, rowsource and domain function.
No, the rowsource is still active so the connection is openAre we right when we assume that when we click on such a combo box, it does the calculation and close it,
Use the connection facility supplied by Colin to verify this. Create a new blank form, check the connections. Add a recordsource, check the connections, add a combo, check the connections, add a subform...etc. If you are using 3 lookups in your tables, opening that table will have 4 (8) connections. Or for your balancesheet, try temporarily removing something and see how it affects your connections.
I don't understand the pyramid structure/creation of your balance sheet - at least why you need to do this - I would expect two tables - an account table which would define the type of account (BS or P&L) and a transaction table for the transaction dates and amounts - and one simple query to get the amounts to any given day. if doing prior year you would have a second sum column for 'date-1 year'. For me, I include budgets and forecasts in the same transaction table with a field to define type -actual/budget/forecast.
You need to do your own investigation to determine why you are using the connection resource so heavily - perhaps you have twenty forms open all the time, perhaps you have connections left open in VBA, perhaps you are using lookups in tables.