rdolivaw
asked on
Backup to and restore .mdb file
I'm trying to set up a couple buttons for the user to backup and restore an Access database file. The backup part works ok, but when I restore I have difficulty. The code so far is as follows:
Private Sub cmdGo_Click()
Dim fso
On Error GoTo errDiskError
envMedical.cnnExams.Close 'Close connection
Set fso = CreateObject("Scripting.Fi leSystemOb ject")
Screen.MousePointer = vbHourglass
Do While Not fso.FileExists("A:\MedAcce ss.mdb")
If MsgBox("Wrong diskette; please fix and try again", _
vbOKCancel + vbExclamation, _
"Wrong disk") = vbCancel Then
Unload Me
Exit Sub
End If
Loop
DoEvents
Kill "C:\MedAccess\MedAccess.md b"
FileCopy "A:\MedAccess.mdb", "C:\MedAccess\MedAccess.md b"
envMedical.cnnExams.Open 'Open connection
envMedical.rsdcdExams.Open 'Open table
envMedical.rsdcdPSErr.Open 'Open table
Screen.MousePointer = vbNormal
MsgBox "Restore successful", vbOKOnly, "Restore complete"
Exit Sub
errDiskError:
MsgBox "Error " & Err.Number & vbCr & Err.Description, _
vbOKOnly + vbExclamation, "Disk Error"
Resume Next
End Sub
The actual restore from the stiffy seems to go OK, based upon file modification times, but when opening the tables I get "Error number 3709 The application requested an operation on an object with a reference to a closed or invalid Connection object." from my error handler.
What am I missing?
Private Sub cmdGo_Click()
Dim fso
On Error GoTo errDiskError
envMedical.cnnExams.Close 'Close connection
Set fso = CreateObject("Scripting.Fi
Screen.MousePointer = vbHourglass
Do While Not fso.FileExists("A:\MedAcce
If MsgBox("Wrong diskette; please fix and try again", _
vbOKCancel + vbExclamation, _
"Wrong disk") = vbCancel Then
Unload Me
Exit Sub
End If
Loop
DoEvents
Kill "C:\MedAccess\MedAccess.md
FileCopy "A:\MedAccess.mdb", "C:\MedAccess\MedAccess.md
envMedical.cnnExams.Open 'Open connection
envMedical.rsdcdExams.Open
envMedical.rsdcdPSErr.Open
Screen.MousePointer = vbNormal
MsgBox "Restore successful", vbOKOnly, "Restore complete"
Exit Sub
errDiskError:
MsgBox "Error " & Err.Number & vbCr & Err.Description, _
vbOKOnly + vbExclamation, "Disk Error"
Resume Next
End Sub
The actual restore from the stiffy seems to go OK, based upon file modification times, but when opening the tables I get "Error number 3709 The application requested an operation on an object with a reference to a closed or invalid Connection object." from my error handler.
What am I missing?
Your connection is destroyed when the original database is deleted. You must re-rstablish your connection and re-initialize your object variables with the Set command.
Argonaut:
Please try the comment button.. <wink>.
-------------------------- ---------- -
You implement this statement early in the Sub Routine.
envMedical.cnnExams.Close 'Close connection
Have you also closed any Open Recordsets? Do so if you haven't already.
-------------------------- ---------- --
What is with the Unload Me in your File Exists test? As you have active references to Me.. its not going to work.
Do While Not fso.FileExists("A:\MedAcce ss.mdb")
If MsgBox("Wrong diskette; please fix and try again", _
vbOKCancel + vbExclamation, _
"Wrong disk") = vbCancel Then
Unload Me
Exit Sub
End If
-------------------------- ---------- -
You should add an Existence test or an On Error before this statement..
Kill "C:\MedAccess\MedAccess.md b"
-------------------------- ---------- -
The proper syntax for this statement shoud be
fso.FileCopy "A:\MedAccess.mdb", "C:\MedAccess\MedAccess.md b"
-------------------------- ---------- -
Please try the comment button.. <wink>.
--------------------------
You implement this statement early in the Sub Routine.
envMedical.cnnExams.Close 'Close connection
Have you also closed any Open Recordsets? Do so if you haven't already.
--------------------------
What is with the Unload Me in your File Exists test? As you have active references to Me.. its not going to work.
Do While Not fso.FileExists("A:\MedAcce
If MsgBox("Wrong diskette; please fix and try again", _
vbOKCancel + vbExclamation, _
"Wrong disk") = vbCancel Then
Unload Me
Exit Sub
End If
--------------------------
You should add an Existence test or an On Error before this statement..
Kill "C:\MedAccess\MedAccess.md
--------------------------
The proper syntax for this statement shoud be
fso.FileCopy "A:\MedAccess.mdb", "C:\MedAccess\MedAccess.md
--------------------------
ASKER
I'm accessing the Access database through an ODBC connection. What's the correct way to reconnect?
Thanks for your hints, too, wsh2.
Thanks for your hints, too, wsh2.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still haven't really resolved the issue but it's a minor thing at this point as there's still a lot of other areas to complete on the app. Thanks for your advice; I've filed it and will readdress it in the future when I get back around to this. May be back in touch then.
HTH