Automatic Copy Database

[gstylianou]

Hi all,

There is a way using code to copy the back_end database? Please note that, i wanted to do that from Front_end Interface.

Thanks

 

[theDBguy]

Hi. I think so. Have a look here. Hope it helps...

 

[Gasman]

Another user has just done that and posted their method.

https://www.access-programmers.co.uk/forums/showthread.php?t=305144

 

[gstylianou]

Hi. I think so. Have a look here. Hope it helps...

Hi TheDBGuy and thanks but,

I modified the code as follow but i have problem because there is a password in my backend and i'm getting the error msg.

Option Compare Database
Option Explicit
Public Sub BackUpAndCompactBE()
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Source: http://accessmvp.com/theDBguy

On Error GoTo errHandler

Dim oFSO As Object
Dim strDestination As String
Dim strSource As String

'Get the source of your back end
strSource = Split(Split(CurrentDb.TableDefs("tblSettings").Connect, _
"Database=")(1), ";")(0)

'Determine your destination HERE I WANT A STANTART DESTINATION PATH AS :M:\Backup
strDestination = CurrentProject.Path & "\DATASET_MD216.0.0.1 (" _
& Format(Now, "yyyymmddhhnnss") & ").accde"

'Flush the cache of the current database
DBEngine.Idle

'Create a file scripting object that will backup the db
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile strSource, strDestination
Set oFSO = Nothing

'Compact the new file, ...
Name strDestination As strDestination & ".cpk"
DBEngine.CompactDatabase strDestination & ".cpk", strDestination
Kill strDestination & ".cpk"

'Notify users
MsgBox "Backup file '" & strDestination & "' has been created.", _
vbInformation, "Backup Completed!"

errExit:
Exit Sub

errHandler:
MsgBox err.Number & ": " & err.Description
Resume errExit

End Sub

 

[gstylianou]

Another solution that i found and works perfect but i cannot run it within vba module is a Script file for backup. Anyone can help me in order to run the Script file within access form (command)?

attached is the Script

 

[theDBguy]

Hi. Is that a typo? I see a space between the two n's in .Connect. Give me a sec and I'll try to test the code on a password-protected BE. If you debug the error, which line gets highlighted?

 

[theDBguy]

Hi. Is that a typo? I see a space between the two n's in .Connect. Give me a sec and I'll try to test the code on a password-protected BE. If you debug the error, which line gets highlighted?

Hi. This worked for me. Just replace "test" with your actual password.

    DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=test"

 

[gemma-the-husky]

can you just not copy it like this

filecopy databasename as copyname

 

[Gasman]

Oops, from what you posted it appears that user actually used the DBGuy's routine.
I never realised. Sorry the DBGuy

Another user has just done that and posted their method.

https://www.access-programmers.co.uk/forums/showthread.php?t=305144

 

[theDBguy]

Oops, from what you posted it appears that user actually used the DBGuy's routine.
I never realised. Sorry the DBGuy

Hi. No worries. It's all good.

 

[gstylianou]

Hi. This worked for me. Just replace "test" with your actual password.

    DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=test"

Yeap, working.
Tell me something, how can i run external Script file using code into my form?

 

[theDBguy]

Hi. This worked for me. Just replace "test" with your actual password.

    DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=test"

Upon further testing, it appears this method will apply the given password to the backup BE as well, even if the original BE was not password protected. I may have to update the website with this note later on. It may or may not be a good side effect, but definitely worth letting everyone know. Cheers!

 

[theDBguy]

Yeap, working.
Tell me something, how can i run external Script file using code into my form?

There are several ways to run a script file from VBA. For example, you can use Shell or ShellExecute.

 

[gstylianou]

There are several ways to run a script file from VBA. For example, you can use Shell or ShellExecute.

TheDBGuy can you please give an example on how using command button to call the Script (Vbs) file please? And sorry for my last post....

 

[theDBguy]

TheDBGuy can you please give an example on how using command button to call the Script (Vbs) file please? And sorry for my last post....

Hi. I can't test this right now but try something like:

Shell "c:\foldername\scriptname.vbs"