Disk or Network Error Handling

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.

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.

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

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.

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.

None here ...

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"

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

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

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

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

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.

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

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"

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.

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 ....

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?