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

    Open connections

    When Access uses SQL server as back end, are there still limitations 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
    You can still have a persistent connection if that's what you mean
    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 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi Colin, if that's your name. Currently we have issues with limits on open connections. We use a utility that counts the connections, but our current challenge does not make sense yet. We have a query that definitely
    opens very little compared to our other queries, but it still "freezes" the query. I just assume that I have heard in the past, connecting SQL server as the back end may eliminate the CONNECTION LIMIT challenge. It was always in my mind that sometime we will do that.

    Another question; If I use an expression i.e. Dlookup or Dsum in one query to return something from another query, Does the expression now open connections in the foreign object? My assumption is that it is not. My further assumption is that where there is a join of course then it causes more 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
    Hi Perfac if that is your name

    Ah. Nothing to do with having a persistent connection to the BE ... which is a good thing to do.

    I believe I may have supplied the utility to count the open connections a couple of months ago.
    https://www.accessforums.net/showthr...en+connections
    Although the use of SQL Server for the BE has many advantages, I can't see how that would affect the number of connections in the Access FE.
    Linked SQL tables will I believe use the same connections as linked Access tables.
    IIRC two connections per linked table compared to one for local tables (not checked)

    If your query is causing issues, look for ways of closing objects not in current use or modify your query so it is more efficient.

    You can confirm the answers to your new question by using the utility whilst such code runs
    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
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    My name is Hans. Yes I remember you supplied the utility, thanks. I assume that your last post says the limitations on open connections will not have any difference whether we use SQL server as back end or whether we use Access as back end. My assumption up to now was therefore wrong. We will use the utility to learn whether more connections are opened because of an expression.
    Within my limited experience, I feel good enough with the current challenge we have that I am right to think there are something else wrong here, since there are very little objects connected to this query and it still halts the specific query. We are boxing on.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    be aware that if using sql server as a back end, domain queries will be extremely slow - perhaps 100 times slower. Queries need to be optimised for use by the ODBC driver or passthrough query depending on which method you are using.

    passthrough queries do not use domain functions at all. Those used in queries via ODBC will work, but not at all efficiently - they need to fetch all the data from sql server back into the access environment and then apply the domain action - so instead of fetching one value back from sql server, it will be returning 100, 1000 or more records

    If you are considering a move to a sql server backend I would investigate now what the implications are - I suspect (from the info provided in your posts) that most of your queries will need to be rewritten

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

Similar Threads

  1. DSN less connections
    By jaryszek in forum Access
    Replies: 23
    Last Post: 02-26-2018, 10:31 AM
  2. Connections between forms and tables
    By lqangel in forum Programming
    Replies: 4
    Last Post: 09-15-2015, 07:25 PM
  3. Problems with Database connections
    By Goinfory in forum Misc
    Replies: 1
    Last Post: 06-22-2015, 06:13 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