VBA Code to Create Backup of Password Protected DB (1 Viewer)

davidb88

Registered User.
Local time
Today, 10:44
Joined
Sep 23, 2013
Messages
62
Hi -

I have a function in the backend of my database that when executed is supposed to create a backup in the location that I have specified on our network drive. The code was working well until I put a password on the back end database. Now when it runs, I receive a run-time error #70 "permission denied". I assume it is because I am not specifying the password anywhere? I am just not sure where it should go if that is the case? Or if maybe the password is not the issue and there is something else wrong with the code?

Any help is appreciated. Thanks!

Code:
Option Compare Database

Public Function db_backup()

Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String

sourceFile = CurrentProject.FullName
path = CurrentProject.path
name = CurrentProject.name
destinationFile = "\\Documents\Database_v1_BackUp.accdb"

If Dir(destinationFile) <> "" Then
Kill destinationFile
End If

If Dir(destinationFile) = "" Then

Set aFSO = CreateObject("scripting.filesystemobject")
aFSO.copyfile sourceFile, desinationfile, True


End If

End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 19, 2013
Messages
16,553
if you are just copying the file, a password should not be required since you are not opening the db.

You do have a typo in your code

aFSO.copyfile sourceFile, desinationfile, True

And using a reserved word

Dim path As String, name As String
 

davidb88

Registered User.
Local time
Today, 10:44
Joined
Sep 23, 2013
Messages
62
Hi CJ, thanks for your reply. I corrected those two errors and I am still getting a permission denied message. Is it possible that this error is occurring because I am trying to copy the same database that the code is running in? Meaning the code is running in the backend of the database and that is the file I am trying to copy? If so, do you know of a workaround for that?

Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 19, 2013
Messages
16,553
It shouldn't make a different although I would argue that if this is a backup then all users should be out of the db otherwise there is a risk of corruption.

Whilst the BE is open, can you copy and paste manually?

Also why are you trying to run this from the BE? The normal design is to only have tables in the BE, all code, forms,. queries etcin the front end?
 

davidb88

Registered User.
Local time
Today, 10:44
Joined
Sep 23, 2013
Messages
62
I am able to copy and paste manually while it is open, but the code still will not work for me.

The backend does only contain tables and the queries are stored in the front end. The reason the back up function is stored in the back end is because a lot of the data manipulation is occurring via an Excel toolbar so the only way I could think of to create a backup of the database each time something was changed via the Excel toolbar would be to have the VBA open the backend and upon open it executes the backup function. I know it would be better to have it stored not in the backend but I could not think of a logical method to pragmatically create a backup otherwise.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 19, 2013
Messages
16,553
something was changed via the Excel toolbar would be to have the VBA open the backend and upon open it executes the backup function
So where is the VBA referred to above that opens the back end?

Why can't this VBA just copy the db?
 

davidb88

Registered User.
Local time
Today, 10:44
Joined
Sep 23, 2013
Messages
62
The VBA that opens the backend is stored in the Excel ribbon code (data manipulation) and the front end (data entry). The code in each of those tools opens the backend to create the backup when a change is saved.

Having those two tools though copy the backend and paste to a new folder is a really good idea though. Would I be able to use the same code that I have now or would I need to change things to work in Excel VBA?

Thank you!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 19, 2013
Messages
16,553
the code should work as is so far as I can see - there is nothing specific to Access
 

Users who are viewing this thread

Top Bottom