Resume Next versus GoTo

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,
About halfway down are the lines:

'REPLACE ORIGINAL BE WITH NEW BE (UNZIPPEDFILE)
On Error Resume Next
FileCopy UnzippedFile, OriginalPath 'e.g., C:\fpsdata.mdb to
C:\Access97\fpsdata.mdb
' *****************************************

I had originally had this as:
On Error GoTo ReplaceOriginal_err
FileCopy UnzippedFile, OriginalPath 'e.g., C:\fpsdata.mdb to
C:\Access97\fpsdata.mdb
' *****************************************

If I use On Error GoTo ReplaceOriginal_err, the code errors-out at that
point with an error description of "Permission Denied". If I use On Error
Resume Next, the code runs fine. If I look in Windows Explorer at the point
of "Permission Denied", the user's local BE is open (.ldb exists) -- which
may explain the "Permission Denied". The user's FE should be linked to
Tempdata.mdb at that point. And, in fact, if I put a breakpoint at On Error
GoTo ReplaceOriginal_err -- just before FileCopy UnzippedFile,
riginalPath -- the FE is, in fact, linked to Tempdata.mdb and the local BE
is not open (no .ldb). Must be a timing thing, I guess? -- The linking to
Tempdata.mdb has not fully completed before the next line of code (FileCopy
.... ) begins?

