VBA Code to Copy front END DB using Command button

[DJRhino1175]

I'm looking to see if there is code in VBA to copy lets say an Admin database paste it to another folder and have it rename/Overwrite the current file in this folder. I already have a way to kick everyone off the database so I will not need to consider this part. Right now I do this manually and it can be very time consuming.

I have looked at other solutions and they seem to complicated for my abilities.

Thanks for any help you can give me.

Rhino

[twinnyfo]

DJRhino1175,

Using a File System Object, you can move, copy, rename files very extensively. You will need the Windows Scripting Runtime reference added. Simply put:

Private Sub cmdCopy_Click()
    Dim FSO as FileSystemObject
    Set FSO = New FileSystemObject
    FSO.CopyFile FromPathFileName, ToPathFileName
    Set FSO = Nothing
End Sub

Very simple--just gotta figure out the from and to!

Hope that hepps!

[NeoPa]

Hi Rhino.

While it's perfectly possible to open up a FileSystemObjec t and use CopyFile, it's also possible, and probably more straightforward , to use FileCopy that comes with the basic VBA library.

Scripting is great and very powerful, but it's not always necessary.

[twinnyfo]

NeoPa,

Please add to my tool kit.

I remember many a year ago when I was searching for how to copy stuff in VBA, and all info pointed to FSOs.

SO, yer tellin' me that all this time I have been wasting my efforts with this???

I feel so violated! I guess that's what I get for not knowing everything in the standard VBA library!

DUH!

Thanks, buddy! Now I can make my stuff more easier to run!

[twinnyfo]

So, to do it the RIGHT WAY....

Private Sub cmdCopy_Click()
    Call FileCopy(FromPathFileName, ToPathFileName)
End Sub

[DJRhino1175]

Line 2 in the code, I would repeat this for each one I would need to replace?

[DJRhino1175]

Here is what I have so far, but the Call FileCopy part is red. When I go and do a Compile I get an Error: Compile Error: Syntax error.

Private Sub DatabaseUpdate_Click()

    Call FileCopy G:\Unsecure-Share\ASM OEE Files\Data Tables\Assembly OEE Database-Admin.accdb, G:\Unsecure-Share\ASM OEE Files\Assembly OEE Database-Brian Pritchett.accdb
    
End Sub

[twinnyfo]

First you need to maintain the same syntax that the code has:

Call FileCopy(FromPathFileName, ToPathFileName)

Notice the Parentheses.

Second, whenever you have text strings, you must enclose them in Quotation marks.

A better way, to clean up your code, is to declare variables, then assign the variables values, and then use the variables in your expression:

Private Sub cmdCopy_Click()
    Dim strPath     As String
    Dim strFromFile As String
    Dim strToFile   as String

    strPath = "G:\Unsecure-Share\ASM OEE Files\"
    strFromFile = strPath & _
        "Data Tables\Assembly OEE Database-Admin.accdb"
    strToFile = strPath & _
        "Assembly OEE Database-Brian Pritchett.accdb"

    Call FileCopy(strFromFile, strToFile)
End Sub

Do you see how I've cleaned it up and made things easier to read, also?

Hope this hepps!

[DJRhino1175]

Ok, this works, I needed to copy the DB I needed to copy because I got an Access denied error. Now is there a way to loop this so I can do it to multiple users? I currently have 5 unique users, but they are going to add 8 more next week.

Thank you so much for helping me twinnyfo it is greatly appreciated.

[PhilOfWalton]

Set up your ToFiles in a table, and just loop through the table.

Phil

[NeoPa]

TwinnyFo:
I feel so violated! I guess that's what I get for not knowing everything in the standard VBA library!

For more such methods look up VBA.FileSystem in the Object Explorer (Shift-F2).

I've also found it difficult over time to find out about some of these methods - for exactly that same reason. All the info assumes you need an FSO and scripting. Clearly that's wrong - but that's how the web works until someone comes along to show the simpler way.

@Rhino.
Feel free to proceed on this course but remember you get best result by keeping your threads to simple questions and opening new ones when you need to move forward.

[zmbd]

DJRhino1175
(...) DB I needed to copy because I got an Access denied error. (...)

I suspect that you are attempting to copy the database while it is open/loaded, of course, this will not work.

In years past, I had a batch file (yes the old MSDOS days) that I had bodged together that the users would run via a shortcut on the desktop. The batch file would copy the front-end to the local drive every time. Worked like a charm for a very long time.

Looking around one day I ran across a variant of this which runs from within Access - YEA! It's a lot more flexible and works without much effort on my end

So what I have now is a table with versioning numbers in both the front and backend
In the front end:
[t_FeMetadata]
[pk_v][BE_V][FE_V]
In the back end
[t_BeMetadata]
[pk_v][BE_V][FE_V][FE_MstrLctn]

I have the following code in a Macro - [AutoExec]
(I've removed some of the form calling script - it has sensitive information - should be very easy to add in at the user's end.)
This macro checks to see if the database is in a trusted state and if so it launches the splash/re-linking form:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
   <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
      <UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
         <Statements/>
         <Sub Name="autostart">
           <Statements>
              <ConditionalBlock>
                  <If>
                     <Condition>[currentproject].[istrusted]</Condition>
                     <Statements>
                        <Action Name="MessageBox">
                           <Argument Name="Message">You would place your action code to open the re-linking form here so that the form's onload code will execute</Argument>
                           <Argument Name="Type">Information</Argument>
                           <Argument Name="Title">VBA Code Should Run, DB in Trusted Status.</Argument>
                        </Action>
                     </Statements>
                  </If>
                  <Else>
                     <Statements>
                        <Action Name="MessageBox">
                           <Argument Name="Message">The database is currently not in a trusted state.@Many of the features of the database are currently disabled.@Please allow content and add to your trusted documents list. You may need to close and reopen the file after adding to the trusted documents.</Argument>
                           <Argument Name="Type">Critical</Argument>
                           <Argument Name="Title">Database Is Partialy Disabled. Please Enable and Add to Trusted Documents.</Argument>
                        </Action>
                     </Statements>
                  </Else>
               </ConditionalBlock>
            </Statements>
         </Sub>
      </UserInterfaceMacro>
   </UserInterfaceMacros>

Once the relinking/splash form loads the on_load event triggers:
+ checks to make sure that the currently opened front-end isn't the Master Copy. If it is then it prompts the user for location on their PC and verifies that they can read/write to that document (this is then passed to the following vba module instead of the current location to copy the master to and the remaining checks are skipped).
+ checks to see if the backend is in the same location and if so then it links the [t_BeMetadata] first, checks the versioning numbers to make sure that the front and back ends are compatible, if front end version number is different in the backend then it passes the current path for the database that is loaded the location of the master-file
((side note: during the final beta stages I'll often just skip the version checks and just have the current Master FE file copied over the user's version regardless of the situation))
+ starts the following code:

(I don't remember where I found the concept now; however, I've seen versions of this in a lot of VBA forums. If the author is reading this - THANK YOU for your help!)

Option Compare Database
Option Explicit
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String


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

This basically re-creates the same batch file I used to do by hand, shells the command outside of Access, closes the current Access session, copies the new Access over, and restarts a new Access session.

BEAUTY OF THIS METHOD:
It runs in every version of the front-end file.
You do NOT need to loop through tables of user names, make sure your profile as read/write permission to the user's directory etc...

DRAWBACK: User MUST have at least Read permission to the Master Front-End location. My normal arrangement
<MainFolder>Fro nt_end.Accdb
<BeDataFolder>B ack_End.ACCDB</BeDataFolder>
</MainFolder>