473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VBA Code to Copy front END DB using Command button

DJRhino1175
221 128KB
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
Jul 24 '18 #1

✓ answered by twinnyfo

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

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.     Dim strPath     As String
  3.     Dim strFromFile As String
  4.     Dim strToFile   as String
  5.  
  6.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  7.     strFromFile = strPath & _
  8.         "Data Tables\Assembly OEE Database-Admin.accdb"
  9.     strToFile = strPath & _
  10.         "Assembly OEE Database-Brian Pritchett.accdb"
  11.  
  12.     Call FileCopy(strFromFile, strToFile)
  13. End Sub
Do you see how I've cleaned it up and made things easier to read, also?

Hope this hepps!

11 2348
twinnyfo
3,653 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.     Dim FSO as FileSystemObject
  3.     Set FSO = New FileSystemObject
  4.     FSO.CopyFile FromPathFileName, ToPathFileName
  5.     Set FSO = Nothing
  6. End Sub
Very simple--just gotta figure out the from and to!

Hope that hepps!
Jul 24 '18 #2
NeoPa
32,556 Expert Mod 16PB
Hi Rhino.

While it's perfectly possible to open up a FileSystemObject 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.
Jul 24 '18 #3
twinnyfo
3,653 Expert Mod 2GB
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!
Jul 24 '18 #4
twinnyfo
3,653 Expert Mod 2GB
So, to do it the RIGHT WAY....

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.     Call FileCopy(FromPathFileName, ToPathFileName)
  3. End Sub
Jul 24 '18 #5
DJRhino1175
221 128KB
Line 2 in the code, I would repeat this for each one I would need to replace?
Jul 24 '18 #6
DJRhino1175
221 128KB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DatabaseUpdate_Click()
  2.  
  3.     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
  4.  
  5. End Sub
  6.  
Jul 24 '18 #7
twinnyfo
3,653 Expert Mod 2GB
First you need to maintain the same syntax that the code has:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.     Dim strPath     As String
  3.     Dim strFromFile As String
  4.     Dim strToFile   as String
  5.  
  6.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  7.     strFromFile = strPath & _
  8.         "Data Tables\Assembly OEE Database-Admin.accdb"
  9.     strToFile = strPath & _
  10.         "Assembly OEE Database-Brian Pritchett.accdb"
  11.  
  12.     Call FileCopy(strFromFile, strToFile)
  13. End Sub
Do you see how I've cleaned it up and made things easier to read, also?

Hope this hepps!
Jul 24 '18 #8
DJRhino1175
221 128KB
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.
Jul 24 '18 #9
PhilOfWalton
1,430 Expert 1GB
Set up your ToFiles in a table, and just loop through the table.

Phil
Jul 24 '18 #10
NeoPa
32,556 Expert Mod 16PB
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.
Jul 25 '18 #11
zmbd
5,501 Expert Mod 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2.    <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
  3.       <UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
  4.          <Statements/>
  5.          <Sub Name="autostart">
  6.            <Statements>
  7.               <ConditionalBlock>
  8.                   <If>
  9.                      <Condition>[currentproject].[istrusted]</Condition>
  10.                      <Statements>
  11.                         <Action Name="MessageBox">
  12.                            <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>
  13.                            <Argument Name="Type">Information</Argument>
  14.                            <Argument Name="Title">VBA Code Should Run, DB in Trusted Status.</Argument>
  15.                         </Action>
  16.                      </Statements>
  17.                   </If>
  18.                   <Else>
  19.                      <Statements>
  20.                         <Action Name="MessageBox">
  21.                            <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>
  22.                            <Argument Name="Type">Critical</Argument>
  23.                            <Argument Name="Title">Database Is Partialy Disabled. Please Enable and Add to Trusted Documents.</Argument>
  24.                         </Action>
  25.                      </Statements>
  26.                   </Else>
  27.                </ConditionalBlock>
  28.             </Statements>
  29.          </Sub>
  30.       </UserInterfaceMacro>
  31.    </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!)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. ' global variable for path to original database location
  4. Public g_strFilePath As String
  5. ' global variable for path to database to copy from
  6. Public g_strCopyLocation As String
  7.  
  8.  
  9. Public Sub UpdateFrontEnd()
  10. Dim strCmdBatch As String
  11. Dim notNotebook As Object
  12. Dim FSys As Object
  13. Dim TestFile As String
  14. Dim strKillFile As String
  15. Dim strReplFile As String
  16. Dim strRestart As String
  17.  
  18. ' sets the file name and location for the file to delete
  19. strKillFile = g_strFilePath
  20. ' sets the file name and location for the file to copy
  21. strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
  22. ' sets the file name of the batch file to create
  23. TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
  24. ' sets the restart file name
  25. strRestart = """" & strKillFile & """"
  26. ' creates the batch file
  27. Open TestFile For Output As #1
  28. Print #1, "Echo Off"
  29. Print #1, "ECHO Deleting old file"
  30. Print #1, ""
  31. Print #1, "ping 1.1.1.1 -n 1 -w 2000"
  32. Print #1, ""
  33. Print #1, "Del """ & strKillFile & """"
  34. Print #1, ""
  35. Print #1, "ECHO Copying new file"
  36. Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
  37. Print #1, ""
  38. Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
  39. Print #1, "START /I " & """MSAccess.exe"" " & strRestart
  40. Close #1
  41. 'Exit Sub
  42. ' runs the batch file
  43. Shell TestFile
  44.  
  45. 'closes the current version and runs the batch file
  46. DoCmd.Quit
  47.  
  48. End Sub
  49.  
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>Front_end.Accdb
<BeDataFolder>Back_End.ACCDB</BeDataFolder>
</MainFolder>
Jul 25 '18 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: rdade22 | last post by:
Hi, I'm new so try to bare with me. I'm using access 2000 and I created a database where the user is prompted to click on a command button and the info that was put on the form is sent to a table....
5
by: dgrabuloff | last post by:
i am inputting records using a form. how do i put a command button on the form that will copy the record i just input----sometimes i have the same record that needs to be put in 3 or 4 times and i...
4
by: sirimanna | last post by:
hi, Is any one can help me to open files in my computer(for an example: some word document)using command button... i want to open file's using my vb programme..but i can't do it...can any one...
2
by: tejavenkat | last post by:
Hi, I have one scenario i.e Download as csv is there it is a command button when we click on this the save dialog box should be open,Am using javascript how can we do this by using by javascrpt...
0
by: mesadobes | last post by:
I am very new to visual basic. Actually, I have no clue what I'm doing! But I know what I want to do. I am trying to learn as I go... How do I create a command button that when clicked will...
4
beacon
by: beacon | last post by:
Hi everybody, I have a main form, frmDeficiency, that has a tab control, deficiencyTabControl, that has a subform, fsubEpisodeDetail, on page 2 of the tab control. I also have a command button...
1
by: Palaniappan | last post by:
how to add an item into the textbox by using command button in ms access in the form from the table?
2
by: aanand1 | last post by:
Hi, I am unable to delete a selected record on listbox using command button. I am new to Access and have made VB code using internet help. Here is my code. Private Sub...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.