Results 1 to 13 of 13
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Open connections

    If our front end is connected to SQL Server as back end. Does it mean there is no limit to open connections?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Does it mean there are still a limit of 255 open connections?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes it does
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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 'th
    e way we see to disconnect subdatasheets is through "append queries"

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    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.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you have a form open (visible or not) you will have a connection (2 if linked) for each recordsource, rowsource and domain function.
    Are we right when we assume that when we click on such a combo box, it does the calculation and close it,
    No, the rowsource is still active so the connection is open

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too many open connections
    By Perfac in forum Programming
    Replies: 8
    Last Post: 02-24-2019, 06:18 PM
  2. Open connections
    By Perfac in forum Access
    Replies: 5
    Last Post: 07-27-2018, 03:44 AM
  3. DSN less connections
    By jaryszek in forum Access
    Replies: 23
    Last Post: 02-26-2018, 10:31 AM
  4. DSN-less connections, how?
    By tpcolson in forum Access
    Replies: 6
    Last Post: 02-17-2014, 07:53 PM
  5. How many FE connections to BE are too many?
    By ItsMe in forum Database Design
    Replies: 3
    Last Post: 12-06-2013, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums