When Access uses SQL server as back end, are there still limitations to open connections?
When Access uses SQL server as back end, are there still limitations to open connections?
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.
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
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.
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