Access 2013 to SQL 2014 ado vs odbc linked tables

[gerrythefish]

Hi all,

I need advice on the most efficient way to connect to SQL to reduce user wait times.
I recently moved access 2013 backend to SQL Server 2012. We have about 150 users over the company WAN (typically 10 concurrent) and have some latency issues accross the network.

The front end is on everyone's computer.

I use DSNless odbc connections via SQL server native client 11 to link tables and views directly to access. I also use ADO, with the following connection string, mostly to execute action queries on the server, sometimes to bring back resulting recordsets:

DRIVER={SQL Server}; SERVER=PDX01;DATABASE=Portland_Data;Trusted_Connection=Yes;DataTypeCompatibility=80;Regional=Yes;

Normalised as best I can, (150 tables) typically a parent table with 1000 records and up to 10 child tables. The child tables typically having thousands of records with about 10 related to the parent record. Only 1 subform is loaded at a time, and is unloaded when the user switches tabs.

In general, a user opens the database, picks a parent table to load as a datasheet (filtered). They can edit the record or open a single form (with subform) to edit the parent and the connected child subform.

I get intermittant errors and havent been able to reproduce exactly what occurs, but on my connection down the hall from the SQL server, I almost never get these:

Error_Number Error_Description
2074 This operation is not supported within transactions.
-2147467259 [Microsoft][ODBC SQL Server Driver]Communication link failure
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
3151 ODBC--connection to '{SQL Server Native Client 11.0}PDX01' failed.
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

Im thinking about going all ADO, but is that the most efficient for this? Id rather not go the completely disconnected forms route.

If I do use ADO, do I load all 1000 records of the parent form in a recordset and use that for the datasheet list and the single form.
Do I have to rebuild the subform sql string for every change in current record in the parent form, or can I just let the forms linkmaster/linkchild settings take care of this.

I understand I could just try testing all this out, but results tend to be about the same from my office down the hall, and testing from a remote location introduces a lot of variability depending on network use.

Really Im just looking for help defining a strategy.

 

[CJ_London]

there should not be any significant difference between using ado or odbc. What is important is

1. to maintain a persistent connection to the db so you do not have the overhead of making it again and again

2. minimise network traffic with minimal recordsets - so don't have a form with a parent table that can be filtered and a subform with all the child records to again be filtered on the parent/child relationship (same goes for combo and listbox rowsources if these contain significant numbers of records).

It depends on how your application works to minimise network traffic - You might do this by a) opening the parent form with a limited recordset (use the where parameter of openform command) and instead of relying on the child/master link to filter the child records, have the subform recordset set to return no records and in the main form current event, some code to modify this to return the child records.

If users are constantly browsing through the recordset then perhaps load the data once when the app opens - but assumes users do not make changes.

Look at how websites work - they rarely return a full dataset unless it is small - users are required to enter something first (perhaps a search field, perhaps click a category option) before any data is returned.

 

[gerrythefish]

1. to maintain a persistent connection to the db so you do not have the overhead of making it again and again

Thanks - when I look on SQL monitor, each user has about 8 connections - should there be only 1 per user, or is this normal when a form/subform is open with multiple combo boxes. Often I link comboboxes to pass-thru queries.

 

[CJ_London]

I would have thought 1 per user. Access is not multi threaded so I don't see the benefit of multiple connections

 

[gerrythefish]

I would have thought 1 per user. Access is not multi threaded so I don't see the benefit of multiple connections

Ok - so how do I count connections on the server? If I look on the server at the activity monitor or run sp_who it looks like there is one connection/session per user plus one for each table/view/passthru query that user has opened.

I tested this by creating a brand new empty desktop app, linking to the server thru odbc, and then opening tables/views/pass thru queries one at a time. There are no code or forms in this test app.

 

[CJ_London]

so how do I count connections on the server

short answer is I don't know. My point was about making a persistent connection which you appear to have. If you have multiple connections per user, that might have an impact on sql server accessibility for other users of the server, but you would need to ask the question on a sql server forum or ask your IT dept.

 

[HiTechCoach]

Thanks - when I look on SQL monitor, each user has about 8 connections - should there be only 1 per user, or is this normal when a form/subform is open with multiple combo boxes. Often I link comboboxes to pass-thru queries.

According to the SQL Server DBA gurus I know, they say is totally normally. Not just with Access.

AFAIK, I would not worry about the number of connections.

 

[HiTechCoach]

My point was about making a persistent connection which you appear to have.

With a ACE/JET back end, the persistent connection keeps the locking database (.ldb/.laccdb) alive (created). It saves on the (small) overhead of creating this file when needed.


Curious, what is the benefit of a persistent connection to a SQL Server.

 

[CJ_London]

connecting to sql server still incurs the overhead of going through authorisation, processing password etc. If you have a form with several subforms and a few list or combo controls, it can add up