Can't copy Access database

[Islwyn]

The below piece of code, which backs up the database which is currently open by opening another database, has suddenly stopped working in Access 2000 - it gives a "permission denied" error and the option to debug the code. The Visual Basic editor indicates that it is the line which copies the database which is causing the problem. The odd thing is that it was previously working fine.

It seems to have suddenly stopped closing the database to be copied after opening the new one (failing to reach the DoCmd.Quit acSave line), causing the second database to refuse to copy the first because the first is still open.

Any help would be much appreciated.

Function OpenNewDatabase(strDatabase As String)
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = True
appAcc.RunCommand acCmdAppMaximize
appAcc.OpenCurrentDatabase (strDatabase)
appAcc.UserControl = True
End Function

Private Sub Command11_Click()
If MsgBox("DO YOU WANT TO BACK UP THIS DATABASE?", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
OpenNewDatabase ("D:\[PATH]\Backup.mdb")
DoCmd.Quit acSave 'close current DB'
End If
End Sub

 

[vdanelia]

Is your Database Debugged and Compiled?

 

[Rx_]

Was wondering if a user needed to have exclusive use of the database to do this?

 

[Islwyn]

As far as I'm aware it's debugged - it worked fine before. I'm not sure how to compile it - can you clarify this?

When I click on the debug option it picks out the first "FileCopy" line in the below code, which is in the second database whose only purpose is to copy the first. The permission denied message makes me think that it's because the original database is not closing properly - you can still see the icon for it on the toolbar. Yet it seemed to be working fine before.

Private Sub Form_Open(Cancel As Integer)
Dim strFilter As String
Dim strSaveFileName As String
Dim tablefile As String
Dim todaysdate As String
DoCmd.Close

todaysdate = Date$
tablefile = "databasename" & todaysdate & ".mdb"
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mdb)", "*.mdb")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False, Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY, FileName:=tablefile, _
DialogTitle:="Backup")

FileCopy "D:\[PATH]\databasename.mdb", strSaveFileName
FileCopy "D:\[PATH]\databasename.mdb", "D:\[PATH2]\Database" & todaysdate & ".mdb"
MsgBox "THE BACKUP HAS BEEN MADE WITH THE NAME " & strSaveFileName
DoCmd.Quit
End Sub

 

[Islwyn]

Does anyone know how I might get the second database (the one that performs the "FileCopy" to make a backup of the first) to close and save the first. I'm wondering whether that might work?

Thanks

 

[Islwyn]

Thanks once again for your help Bob Larson.

The code below, is operated by a 'Backup this database' button, and seems to work fine. Hopefully others might find it useful - it seems odd that MS Access does not have a built in function to do this, given the number of people who use databases who are novices when it comes to copying files and in particular are terrible at remembering to back stuff up!

For the 'Save as' menu to work in the below code (the 'ahtCommonFileOpenSave' and 'ahtAddFilterItem' functions) you need code originally written by Ken Getz placed in a module. The code can be found at http://access.mvps.org/access/api/api0001.htm

--------------------------------------

Private Sub BackUp_Click()
Dim CopyFile As String
Dim FileName As String
Dim FileAddName As String
Dim CopyAddName As String
Dim CopyAddName2 As String
Dim todaysdate As String
Dim CopyNameDef As String
Dim FileAdd As String
Dim CopyAdd As String
Dim strFilter As String
If MsgBox("DO YOU WANT TO BACK UP THE DATABASE?", vbYesNo) = vbYes Then
' record date of backup in a table
DoCmd.SetWarnings False
DoCmd.OpenQuery "BackupDate1"
DoCmd.OpenQuery "BackupDate2"
' set name of database to be copied (without the '.mdb' bit):
FileName = "NameOfTheDatabase"
' set folder address for database
FileAdd = "D:\Users\MyName\FolderWhereDatabaseIsKept\"
' set address for second backup location which the user will not see
CopyAdd = "D:\Users\MyName\FolderWhereOtherBackupsAreKep t\"
' set the name and address of the file to be backed up:
FileAddName = FileAdd & FileName & ".mdb"
' include date in a default name for the new backup file
todaysdate = Date$
CopyNameDef = FileName & todaysdate & ".mdb"
'Ask for alternative name for backup file
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mdb)", "*.mdb")
CopyAddName = ahtCommonFileOpenSave(OpenFile:=False, Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY, FileName:=CopyNameDef, _
InitialDir:=FileAdd, DialogTitle:="Backup Database")
CopyAddName2 = CopyAdd & FileName & todaysdate & ".mdb"
' set the file name of the batch file to create:
CopyFile = FileAdd & "BackupDb.cmd"
' create the batch file:
Open CopyFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Making backup of database"
Print #1, "Copy /Y """ & FileAddName & """ """ & CopyAddName & """"
Print #1, ""
Print #1, "Copy /Y """ & FileAddName & """ """ & CopyAddName2 & """"
Print #1, ""
Print #1, "START /I MSAccess.exe """ & FileAddName & """"

Close #1
' run the batch file:
Shell CopyFile
'close the database:
DoCmd.Quit
End If
End Sub

---------------------------------------