UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Client Settings Against SQL Backend With Linked Tables, SQL Server 2012    
 
   
rsindle
post May 30 2019, 08:21 AM
Post#1


UtterAccess VIP
Posts: 1,700
Joined: 25-June 04
From: Northern Virginia


I've read a lot about the advanced client settings for Access and their effect (or lack thereof) when operating against a SQL Server backend. I have an app that uses linked tables, bound forms and ODBC, DSN-less connections.

I've seen some conflicting information and some that makes sense but doesn't go into much detail.
The options I'm interested in are:

Default record locking: (none/all/edited)
Open databases by using record-level locking: (Y/N)
OLE/DDE timeout: (30 sec)
Refresh interval: (60 sec)
Number of Retries: (2)
ODBC refresh interval: (1,500 sec)
Update retry interval: (250 msec)

My users periodically run into an "update" that fails and the timeout seems to take forever (I want to say something like 45 seconds or so, but I haven't timed it). My questions are:
1. Do the above settings have ANY effect on my application (SQL with bound forms) and in particular, the timeout effect when an update fails?
2. Is there a (simple) Server side setting that can allow SQL to tell Access ("Hey its not happening. Return control to your user")??
3. If there IS a setting, on SQL Server, is it a server specific setting or can I specify the database?
4. I've seen the ODBCTimeout property, but it seems that you can specify that on queries. How can/could that impact on a bound form where Access is just updating the record via the bound table/query?

I know there is a lot here, but I think a good answer would be massively helpful to a lot of users.
Here's hoping. iconfused.gif
Thanks in advance,
R-


Go to the top of the page
 
ranman256
post Jul 16 2019, 05:36 AM
Post#2



Posts: 903
Joined: 25-April 14



I have SQL linked tables, all default settings, And never have any problems.
If your users are getting wait problems and update fails, then you may need to try another ODBC driver, or an update.
Go to the top of the page
 
PhilS
post Jul 16 2019, 06:31 AM
Post#3



Posts: 634
Joined: 26-May 15
From: The middle of Germany


QUOTE
My users periodically run into an "update" that fails and the timeout seems to take forever (I want to say something like 45 seconds or so, but I haven't timed it). My questions are:
1. Do the above settings have ANY effect on my application (SQL with bound forms) and in particular, the timeout effect when an update fails?

Only the ODBC refresh interval, but it is not relevant to your question.

QUOTE
2. Is there a (simple) Server side setting that can allow SQL to tell Access ("Hey its not happening. Return control to your user")??

No.
The setting you are looking for is the QueryTimeout (same as ODBCTimeout below), but it is per connection (or even per query) and you cannot set it on the server side.

QUOTE
4. I've seen the ODBCTimeout property, but it seems that you can specify that on queries. How can/could that impact on a bound form where Access is just updating the record via the bound table/query?

As far as I know, this setting affects only the very query you applied it to, but not the updates that happen through bound forms.
However, you can set the QueryTimeout in the registry. It's in the path HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\ODBC
(Or similar, depending on version/edition of Access)
Maybe there is even a similar setting in the HKCU branch of the registry.


QUOTE
If your users are getting wait problems and update fails, then you may need to try another ODBC driver, or an update.

No.

The problem is most likely caused by an open form, listbox, or combobox bound to a query returning more records than Access does fetch automatically. Thus, the fetch remains incomplete, the server side cursor stays open and the lock on the underlying tables is not released. Any update query against the affected tables will fail with a timeout as long as this situation is not resolved by either fetching all the records or closing the form causing the issue.


--------------------
Go to the top of the page
 
PhilS
post Jul 18 2019, 07:29 AM
Post#4



Posts: 634
Joined: 26-May 15
From: The middle of Germany


QUOTE
QUOTE
2. Is there a (simple) Server side setting that can allow SQL to tell Access ("Hey its not happening. Return control to your user")??

No.

Well, while the No is correct when emphasizing the term simple in the question, it just occurred to me, that there might be a way to achieve this.

If the problem is caused in the way I suspect, it would be possible to set the LOCK_TIMEOUT property of the connection. Once again, this is difficult to achieve, because it is set per connection and (AFAIK) cannot be set in the connection string. However, you could write a Logon-Trigger on SQL-Server that applies your desired LOCK_TIMEOUT to each connection.

Still, I would rather recommend to fix the underlying issues causing the timeout problem in the first place!

This post has been edited by PhilS: Jul 18 2019, 07:30 AM

--------------------
Go to the top of the page
 
AlbertKallal
post Jul 18 2019, 10:13 PM
Post#5


UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada


As a general rule, I never had to touch the settings.

I actually recommend you don’t mess with the settings.

However, if you are seeing some large delays?

There are a few things to check.

First up, we assume a front end on each machine.

Next, there is a setting that I see that seems to get “flipped” on, and I don’t know why. This is the ODBC log file (not to be confused to have anything to do with SQL server log files).

This setting, if turned on by some workstations can really cause slow downs.

The set of panels you see when creating a new connection from the ODBC panel will eventually get you to this panel:




In above, you want to ensure that the check boxes above are un-checked. This log can really start to slow down things.

The other issue of course is the network connection. If you say have two locations, and a VPN is involved?

Then often they are quite slow.

Of course, the other issue is how large are some of the tables?

As a general rule, the #1 trick I can share for good performance?

Well, you want to NEVER launch a form without a “where” clause to a large table.

In other words, say a linked table to SQL server has 100,000 rows.

When you launch that form (that is bound to the linked table to SQL server) you “always” want to supply some kind of where clause.

If you don’t supply a where clause, then Access will start to pull the whole table – that can take a long time.

However, if you supply some kind of “where” clause, then ONLY the one record that matches the supplied “where” clause gets pulled down the network pipe.

So, when possible, present the user with a search form BEFORE you launch the form.

A really nice search form means:
Searching, and loading of the main form to edit/view the data will occur VERY fast.

You don’t want to launch a form, let it load huge numbers of records and THEN say have the user do a ctrl-f to find or search though 1000’s of records that you just loaded.

How fancy your search forms get it up to you.

But such forms encourage users to search before they add a new record.

After they are done editing they close the main edit form, and are now right back ready to search and do battle with the next customer/task.

So, I tend to build an Access search form say like this:




So, in above, the user has type in a few characters, and the results with “smi” are displayed. The above works fast – even on tables with say 500,000 rows – or more!!!

Now the user can click on any row to edit (the glasses icon), and we launch the “main” edit form, but we use this code behind those “glasses” buttons.

CODE
Docmd.OpenForm "frmEditMain",,,"ID = " & ME.id


So, now despite the “main” form being bound to a large table with say 500,000 rows, the form loads instant because of the above where clause. (Access client will only pull the one record into that table). One record is instant – even with 5, or 500,000 rows, the delay for the form is not noticeable). And we are talking about a form bound to that large SQL linked table.

After they are done working, they close that form and are right back to the handy dandy search form. They are ready to do battle with the next customer or task at hand.

However, having said all of the above?

You should not as a general rule see much delay when using SQL server as the back end. Of course the exception is when you needless launch a form without a where clause – that will cause access to pull huge numbers of records – it will pull the whole table.

With just a bit of extra work, a simple ask of what invoice number or whatever BEFORE you launch such forms not only gives you fantastic performance, but also tends to be far more user friendly anyway.

Last but not least? General update query on a large table via ODBC is VERY slow. In these cases, I suggest you consider a pass-though query, but that's another topic for another day.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 03:37 PM