The setup is a LAN with all the LAN FE's linked to the BE on P-drive.
Other users bring in their laptops to update their personal BE's. Their FE's
are linked to their own C-drive.
Certain records have flags and if the FE is linked to a C-drive, the flag is
turned on when the record is changed. (If the FE is already linked to the
LAN BE, then the flag is not turned on when the record is changed). When the
user wants to update their laptop -- without losing any of their own new
records, the code first appends all their flagged records to Temp tables in
their own FE. The code then copies the LAN BE to their local C-drive, makes
a second copy of the LAN's BE - also in their C-drive, links to the first
copy, replaces their local BE (which is located in a separate folder) with
the second copy, relinks to their original BE (now replaced with the copy of
the LAN's BE) and then re-inserts their new records. There's also a
procedure they can select for transferring their new records, including
Billing Records, to the LAN's BE. Transferring Billing Records is restricted
to one time at payroll; users, however, can update the BE at anytime -- they
just don't want to lose new records that have'nt been transferred yet.

Case Is = 4 'User selected Update with Billing Records to protect

'SourceFile = "P:\fpsdata.mdb"
'UnzippedFile = "C:\fpsdata.mdb"
'TempFile = "C:\tempdata.mdb"
'OriginalPath = "C:\Access97\fpsdata.mdb"

'1. run queries to fill FE TempTables
'2. FileCopy SourceFile, UnzippedFile
'3. FileCopy UnzippedFile, TempFile
'4. Link to TempFile
'5. FileCopy UnzippedFile, OriginalPath
'6. Link to OriginalPath
'7. run queries to restore new records

Select Case GetLinkedDBName("Names")
Case Is = "C:\Access97\fpsdata.mdb"
SourceFile = "P:\fpsdata.mdb"
UnzippedFile = "C:\fpsdata.mdb"
SourceInUse = "P:\fpsdata.ldb"
TempFile = "C:\tempdata.mdb"
OriginalPath = "C:\Access97\fpsdata.mdb"
Case Is = "C:\Access97\Petedata.mdb"
SourceFile = "P:\Petedata.mdb"
UnzippedFile = "C:\Petedata.mdb"
SourceInUse = "P:\Petedata.ldb"
TempFile = "C:\tempdata.mdb"
OriginalPath = "C:\Access97\Petedata.mdb"
End Select

'CHECK FOR EMPTY STAFF NAME, START OR STOP DATES:
If IsNull(Me!StaffSelect) Then
MsgBox "PLEASE ENTER YOUR NAME."
Exit Sub
End If

If IsNull(Me!Start) Then
MsgBox "PLEASE ENTER THE START DATE FOR THE CURRENT BILLING PERIOD."
Exit Sub
End If

If IsNull(Me!Stop) Then
MsgBox "PLEASE ENTER THE STOP DATE FOR THE CURRENT BILLING PERIOD."
Exit Sub
End If

' *****************************************
'EXIT IF THE NETWORK DATABASE IS IN USE
If Len(Dir(SourceInUse)) > 0 Then
MsgBox "The network database is in use. Try again later."
Exit Sub
End If
' *****************************************

'... assuming everything's OK ...

Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
DoCmd.RunMacro "OpenfrmPleaseWait"
DoCmd.Echo False, ""
DoCmd.SetWarnings False
DoCmd.Close acForm, "frmHidden", acSaveNo

'BACKUP NEW RECORDS TO FE TEMP TABLES
On Error Resume Next
'Formerly: DoCmd.RunMacro "UpdateDatabase.Step1_HomeComp"
DoCmd.OpenQuery "QEmpty_TempDischargeSummaries", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempAssessment1", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempAssessment2", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempBMPNew", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempBMP_TBs", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempTherapyPlans", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempMonthlySummaries", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempServicesProvided", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempTxPlans", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempTxPlans_Problems", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempAxisI", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempAxisII", acNormal, acEdit
DoCmd.OpenQuery "QEmpty_TempPsychEval", acNormal, acEdit
DoCmd.OpenQuery "QFillTempDischargeSummaries", acNormal, acEdit
DoCmd.OpenQuery "QFillTempAssessment1", acNormal, acEdit
DoCmd.OpenQuery "QFillTempAssessment2", acNormal, acEdit
DoCmd.OpenQuery "QFillTempBMPNew", acNormal, acEdit
DoCmd.OpenQuery "QFillTempBMP_TBs", acNormal, acEdit
DoCmd.OpenQuery "QFillTempTherapyPlans", acNormal, acEdit
DoCmd.OpenQuery "QFillTempMonthlySummaries", acNormal, acEdit
DoCmd.OpenQuery "QFillTempServicesProvided", acNormal, acEdit
DoCmd.OpenQuery "QFillTempTxPlans", acNormal, acEdit
DoCmd.OpenQuery "QFillTempTxPlans_Problems", acNormal, acEdit
DoCmd.OpenQuery "QFillTempPsychEval", acNormal, acEdit
DoCmd.OpenQuery "QFillTempAxisI", acNormal, acEdit
DoCmd.OpenQuery "QFillTempAxisII", acNormal, acEdit

' *****************************************
'COPY THE NETWORK DATABASE TO THE LOCAL C-DRIVE
On Error GoTo CopyNetworkDatabase_err
FileCopy SourceFile, UnzippedFile 'UnzippedFile = e.g., C:\fpsdata.mdb
' *****************************************
'CREATE THE TEMPFILE
On Error GoTo CreateTempFile_err
FileCopy UnzippedFile, TempFile 'TempFile = "C:\tempdata.mdb"
' *****************************************
'LINK TEMPORARILY TO "C:\tempdata.mdb"
On Error GoTo Relink_err
'Loop through the tables collection
For Each tdf In Tdfs
'...link main tables
If Len(tdf.Connect) > 0 Then
Select Case Left(tdf.SourceTableName, 4)
Case Is = "DapN"
'tdf.Connect = ";DATABASE=" & NewDapNotesPathname
'tdf.RefreshLink
Case Else
tdf.Connect = ";DATABASE=" & TempFile 'i.e.,
"C:\tempdata.mdb"
tdf.RefreshLink
End Select
End If
Next 'Goto next table
' *****************************************

'REPLACE ORIGINAL BE WITH NEW BE (UNZIPPEDFILE)
On Error Resume Next
FileCopy UnzippedFile, OriginalPath 'e.g., C:\fpsdata.mdb to
C:\Access97\fpsdata.mdb
' *****************************************

'RE-LINK TABLES
On Error GoTo Relink_err
'Loop through the tables collection
For Each tdf In Tdfs
'...link main tables
If Len(tdf.Connect) > 0 Then
Select Case Left(tdf.SourceTableName, 4)
Case Is = "DapN"
'tdf.Connect = ";DATABASE=" & NewDapNotesPathname
'tdf.RefreshLink
Case Else
tdf.Connect = ";DATABASE=" & OriginalPath
tdf.RefreshLink
End Select
End If
Next 'Goto next table
' *****************************************

'REPLACE NEW RECORDS
On Error Resume Next
'Formerly: DoCmd.RunMacro "UpdateDatabase.Step3_HomeComp"
DoCmd.OpenQuery "QUpdateMatching_DischargeSummaries", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_MonthlySummaries", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_Assessments1", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_Assessments2", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_BMPNew", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_BMP_TBs", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_TherapyPlans", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_TxPlans", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_TxPlans_Problems", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_PsychEval", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_AxisI", acNormal, acEdit
DoCmd.OpenQuery "QUpdateMatching_AxisII", acNormal, acEdit
DoCmd.OpenQuery "QAppend_DischargeSummaries", acNormal, acEdit
DoCmd.OpenQuery "QAppend_TherapyPlans", acNormal, acEdit
DoCmd.OpenQuery "QAppend_Assessments1", acNormal, acEdit
DoCmd.OpenQuery "QAppend_Assessments2", acNormal, acEdit
DoCmd.OpenQuery "QAppend_BMPNew", acNormal, acEdit
DoCmd.OpenQuery "QAppend_BMP_TBs", acNormal, acEdit
DoCmd.OpenQuery "QAppend_MonthlySummaries", acNormal, acEdit
DoCmd.OpenQuery "QAppend_ServicesProvided", acNormal, acEdit
DoCmd.OpenQuery "QAppend_TxPlans", acNormal, acEdit
DoCmd.OpenQuery "QAppend_TxPlans_Problems", acNormal, acEdit
DoCmd.OpenQuery "QAppend_PsychEval", acNormal, acEdit
DoCmd.OpenQuery "QAppend_AxisI", acNormal, acEdit
DoCmd.OpenQuery "QAppend_AxisII", acNormal, acEdit

GoTo ProcessComplete

' *****************************************
End Select 'User's selection on Frame74
' *****************************************
ProcessComplete:
Set dbs = Nothing
Set Tdfs = Nothing
Set tdf = Nothing
DoCmd.OpenForm "frmHidden", , , , , acHidden
DoCmd.Close acForm, "PleaseWait", acSaveNo
DoCmd.SetWarnings True
DoCmd.Echo True
MsgBox "Process Complete"
Exit Sub

CopyNetworkDatabase_err:
Set dbs = Nothing
Set Tdfs = Nothing
Set tdf = Nothing
DoCmd.OpenForm "frmHidden", , , , , acHidden
DoCmd.Close acForm, "PleaseWait", acSaveNo
DoCmd.SetWarnings True
DoCmd.Echo True
MsgBox "COPY THE NETWORK DATABASE TO THE LOCAL C-DRIVE -- " &
Err.Description
Exit Sub

CreateTempFile_err:
Set dbs = Nothing
Set Tdfs = Nothing
Set tdf = Nothing
DoCmd.OpenForm "frmHidden", , , , , acHidden
DoCmd.Close acForm, "PleaseWait", acSaveNo
DoCmd.SetWarnings True
DoCmd.Echo True
MsgBox "CreateTempFile_err: " & Err.Description
Exit Sub

Relink_err:
Set dbs = Nothing
Set Tdfs = Nothing
Set tdf = Nothing
DoCmd.OpenForm "frmHidden", , , , , acHidden
DoCmd.Close acForm, "PleaseWait", acSaveNo
DoCmd.SetWarnings True
DoCmd.Echo True
MsgBox "Relink_err: " & Err.Description
Exit Sub

End Sub
 
Networks and file servers are optimised to keep open files
open, and delay closing. You could reconfigure your network
and file server to not cache files or keep files open, but that
would be even more difficult than waiting, and using new file
names if necessary instead of deleting the old file. Your automatic
system can automatically delete extra files next time instead
of automatically deleing them now.

(david)
 
Thanks for the response, David, but I don't believe that's what's happening
in this case. My post was probably not very clear, but it's the laptop's
backend that is still open, not the one on the server. The laptop starts out
with it's frontend linked to its own .mdb file. The laptop never links to,
or opens anything across the network. It does copy an .mdb file from the
network (first making sure that it's not open) to it's own C-drive (root
directory). Once there, it makes a second copy (with a different name), also
in the root directory. It links to this second copy. It then replaces the
laptop's original .mdb file with the first copy. It then links back to this
local (and now updated) .mdb file.

The sequence is:

' Laptop FE starts out linked to laptop BE ("OriginalPath")
'1. run queries to fill FE TempTables -- on laptop only
'2. FileCopy SourceFile, UnzippedFile -- network to laptop
'3. FileCopy UnzippedFile, TempFile -- on laptop only
'4. Link to TempFile -- on laptop only
'5. FileCopy UnzippedFile, OriginalPath -- on laptop only
'6. Link to OriginalPath -- on laptop only
'7. run queries to restore new records -- on laptop only

It looks like, without some kind of pause, step 4 does not completely
finish -- linking to TempFile and allowing the original BE to close --
before step 5 attempts to replace this original BE. An error-handler right
before step 5 results in the procedure going to that error-handler and
stopping the procedure. Resume Next, however, somehow allows step 4 to be
completed -- including the closing of the original BE -- and the original BE
is successfully replaced with "UnzippedFile".
 
Interesting. I guess the file system is reporting an error, but
succeeding anyway. Like an exception is generated, then a
couple of re-tries, then it works... I still think that the failure
is gonna be that the file system has not completed all it's background
tasks, not that the Jet Cache has not completed all it's background
tasks, but who can say? Throw in a couple of dbEngine.idle and
flushOS and DoEvents to help it along. It seems to me that if you
depend on File Copy succeeding with Error handling turned off you
may end up linked to the wrong file, so I guess you need to check
the error object if you go that way.

(david)
 
FYI ...
Found out the problem was that the code was being run from a form which
contained a bound text field to supply info to the code. This caused the
local BE to remain open, in spite of the step in the code of re-linking the
FE to another location. My solution was to just split the code onto two
separate forms. When the second form opens, it retrieves the info from the
first form, which allows the first form to then be closed, thus allowing the
local BE to close. After that, everything runs fine -- FileCopy can
successfully replace the original local BE.
 
Back
Top