Solved

Automating Database copy and rename procedure

Posted on 2007-03-23
5
426 Views
Last Modified: 2013-11-27
I have created an application that is currently being used, but I still make updates to on a regular basis.  I am using the FMS Start Up application, so I have a table in the application that is called tblVersion.  All of the other tables are linked to a back end SQL Server database.  When I make an update, I have to change the value in tblVersion, copy the database to the network location for office users, copy the db to another network location for my citrix users and finally rename the citrix version.  I also create a back up copy on the network.  I am trying to automate this process by creating a separate database that will connect to the target database, update the version number, copy the database to its new locations, and rename the database for the citrix users.  I'd like to keep it in a separate mdb so I can use it as a log to keep track of the dates and reasons for the updates.
0
Comment
Question by:AaronGreene1906
  • 3
5 Comments
 
LVL 2

Expert Comment

by:CSBTech
ID: 18782716
Hi Aaron, I have a similar solution but I didn't it slightly differently.  I am not sure what exactly you are asking here, do you need help with the copying or the figuring out versions?  Anyway, I have a version table that I update withing the mdb when I make a change, then I put the new file on a server in a specific location.  When someone accesses their local mdb, it checks the version against the new version on the server by looking in that table.  If there version is less, it asks if they wish to upgrade.  If they say yes, it closes the mdb, and runs a small VB Executable that copies the new mdb to their location.  You can automate all this within another MDB, but should really be done in an EXE.  If you dont' have access (pardon the pun) to VB or another language, it can be done in Access but tell me which parts you are having problems with.  Or maybe you just need a general overview of how it should work?
0
 

Author Comment

by:AaronGreene1906
ID: 18784258
I guess I should have been more specific.  I'm getting stuck on the fact that the db that I'm using doesn't contain any of the objects that I'm going to be working with.  Here are the steps that I'm following.
1 Connect to the main database and update the version number
2 Create three mdb's in their network locations.
3 Create the links to the server database in each of the new db's
I'm having trouble figuring out how to create the links.  
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18792768
Hi Aaron. As far as #2 goes, I have a similar issue. Our performance between regions is bad so I have to keep db copies on each server. I use a batch file. If you are not familiar with batch files, just go into notepad. Copy this into the file and change to meet your needs:

::Update Chicago
Copy \\YourServer\Folder\DB_Tables.mdb \\TheirServer\Folder\Folder/Y
Copy \\\YourServer\Folder\FrontEnd.mde \\TheirServer\Folder\Folder/Y
::

Note that any line that starts with a colon will be skipped. I use these for spacers and comments and that the /Y states that you want to replace the file if there is already one on the server.

You can put as many lines as you need to on the batch file. When you are finished writing it, save it as a .bat file.

If you want it to run automatically, go into the control panel, then to scheduled tasks. From there you can schedule it to run at a certain time every day/week/month.

As far as #3 goes, you are copying over the front end to the new servers, but what are you trying to link the enw front ends to? Your SQL tables?

-a
0
 
LVL 2

Expert Comment

by:CSBTech
ID: 18792979
Aaron,

1 - I don't think you have a problem with this right?  You just link to the mdb so you can update the version number.  If you need help here, let me know.

2 - Copying the files.

            homeDir = "C:\homeDir\"
            MoveToNetDir1 = "F:\myDBNet1\"
            MoveToNetDir2 = "G:\myDBNet2\"
            MoveToNetDir3 = "H:\myDBNet3\"
         
            myfile = Dir(homeDir & "filename.mde")
       
            Do While myfile <> ""

'This will import ALL the files (one at a time, but automatically) in this folder, you can use *.mdb or whatever you wish to do..

                FileCopy homeDir & myfile, MoveToNetDir1 & myfile
                FileCopy homeDir & myfile, MoveToNetDir1 & myfile
                FileCopy homeDir & myfile, MoveToNetDir1 & myfile

                myfile = Dir
                FileCount = FileCount + 1   'if you wish to keep track of number of files
            Loop

'to rename any of the files
                        origFileName = myDB.mdb
                        newName1 = userDB_1.mdb
                        newName2 = userDB_2.mdb

                        newName = "G:\NetDir1\" & newName1
                        FileCopy origFileName, newName
                        newName = "G:\NetDir2\" & newName2
                        FileCopy origFileName, newName
                       
'to delete the original file

                       Kill origFileName

Number 3, why would you need to recreate the links to the database server?

JV
0
 
LVL 2

Accepted Solution

by:
CSBTech earned 500 total points
ID: 18793006
Aaron,

Sorry, the code is messed up, I changed some variables in some places, here is the new code.

            homeDir = "C:\homeDir\"
            NetDir1 = "F:\myDBNet1\"
            NetDir2 = "G:\myDBNet2\"
            NetDir3 = "H:\myDBNet3\"
         
            myfile = Dir(homeDir & "filename.mde")
       
            Do While myfile <> ""

'This will import ALL the files (one at a time, but automatically) in this folder, you can use *.mdb or whatever you wish to do..

                FileCopy homeDir & myfile, NetDir1 & myfile
                FileCopy homeDir & myfile, NetDir2 & myfile
                FileCopy homeDir & myfile, NetDir3 & myfile

                myfile = Dir
                FileCount = FileCount + 1   'if you wish to keep track of number of files
            Loop

'to rename any of the files
                        origFileName = myDB.mdb
                        newName1 = userDB_1.mdb
                        newName2 = userDB_2.mdb

                        newName = "G:\NetDir1\" & newName1
                        FileCopy origFileName, newName
                        newName = "G:\NetDir2\" & newName2
                        FileCopy origFileName, newName
                       
'to delete the original file

                       Kill origFileName
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now