Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-05-2017, 04:04 PM   #1
gerrythefish
Newly Registered User
 
Join Date: Oct 2014
Location: From Dublin but live in Portland, Oregon
Posts: 28
Thanks: 6
Thanked 2 Times in 2 Posts
gerrythefish is on a distinguished road
Access 2013 to SQL 2014 ado vs odbc linked tables

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_Connec tion=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.

gerrythefish is offline   Reply With Quote
Old 05-06-2017, 06:45 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,383 Times in 3,278 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-06-2017, 08:50 AM   #3
gerrythefish
Newly Registered User
 
Join Date: Oct 2014
Location: From Dublin but live in Portland, Oregon
Posts: 28
Thanks: 6
Thanked 2 Times in 2 Posts
gerrythefish is on a distinguished road
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

Quote:
Originally Posted by CJ_London View Post

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.

gerrythefish is offline   Reply With Quote
Old 05-06-2017, 09:04 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,383 Times in 3,278 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

I would have thought 1 per user. Access is not multi threaded so I don't see the benefit of multiple connections
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-08-2017, 11:58 AM   #5
gerrythefish
Newly Registered User
 
Join Date: Oct 2014
Location: From Dublin but live in Portland, Oregon
Posts: 28
Thanks: 6
Thanked 2 Times in 2 Posts
gerrythefish is on a distinguished road
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

Quote:
Originally Posted by CJ_London View Post
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.
gerrythefish is offline   Reply With Quote
Old 05-08-2017, 02:18 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,383 Times in 3,278 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

Quote:
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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-09-2017, 10:12 AM   #7
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,243
Thanks: 8
Thanked 155 Times in 143 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

Quote:
Originally Posted by gerrythefish View Post
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.

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Old 05-09-2017, 10:20 AM   #8
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,243
Thanks: 8
Thanked 155 Times in 143 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

Quote:
Originally Posted by CJ_London View Post
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.
__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Old 05-09-2017, 03:17 PM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,383 Times in 3,278 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access 2013 to SQL 2014 ado vs odbc linked tables

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to Update ODBC linked tables on ms access vince1818 Tables 5 05-03-2015 01:02 AM
[SOLVED] Access 2013 Linked tables - Open/close form extremely slow Rx_ Code Repository 0 09-25-2013 02:08 PM
ORACLE linked tables to ACCESS through ODBC connection shows #DELETED Gouri Dutta Tables 0 09-22-2011 08:18 AM
Reconnecting ODBC linked tables in MS Access via VBA dodulo Modules & VBA 13 10-27-2009 01:59 PM
Question Slow access 2003 search over ODBC SQL Server linked tables eligio General 9 11-21-2008 11:32 AM




All times are GMT -8. The time now is 07:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World