Solved

Disk or Network Error Handling

Posted on 2006-06-20
22
631 Views
Last Modified: 2008-01-09
Anyone have any good suggestions for handling this error?  I get it almost 2 or 3 times a week during the same time everymorning, (during scheduled jobs), and the IT guys are no help.  I know and they know it's a bad server and since I don't think it's going to be resolved anytime soon, I'd like the vba I'm running to not break at that error but start over or resume.  The main vba goes something like this:

Sub MySub
'map to the drive the mdb resides on just in case the computer is not already and open the database
'create a recordset and do some checks
'loop to check if recordset returns desired value and run then run Macro in database
'Quit Access
End Sub

So if I get a disk or network error can I do this? (Saw it posted here):
Sub
on error goto err_here
const cintLockErr as integer = 3043 'I think it's 3043--worried it won't always trap it
'map to the drive the mdb resides on just in case the computer is not already and open the database
'create a recordset and do some checks
'loop to check if recordset returns desired value and run then run Macro in database
'Quit Access

exit_err:
exit function

err_here:
if err = cintLockErr then
dbengine.idle
varTmrStart = timer
while timer - varTmrStart < 3000
wend
resume MySub
End Sub

Will this work or do I do a call to open up another instance of the database and run the same sub (not sure if I know how to even do that...I know how to call a macro) and then shut down the existing database that the error occured on?
0
Comment
Question by:error_prone
22 Comments
 
LVL 8

Expert Comment

by:infolurk
Comment Utility
Your pseudocode looks OK. Have you tried it?
You shouldnt need to put the MySub after the resume, resume on its own should resume at the line that caused the error.
0
 
LVL 84
Comment Utility
Disk or Network error can mean many different things, from a dropped connection to a corrupt database to a hardware problem on your workstation ... you'll need to determine all of these potential errors and handle them appropriately.

