Backing up an Access Database

[Dmonkyking]

Hi,
I have an Access database that I have that when you exit it it'll ask you if you want to back up the database. That all works great using the following command (all on one line):

DoCmd.CopyObject "E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb", "", acTable, "MAIN INVENTORY TABLE"

As you see, if I wanted to move this database to somewhere else or the backup file is missing it's not goona backup the database. Does anyone know any easier way to do this. For example, maybe making the main database create the backup database in the dir that the main database is in. If the backup database doesn't exist, it'll create it instead of saying it doesn't exist. Hope that makes sense. If anyone can help me out that would be great.

 

[zevw]

Try doing this

Dim WsSearchDb As Workspace
Dim dbs As Database

If Dir("E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb" ) = "" Then
Set dbs = WsSearchDb.CreateDatabase("E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb", dbLangGeneral)
End If

Then write your command

DoCmd.CopyObject "E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb", "", acTable, "MAIN INVENTORY TABLE"

The caommand above creates a database if it does not exist

 

[willir]

Dmonkyking

Consider using a table with administrative information in it. I found this approach useful for tracking other things besides the file location.

AdminTbl
TaskPK - text field, primary field
AdminTask - text field with information.

Example:
TaskPK = "BACKUP"
AdminTask = "E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb"

The records need to be maintained, but this is easier than editing the code.

I like zevw suggestion.

Richard

 

[Dmonkyking]

Thanks alot zevw and willir. I'm goona give both of those suggestions a shot.

-Ben

 

[Dmonkyking]

Thanks zevw, I tried your suggestion and it did work when the database was there. I tried to rename the database to see if it would create it again and when I run the code it gives me this error box:

Object variable or With block variable not set

Did I miss something? Not sure what that means.

This is the code I put in so far for the command:

Option Compare Database

Function Overdue_Cal_Check()
On Error GoTo Overdue_Cal_Check_Err

Dim WsSearchDb As Workspace
Dim dbs As Database

If Dir("E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb") = "" Then
Set dbs = WsSearchDb.CreateDatabase("E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb", dbLangGeneral)
End If

DoCmd.CopyObject "E:\F-16ais\Additional Duties\PMEL\Tracking Database\Backup.mdb", "", acTable, "MAIN INVENTORY TABLE"

Overdue_Cal_Check_Exit:
Exit Function

Overdue_Cal_Check_Err:
MsgBox Error$
Resume Overdue_Cal_Check_Exit

End Function

 

[PHV]

Try replacing this:
Dim WsSearchDb As Workspace
By this:
Dim WsSearchDb As New Workspace
Or this:
Set dbs = WsSearchDb.CreateDatabase("...
By this:
Set dbs = CreateDatabase("...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dmonkyking]

Thanks alot PH. It works, but the only thing is if the database doesn't exist it gives an error that the database is already in use, but it still creates the backup dataebase. So you'd just have to go back into the main database and pick to back it up again, which is fine, unless you might know why it might be doing that. But thanks for the create tip.

-Ben