Open another database using a button

[Islwyn]

I'm trying to open another database and close the original by clicking on a button on a form (the second database will then backup the first). I'm very much an MVB novice and am blundering around a bit, but I've almost got it working using a Call Shell command. Only the path I am using for the .mdb file isn't accepted in full:
I'm using:

Call Shell("C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE D:\Users\Joe Brown\Documents\Database Stuff\Backup.mdb", 1)

But the program seems to try and open a file called "Joe.mdb", implying that it doesn't like the spaces in the address - yet it's quite happy with the address for the MSACCESS.EXE file. It works fine if I place "Backup.mdb" in the "Users" folder and change the address accordingly.

Is it looking for a DOS address, and if so how do I find this? Or does anyone have any better suggestions as to how I do this?

Many thanks

 

[boblarson]

How about this instead (no need to know what the Access installation path is):

http://www.btabdevelopment.com/ts/default.aspx?PageId=46

 

[Islwyn]

Thanks for that reply - from the following question you will probably see that I really am blundering around!

How do I use the above instead of the relevant lines in the below code - for example where/how do I define the path to the other database and then run the Function:

Private Sub Command11_Click()
If MsgBox("DO YOU WANT TO BACK UP THE DATABASE?", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "BackupDate1"
DoCmd.OpenQuery "BackupDate2"
Call Shell("C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE D:\Users\Joe Brown\Documents\Database Stuff\Backup.mdb", 1) ' opens new database
DoCmd.Quit acSave 'close current DB
End If
End Sub

Thanks

 

[Islwyn]

OK, I think I have it working following a bit of guesswork and remembering how this type of code works.
I pasted the code you referenced into the editor, and then wrote:

Private Sub Command11_Click()
If MsgBox("DO YOU WANT TO BACK UP THE DATABASE?", vbYesNo) = vbYes Then
OpenNewDatabase ("D:\Users\Joe Brown\Documents\Database Stuff\Backup.mdb"

I assume that's the best way to do it?

Thanks for your help Bob Larson

 

[Islwyn]

Here is the code in full cut from the relevant 'Option Compare Database' for anyone who is a novice like me.

I've added the "appAcc.RunCommand acCmdAppMaximize" to make the new Access window maximise:

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 OPEN THE DATABASE CALLED DATABASE2.mdb AND CLOSE THIS ONE?", vbYesNo) = vbYes Then
        OpenNewDatabase ("D:\Users\....[input relevant path to file]...DATABASE2.mdb")
        DoCmd.Quit acSave    'close current DB
    End If
End Sub

 

[Islwyn]

This code worked beautifully but has suddenly stopped working, with the second database now refusing to copy the first because the fist has not closed properly - there is still a ".ldb" file open for the first. It does not seem to reach the DoCMd.Quit line properly. I can't see why it has suddenly started doing this. Can anyone help? Is it possible to get the second database to force the first to close perhaps?

Thanks

 

[boblarson]

What do you mean by "...copy the first...?" Why are you copying the first?

 

[Islwyn]

The only purpose of the second database is to make a backup copy of the first.

The switchboard on the main database has a box on it which says "You last backed up the database on [BackupDate]" to encourage the user to make regular backups in a straightforward way (rather than having to open a folder and select copy, paste etc. - as I'm sure you appreciate, a relative novice with computers is not likely to be able to do this easily, or if they can remember how to do it, may not do so regularly). [BackupDate] is updataed whenever they select the "Backup now" button.

The "Backup now" button should then open the new database, then close the first. The sole purpose of the second database is to run code to copy the first database.

The second database contains the following code.

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

If there is more straightforward way of getting a database to backup itself from such a button then I'd be grateful for information as to how to do this.

Thanks

Is

 

[boblarson]

How about using the similar method I use in my Frontend Auto Updating Tool.

Here's the code which builds a batch file and then executes it so that the database can close and be deleted. So you would modify the code so that it just closes, does the copy and then perhaps reopens the original database.

Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String
' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile
'closes the current version and runs the batch file
DoCmd.Quit
 
End Sub

 

[smig]

why not simply use FileCopy to backup the db ?
you can copy an open db

 

[boblarson]

you can copy an open db

There is a risk of doing that though. The risk of corruption rises when doing so. But the risk may not be high enough to warrant the need for another method.

 

[Islwyn]

Thanks for these comments.

The code given by me above does use FileCopy, albeit from a second database, but it will not work while the original database is still open and I cannot get the original to shut - it gives a permission denied error message (but will, of course copy an unopen database fine).

This was the whole point of trying to use file copy from a second database after having closed the first - but for some reason the first database has stopped shutting, thereby not allowing FileCopy to take place.

I will try the shell method, having read up on how these work (haven't used this type of command for 15 years or so, and then it was just for very basic UNIX stuff)- and with caution - I assume these can be pretty dangerous if you're not careful.

One quick question: if copying a database while open can cause problems (not surprising), how does the below code manage to close the database before running the "TestFile" shell script when the DoCmd.Quit line follows the Shell TestFile line?

' runs the batch file
Shell TestFile
'closes the current version and runs the batch file
DoCmd.Quit

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\FolderWhereOtherBackupsAreKept\"
' 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

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