Anytime you get disk or network errors you are running the risk of corruption (it's probably the biggest cause of corruption among Access databases). Are your forms bound? If so, then Access is handling the data manipulation "behind the scenes", and this is where you could run into troubles (i.e. corruption troubles). If your forms are unbound, then you can certainly attempt to reconnect to the db and then write the data; this is pretty common stuff.

Your IT people won't fix the network? Seems to me this would be the place to start.
0
 

Author Comment

by:error_prone
Comment Utility
Ok, I've been trying a "wait and see" with this issue and now have come across a new problem I think. I have implemented the code and it's been going fine but that's because I haven't had any network connectivity issues lately. I've left some other databases without the error handling and they've run fine too, so that's how I know and plus the IT guys have been cleaning up old files on the server lately.  But 2 days ago, the actual sql server tables we are connecting to via odbc were moved to a different server and they told us the intermittent odbc call failed errors we are now getting are only temporary and last for only a minute or two. (Something about the tables refreshing every 15 mins). So this morning the databases with the new error handling code above just seemed to be frozen or hanging. How do I incorporate an ODBC call failure into the code as well? What's the number for that error?
Sub MySub
Dim MyVariables
on error goto err_here
const cintLockErr as integer = 3043
My Code
Application.Quit

exit_err:
exit function

err_here:
if err = cintLockErr then
dbengine.idle
varTmrStart = timer
while timer - varTmrStart < 3000
wend
End If
Resume
End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
error_prone,

Make a back up copy of the database right away! (If you haven't already)

A client of mine had this error. We imported all of the objects to a new database and all was well.

Now it make a liitle more sense thanks to LSMConsulting's post:

If the network hiccups, the database might become corrupted, hence the Access "Network" error.
0
 

Author Comment

by:error_prone
Comment Utility
Yea, have several copies since this is such a frequent problem, but there has to be some sort of error trapping for network connections and odbc failures.  I know the code above isn't working because I'm still having problems. All it needs to do upon those 2 errors is stop and start the code over again, but still can't get it to work.
0
 
LVL 84
Comment Utility
None here ...
0
 
LVL 84
Comment Utility
error_prone:

Is your application bound? If so, you have little control over how Access handles it's internal Connections to your database. If you're using a fully unbound app, then you can easily check for a valid connection before performing your data manipualtion, but from your postings that doesn't seem to be the case.

Have you seen these KB articles:
http://support.microsoft.com/kb/251254/
http://support.microsoft.com/kb/q303519/
http://support.microsoft.com/default.aspx?scid=kb;EN-US;283849 << specifically on this one, look at the "Why mdb Files may BEcome Corrupted"

0
 

Author Comment

by:error_prone
Comment Utility
No form/application at all.  It's a set of macros, (with various long-running queries), that I have set up to run automatically via vba.  The disk or network error is inevitable, (the dba has the tables refresh every so often so I lose connection).  I just recently changed it to this but not sure if it's going to work:

err_here:
If Err = cintLockErr Then
DBEngine.Idle dbRefreshCache
DoEvents
Set dbs = ws.OpenDatabase("MyDatabasePath", False, False, "MS Access;")
DoCmd.RunMacro "MyMacro"
dbs.Close
Set dbs = Nothing

End If
0
 
LVL 84
Comment Utility
Linked tables? If so, then you're still in the same boat.

If all you're doing is running queries, you could switch to ADO and run all them in a Transaction off your Connection object. You'd need to build a macro that would run a VBA function when the db opened. The ADO Connection Object also exposes an internal Error buffer; see the code marked DescriptionX for info on enumerating the Error collection for an ADODB.Connection

Function RunQueries() As Boolean

Dim con As ADODB.Connection

On Error GoTo Err_RunQueries

Set con = New ADODB.Connection

con.Open "Connection STring HEre"

con.BeingTrans
  con.Execute "SQL1"
  con.Execute "SQL2"
  etc etc
con.CommitTrans

Exit_RunQueries:
  Exit Function

Err_RunQueries:
  Select Case Err.Number
    Case
  End Select

Public Sub DescriptionX()
  Dim cnn1 As ADODB.Connection
  Dim errLoop As ADODB.Error
  Dim strError As String
On Error GoTo ErrorHandler
` Intentionally trigger an error.
  Set cnn1 = New ADODB.Connection
  cnn1.Open "nothing"
  Exit Sub

ErrorHandler:
` Enumerate Errors collection and display
` properties of each Error object.
  For Each errLoop In cnn1.Errors
  strError = "Error #" & errLoop.Number & vbCr & _
    " " & errLoop.Description & vbCr & _
    " (Source: " & errLoop.Source & ")" & vbCr & _
    " (SQL State: " & errLoop.SQLState & ")" & vbCr & _
    " (NativeError: " & errLoop.NativeError & ")" & vbCr
    End If
    Debug.Print strError
  Next
Resume Next

End Sub

Error code taken from: http://docs.sun.com/source/817-2514-10/Ch11_ADO41.html

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
Comment Utility
Sorry, hit Enter too soon ... the Error handler in the first sample should look more like this:

Err_RunQueries:
  Select Case Err.Number
    Case xxxx '/don't worry with it, just resume
      Resume Next
     Case yyyyy '/MAJOR error
       con.Rollback
       Resume Exit_RunQueries
  End Select
0
 

Author Comment

by:error_prone
Comment Utility
Nothing against the ADO suggestion, but for the sake of not wanting to forgo the existing set up, can I adjust this in anyway further to get around the disk or network error?  My theory is that if all I have to do manually to combat the error is "start over" then this should do the trick, shouldn't it?  Basically I want it to wait a while, close the existing instance of the database that is open and call the function again from a new instance of the database:

err_here:
If Err = cintLockErr Then
DBEngine.Idle dbRefreshCache
DoEvents
Set dbs = ws.OpenDatabase("MyDatabasePath", False, False, "MS Access;")
DoCmd.RunMacro "MyMacro"
dbs.Close
Set dbs = Nothing

End If
0
 
LVL 84
Comment Utility
In re-reading your post, there's a further problem here:

DoCmd.RunMacro "MyMacro"

Will run the macro in the current database, NOT the database opened on MyDatabasePath ... is that what you intended to do?

In order to run a macro in your remote database, you would need to Automate Access, or setup your remote db to run the Macro on startup ...

You can always put a loop in your code to have it wait, then try to run your error handler out:

err_here:

Dim dblStart As Date
Dim dblEnd As Double

dblStart = Now
'/wait for 5 seconds
dblEnd = CDbl(DateAdd("s", 5, dblStart))

Do
   '/wait
Loop Until CDbl(Now) > dblEnd

'/now try to reconnect
'/reset your vars
Set ws = Nothing
Set dbs= Nothing
Set ws = DBEngine.Workspaces(0)
Set dbs = ws.OpenDatabase("SomePath")
dbs.Execute "SQL Here"
'/DoCmd.RunMacro will NOT run a Macro in the dbs database ... you'd need to automate Access for this, which is even more network intensive.

Try putting this in different places in the Access event model ... for example, if you're calling this via button click, try it in the Click event and, if that doesn't work, try putting it in the Form's Error event ....

I still stand by my statement regarding your need to fix the network ... just a quick tour of the various Access newsgroups will show you the horror stories of Access + flaky network hardware.

0
 

Author Comment

by:error_prone
Comment Utility
Ok, here's exactly what I have from start to finish:  

Function AutoRun()

Dim ws As Workspace
Dim dbs As Database
Dim net, FSO
Dim strSql As String
Dim rstFirst, rstSecond As DAO.Recordset

On Error GoTo err_here
Const cintLockErr As Integer = 3146

Set ws = DBEngine.Workspaces(0)
Set dbs = ws.OpenDatabase("MyPath", False, False, "MS Access;")

DoCmd.RunSQL ("Delete * from MyFirstTable")
Set rstFirst = CurrentDb.OpenRecordset("MyFirstTable")
Do While 1 = 1
If rstFirst.RecordCount = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Make MyFirstTable")
Exit Do
End If
Loop

Set rstFirst = Nothing

DoCmd.RunSQL ("Delete * from MySecondTable")
Set rstSecond = CurrentDb.OpenRecordset("MySecondTable")
Do While 1 = 1
If rstSecond.RecordCount = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Make MySecondTable")
Exit Do
End If
Loop

Set rstSecond = Nothing

DoCmd.RunMacro ("Transfer")

Set dbs = Nothing
Application.Quit

err_here:
Dim dblStart As Date
Dim dblEnd As Double
dblStart = Now
dblEnd = CDbl(DateAdd("s", 15, dblStart))
Do
Loop Until CDbl(Now) > dblEnd
'If Err = cintLockErr Then
'DBEngine.Idle dbRefreshCache
DoEvents
Resume
'Set dbs = ws.OpenDatabase("MyPath", False, False, "MS Access;")
'DoCmd.RunMacro "MacroAutoRun" -->This macro just calls the function AutoRun above--My attempt here was just to start over
'dbs.Close
'Set dbs = Nothing
'End If

End Function

Not exactly sure how to input your changes
0
 
LVL 84
Comment Utility
And you want to run a Macro named Transfer in the database located at MyPath? Is that correct?

Your code opens the database at MyPath, but does nothing with it ... you open/set your dbs variable to that database but then you don't use the dbs variable in your code at all.

If you want to run a Macro in that database, here's what you'd have to do:

Function OpenNewAccessSession(DatabasePath As String, MacroToRun As String) As Boolean

Dim acc As Access.Application

Set acc = New Access.Application

acc.OpenCurrentDatabase DatabasePath
acc.Visible = True
acc.DoCmd.RunMacro MacroToRun

acc.Quit

End Function

You can use this directly to do it ... copy/paste that into a Standard Module in your database, then call it like this:

OpenNewAccessSession "Full path to your database", "Name of Macro to Run"


0
 

Author Comment

by:error_prone
Comment Utility
What I think you're saying is that I probably don't need to set the dbs like this, Set dbs = ws.OpenDatabase("MyPath", False, False, "MS Access;"), since I am already in the database and can just continue with the rest of the code.  But the top portion above the error code has always worked for me and I am really looking to just resume the code anytime I get hit with the Disk or Network error.  My intent in showing the entire function was to show what I would be resuming if I was in the error portion.  Hope this makes sense. Not looking to modify the initial function but really just wait and resume once hit with the error instead of it causing the code to break.
0
 
LVL 84
Comment Utility
I'm assuming your tables are linked ... you can try Refreshing the link, which would probably refresh the internal Access connection:

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
  If Left(tdf.Name, 4) <> "MSys" And Len(tdf.Connect) > 0 Then
    tdf.RefreshLink
  End If
Next

set dbs = nothing

You cannot directly interact with the internal connection that Access uses, except through the Functions and such provided by the Access interface (for example, you can issue the .Execute statement against connection). The best you can hope for is to allow Access to do this.

Still - you are treading in dangerous territory. Google the newsgroups for this error and you'll see what we've been saying - disc or network error is not an innocuous error, it's one of the nasty ones and eventually you'll end up with a hopelessly corrupted database ....
0
 

Author Comment

by:error_prone
Comment Utility
After I have refreshed the tables, how can I get to Resume where it left off...and if it cannot, then start the function over?  
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You can just try a Resume statement immediately after. If that doesn't work, you can use a Label with GoTo:

<your error code>
<relink>
Resume

or

ResumeHereOnError:
<code to run your macros and such>
<more code>

<your error code>
<relink>
Goto ResumeHereOnError
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now