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
> SQL Server Dropping Connection Randomly, Any Versions    
 
   
BruceM
post Mar 8 2019, 08:04 AM
Post#1


UtterAccess VIP
Posts: 7,865
Joined: 24-May 10
From: Downeast Maine


SQL Server 2016, Access 365

I posted a few days ago about a problem with an Access database and SQL Server back end, in which a form with two child subforms would fail when navigating from one record to another, with an error message that it couldn't find one of the subform record source queries, followed by the same message about the other subform's record source. Sometimes the failure would happen when first navigating to another record, sometimes after navigating through a few records, but invariably unless I happened to get to the record I needed, made the changes, and closed the form. Further investigation after the error messages showed that all connections to the back end had been lost.

I tried importing all objects into a new database, then rebuilt the form and subforms (no code in any of the three forms), but the problem persisted. Other databases have a main form with two child subforms, and do not crash. The problem database did not crash if I removed one of the two subforms. A poor connection to the back end seemed very unlikely since the crash happened every time, and only in this database.

I went back to a copy the original Access version (Access back end), and changed the linked tables to the SQL Server tables -- no pass-through queries, no SQL Server Views, just kept everything the way it was, and the problem went away. Due to the nature of the database it is unlikely any of the tables will have more than 100 records, so there are no performance issues. Also, the form/subforms in question are used only occasionally.

I will go back to the database one of these days and do some more experiments, such as replacing the row source queries with pass-through queries, and using views to join tables from the current database with the Employee table from another database in the same SQL Server instance. I am intrigued by the problem, but not enough to give it my full attention as long as there is a workable alternative.

Thanks especially to Albert Kallal for his efforts to help me sort out the problem in the original posting.

This is not a question (although if anybody has more ideas about the nature of the problem I would be glad to know), but rather a follow-up to a previous unsolved problem. Maybe it will help somebody some day.
Go to the top of the page
 
FrankRuperto
post Apr 14 2019, 05:58 PM
Post#2



Posts: 195
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Have you tried increasing the ODBC timeout parameters?

SQL_ATTR_CONNECTION_TIMEOUT connection timeout attribute with SQLSetConnectAttr.

SQL_ATTR_QUERY_TIMEOUT statement query timeout attribute with SQLSetStmtAttr().

The dropped ODBC connections could also be caused by something external going on, like Windows Updates or other background processes that cause lag. Observe what's going on in the Resource Monitor of taskmgr.

hth..Frank

--------------------
Currently supporting many pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
BruceM
post Apr 15 2019, 07:08 AM
Post#3


UtterAccess VIP
Posts: 7,865
Joined: 24-May 10
From: Downeast Maine


Since it happened only with one database, and happened invariably with that one, it seems the problem had to do with something other than the connection. Thanks for the thought. It is something to keep in mind, but in terms of the connection everything is working OK with all of the databases now.
Go to the top of the page
 
AlbertKallal
post Apr 15 2019, 08:26 PM
Post#4


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


There is one significant issue that I can think of that will cause this error to occur.

When you create a table (say at design time), then Access auto generates all of the columns as properties of the form.

However, if you add additional columns, or in code CHANGE the data source of the form with VBA code (say stuff in a SQL text with VBA), then Access does NOT re-generate this list of columns.

The result is your link master/child settings can get blown out.

The other result is that simple reference(s) to columns will now not work.

So, you might have

Debug.print me.TicketID

But, you find the above is not working. The way you can re-set and force the form to re-generate the columns is to open the form in design mode, and in property sheet “blank out” the data source, and you MUST then tab out. Now, tab back into the forms data source setting, and select the table, query (or even SQL),a and put it back in and again you MUST tab out. You often notice a delay when you tab, since access is now re-generating the columns.

Another way to avoid this issue is to use:

Me!TicketID

In this case, you’re accessing the field’s collection, and NOT the properties of the form. So if the form does not have a control of the same name (bound) to that column, then the FIRST example (dot) will work, but if you change the source of the form on the fly with VBA code, then you find that you can lose references to those columns by using “dot” notation.

The result of the above knowledge is RATHER significant.

(And you often see a form with a bunch of controls plied up in some corner – all set to invisible. This is done because the developer did not realize the above.

So, my coding rule is:

Me.SomeControl.

I NEVER use me.SomeColumnName.

Now, if I accident (or on purpose) delete that control from the form, I’ll get a compile error (if the control was not bound).

Me!SomeColumnName

Again, I use the bang for column names when I don’t want to care if a corresponding control is on the form.

The above will NOT break if no control of the same name does not exist, or you are shoving in and changing the forms SQL with VBA. When you change the forms reocrdsource with VBA, then the fields/columns are NOT re-generated as columns, and you can NOT thus rely on “me.columnName” (you can’t be sure dot notation will work anymore). This can rather EASY break your VBA code.

You will find that some me.Properties will go out of scope, and to be really fair, it more often that new columns that did not exist at design time are the ones you find missing.

However, me!ColumnName will always work.

So, if you have a copy of the broken application, try the forms data source re-trick – it will force that form to re-generate the properties that likely some code was using, and now is failing.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th April 2019 - 08:55 AM