ODBC connection problem - Cannot reconnect

[thpanos]

Hi
My code runs some update and append queries,
using the ontimer event of a form (timer interval = 90 seconds).

These are Saved Queries that are executed inside a transaction.

The problem is that after loosing connection
(err 3146 - i.e. disabling internet connection on my PC),
I cannot reconnect (i.e. re-enabling internet connection on my PC)
Trying to simulate error 3146, I disable and re-enable connection on my PC.

My code follows:

Function run_some_queries()

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim in_trans As Boolean
Dim log_file As String

Dim dbenger As Long
Dim nn As Long
Dim err_num As Long
Dim err_descr As String

On Error GoTo run_some_queries_Error
run_some_queries = True

Set ws = DBEngine(0)
Set db = CurrentDb

'Logs filename
logs_folder = CurrentProject.Path + "\logs"
log_file = logs_folder + "\" + "server_run_on_time_intervals.txt"

'Counter for error retries
nn = 0

in_trans = True
'Begin transaction
ws.BeginTrans
'run an update savedQuery on a linked table
db.Execute "Update_aLinkTable_SavedQuery", dbSeeChanges + dbFailOnError
If db.RecordsAffected > 0 Then
'Run some append queries (from linked tables to local tables)
db.Execute "append_from_LinkTable1_to_LocalTable1_SavedQuery", dbSeeChanges + dbFailOnError
db.Execute "append_from_LinkTable2_to_LocalTable2_SavedQuery", dbSeeChanges + dbFailOnError
db.Execute "append_from_LinkTable3_to_LocalTable3_SavedQuery", dbSeeChanges + dbFailOnError
db.Execute "append_from_LinkTable4_to_LocalTable4_SavedQuery", dbSeeChanges + dbFailOnError
db.Execute "append_from_LinkTable5_to_LocalTable5_SavedQuery", dbSeeChanges + dbFailOnError
db.Execute "append_from_LinkTable6_to_LocalTable6_SavedQuery", dbSeeChanges + dbFailOnError
End If
'Commit transaction
ws.CommitTrans
in_trans = False

run_some_queries_Exit:
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing
DoCmd.SetWarnings False
run_some_queries = True
Exit Function

run_some_queries_Error:
dbenger = DBEngine.Errors.Count - 1
err_num = DBEngine.Errors(dbenger).Number
err_descr = DBEngine.Errors(dbenger).Description
'Save the error info in the log_file
save_on_log log_file, "Error = " + vbTab + Mid(Str(err_num), 2) _
+ vbTab + "Retries = " + vbTab + Mid(Str(nn), 2) _
+ vbTab + "(" + err_descr + ") : " + vbTab + Now
'Roll back the transaction after 20 unsuccesful retries
If in_trans = True And nn > 20 Then
MsgBox "20 attempts made to reconnect. Operation failed.", vbCritical, "OCBC error"
'Rollback
ws.Rollback
Else
'Send sms to me - On fifth consecutive (and unsoved) occurence of this error
If nn = 5 Then
dfg = send_sms(err_num + vbCrLf + err_descr + vbCrLf + Str(Now))
End If
'Send sms to me - On 12th consecutive (and unsoved) occurence of this error
If nn = 5 Then
dfg = send_sms(Str(err_num) + vbCrLf + Str(Now))
End If
nn = nn + 1
'Delay execution 30 secs
xxx = Delay(30000)
End If

DoCmd.SetWarnings True
Select Case err_num
Case 3146, 3151
'Retry the SavedQuery where error occured
Resume
Case Else
MsgBox "Error = " & Err.Number & " (" & Err.Description & ") in function run_some_queries of Module HouseKeeping"
End Select
run_some_queries = False
End Function

Delay and save_log functions present in the above code follow:

Public Sub save_on_log(log_file As String, what_to_save As String)
Dim fh As Long
fh = FreeFile
If FileExists(log_file) Then
Open log_file For Append As fh
Else
Open log_file For Output As fh
End If
Print #fh, what_to_save
Close fh
End Sub

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function Delay(ms As Long)
'This is a function to delay processing, while letting
'the form update itself and using very little CPU%
Dim lTick As Long
lTick = GetTickCount() + ms
Do Until lTick <= GetTickCount()
DoEvents
Call Sleep(1)
Loop
End Function

Thanks in advance for your time.

Best Regards,

Theodore

 

[mdlueck]

Previously to present time, I used to have one ADO Connection object which was passed by reference from an application global object. If the connection would ever drop to the SQL BE DB, it would never reconnect. Windows 7 has a more severe power save than Windows XP, and was toasting the ADO Connection object. Thus my solution was to switch to creating a new ADO Connection object each time a call is received requesting it.

I only make use of DAO objects according to the following sample code:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

As you can see, I do not leave DAO.QueryDef objects lying around in the FE database, and as for connection I am passing in the ODBC connection string... not some persistent DAO Connection object. This scenerio does recover after the connection is restored.

And likewise since I have switched to disposable ADO Connection objects, ADO queries are now able to work after the connection to the BE DB is restored. FYI:

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

 

[thpanos]

Hi mdlueck,
thank you for your quick response.
The whole story tortured me for a while indeed.

Let me see if everything is well understood.
I have 1 update (on the SQL Server) and
some append queries (from SQL server tables to local tables).
My FE code runs every 90 seconds

So, I must setup my saved queries, and,
delete and recreate them via querydef objects
as it is described in your code

'Make sure they do not exist, delete the queries we need to build
Call dbutils_DeleteQueryDef(strQryNameBE)
Call dbutils_DeleteQueryDef(strQryNameFE)

'Attach to the FE DB
Set daoDB = CurrentDb()

'Build the BE PT Query
Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
With daoQDFbe
.Connect = ObjAppSettings.ODBCConnectString()
.SQL = strSQLbe
.Close
End With

'Append the name of the BE query onto the FE SQL to complete the FROM clause
strSQLfe = strSQLfe & " " & strQryNameBE & " AS t;"

'Build the FE Query
Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
With daoQDFfe
.SQL = strSQLfe
.Execute dbFailOnError
.Close
End With

on every run, or, only on error?

Regards
Theodore

 

[mdlueck]

Let me see if everything is well understood.
I have 1 update (on the SQL Server) and
some append queries (from SQL server tables to local tables).
My FE code runs every 90 seconds

Could you just come up with a disposable FE DB and restart the entire FE application "every 90 seconds"?

So, I must setup my saved queries, and,
delete and recreate them via querydef objects
as it is described in your code
on every run, or, only on error?

Not sure since in my design I do not leave any QueryDef objects in the DB long term. So I do not know if Access leaves a lingering connection as long as the QueryDef object remains in the DB.

At least the DAO.QueryDef accepts a string ODBC connection string rather than a complex object in the case of ADO Connection objects. A string is a string... never will "die", like ADO Connection objects do.

The only QueryDef objects I allow to remain in the FE DB for any length of time are the QueryDef objects which Forms are bound to. They interface between FE DB temp tables and the Forms. Once they are created on-the-fly just prior to the form which needs it opening, I leave those queries in the DB for the run-time duration.

Each time the application starts, a clean copy of the FE DB is deployed and started.

FE AutoUpdate with RoboCopy / XCopy
http://www.access-programmers.co.uk/forums/showthread.php?p=1214733#post1214733

 

[thpanos]

Hi,
I will try to transform my code
and inform you accordingly for the results.

Best Regards,

Theodore