Applying Dbase changes automatically 4

[Trudye]

Hi Guys, gotta question.

Has anyone ever written a batch pgm that automatically updates all copies of an Acces database.

What I want to do is every time I make a change to the database on the server I would like the changes to be automatically applied to every copy of the database upon OPEN. Thus a pgm that interrogates the Mater Copy(.mde) and applies table or pgm changes.

I have heard of it being done in SQL Server but not sure if it can be done with just an MS Access application.

Thanks much,
Trudye

 

[FancyPrairie]

The simplest way is to create a batch job (preferrably vbscript) that simply copies the file from the server to the user's machine. At startup, you can check to see if the batch job exists, if so, then call it.

I prefer another method which, in the long run, provides more benefits. I've created a table that contains at least 2 fields: strComputerName and ysnNewVersion (my table also contains the name of the primary user of the pc, their phone number, office location, a shutdown flag, and a broadcast message flag). At startup I search the table to see if the computer has been "registered" (i.e. record that contains the name of the user's computer (environ("computername")). If it has not, then I pop up a form and ask the user to fill in all of the information (this is a one time thing for each pc).

Then I check to see if the ysnNewVersion flag is set. If so, I kick off a vbscript, exit the database, and the vbscript copies the new version of the database to the user's machine and clears the ysnNewVersion flag.

I like being able to specify who gets the new version (rather than everyone) because I may have made some changes and I want a group of users to test it prior to releasing it to everyone.

And, if you examine the other fields within the table, you can see that there are other things you can do. Force user's out of the database, broadcast messages to them, call a given user up on the phone, etc.

 

[Trudye]

Thanks FancyPrarie fro the feedback.

But I'm not sure i understand.

Does your vbScript pgm delete the old version or rename it? What exactly does it do?

I assume the table that hods the two fields resides on each database. How and when is the ysnNEwVersion Flag set?

Sorry for the hassle.
Trudye

 

[Checking]

That's the way to go 'FancyPrairie'!
Anyway Trudye, here's a shrinked code extraction which will copy the .mde to the individual user's default folder(MyDocs) when there's a new version, placing also a shortcut to his desktop.
'------ Add following code 'OnOpenForm' to a kind of login form
Dim db As Database, Source, strBackEnd As String, Path As String
Dim wsh As Object
Dim Shortcut As Object
Dim strShortCutName As String
Dim strCurrPrjMDe, strLocalMDe, strosshell, fc
Dim rstVs, rstAu As DAO.Recordset
Set db = CurrentDb
Set rstVs = db.OpenRecordset("tbl_Settings", dbOpenDynaset) 'tbl settings has columns [Descr],[Object]
Set rstAu = db.OpenRecordset("tbl_Settings", dbOpenDynaset)
rstVs.FindFirst "[Descr] = 'Version' " 'Search current version
If rstVs!Object = "version 1.xx.xx" Then
Else 'Start AutomaticUpdate Here
rstAu.FindFirst "[Descr] = 'AutomaticUpdatesFE' "
If rstVs!Activ = True Then 'Rule Activ?
Set wsh = CreateObject("wscript.shell")
strCurrPrjMDe = (Left(CurrentProject.name, (Len(CurrentProject.name) - 1))) & "e" '= Current project. it takes the mde; no mdb
strShortCutName = (Left(CurrentProject.name, (Len(CurrentProject.name) - 3))) & "lnk" '= prepare shortcut
strLocalMDe = Application.GetOption("Default Database Directory") & (Left(CurrentProject.name, (Len(CurrentProject.name) - 1))) & "e" ' = destination folder eg My Documents
strosshell = "MSaccess.exe " & Chr(34) & strLocalMDe & Chr(34) ' copies the .mde in the indivicual LOCAL My Documents
Set fc = CreateObject("Scripting.FileSystemObject")
MsgBox ("Downloading new version; pls wait while downloading your new local version.")
If (CurrentProject.Path & "\") = Application.GetOption("Default Database Directory") Then '
Else
fc.CopyFile CurrentProject.Path & "\" & strCurrPrjMDe, Application.GetOption("Default Database Directory") & strCurrPrjMDe
Set oShell = CreateObject("wscript.shell")
oShell.Run (strosshell)
Set oShell = Nothing
Set Shortcut = wsh.CreateShortcut((GetSpecialFolder(&H10)) & strShortCutName) 'Puts shortcut on desktop
Shortcut.TargetPath = strLocalMDe
Shortcut.WorkingDirectory = (Application.GetOption("Default Database Directory"))
Shortcut.Description = "Shortcut AutoCreated; cfr tel Ref ***"
Shortcut.Arguments = strLocalMDe
Shortcut.Save
DoCmd.Quit
End If
End If
End If
'-------End
'Code works in Access2K, AccessXP
'FYI, Libraries used in my curr proj are
'Visual Basic for Applications
'Microsoft Access 9.0 Object Library
'Microsoft ActiveX Data objects 2.1 Libr
'Microsoft DAO 3.6 Obj Libr
'Microsoft Outlook 9.0 Obj Libr 'No need for above code
'OLE Automation
'Microsoft Scripting Runtime '= Needed !!!
'Microsoft Excel 10.0
When you always place the newest version of the .mde on a central place, and you call a recordset referring to that place, + modify above code to it, then you have what you required.
Have lots of fun; greetz to all!

 

[Checking]

Forgot to mention Trudye that when you hardcode the projectVersion you don't need any tables in the .mde
Further, the code places (or overwrites) the full local copy
Another issue, pls. consider an auto-compact method for the .mde which runs periodically of whenever size goes above a certain limit. This way you can handle systemMaintenace more efficiënt.

 

[FancyPrairie]

If you're going to be doing very much development in Access, you should (if you haven't already done so) create a library database. The library db consists of a FE and a BE and contains functions that can be used by multiple databases. A library is simply an Access database that you create and reference from your other databases (generally with an mda extension rather than mdb).

I store the table that keeps track of the pc's that have been registered, in the BE of my library db. At startup, every one of my databases calls a startup function that resides within my library. This function, among other things, opens this table to see if the ysnNewVersion flag has been set for the current pc. If it has, it then calls the vbscript to copy the new version to the user's machine (deleting the current version).

I have another table that keeps track of changes I make to the databases (revisions). When I document a change, it is timestamped. This timestamp is stored in the revision table and also written to a Custom database property within the current database. Consequently, I always know if the user has the latest version of the database because it is defined within the database. After the change has been documented and the Custom db property has been set, I display a form (datasheet view) whose recordsource is set to the table containing the ysnNewVersion flag. From this form, I can set 1 or more ysnNewVersion flags. By the way, I also store the name of the database within this table (table that contains new version flag). As a result, I have one table that manages all of my databases/projects.

 

[MichaelRed]

The process of managing updates to Ms. A. (.MDB) has been serverally discussed in these fora. There are numerous methods and variations on the theme and processes. I suggest the use of the Search capability (see the banner / header of the page) and a review of the topic.




MichaelRed

 

[Trudye]

Thanks to everyone who took the time to respond.

MichaelRed I always go to FAQ first. But I was not sure what to enter as a search criteria to get what I wanted. I tried entering "Database Changes" but did not get anything useful. Also "(see the banner / header of the page)" I have no idea what this means??

FancyPrarie, I assume FE/BE means FrontEnd/BackEnd. However I don't have a clue what an FE/BE to a library is.

Checking I check out (pun intended) your code and see if I am smart enough to use any/all of it.

I'll try FAQ again the more options you get the better. Although at first blush I kinda like what Checking has posted.

Thanks again everyone,
Trudye

 

[FancyPrairie]

Trudye, a library is just another Access database. The FE contains the forms, queries, modules, reports, and pages. The BE contains just the tables that the FE links to.

If you plan to use Checking's code, then create a new database (i.e. MyLibrary_FE.mda) and copy and paste Checking's code into a new module within the library database, compile it, save it, and exit the library database. Open a module in your database and goto Tools|References and browse to your library database. Now you can call Checking's function as if it resided in your database. Now, every database you create can call Checking's code without having to clone it everytime you want to use it. Your libray database will continue to grow as you add more functions that can be used by other applications.

Prior to referencing the library database, I would suggest you convert the library database to a .mde and reference the mde file rather than the mda. The mde will be smaller and the users will not be able to mess with or see your code. And it will be easier to maintain. But don't get rid of the mda version. You can't edit an mde file. So you will still need the mda version to add to or modify.

 

[Trudye]

Thank you so much FancyPrarie and you too Checking. We are wrapping up the conversion this week. I don't code VBScript but no time to learn like the present.

I appreciate your patience and both of you hanging in there with me.

Be well and enjoy your turkey day.
Trudye

 

[chrisbee]

robocopy

Simple and quick

 

[Trudye]

Ok, Ok, this is starting to become clear as Mud.

I have a question (before this is over with rest assured I will have many more). This is my first time so be gentle in your explanations.

What is a Custom database property?

Also if I am understand FancyPrarie correctly (and me thinks I yam), a PC can be several version in arrears. When that PC opens a database all changes are applied to bring it up to date. Now for the dumb question. Are the changes applied individually? Say I added a line of code to On_open event, is that line of code added or is the whole database replaced?

What happens if it is discovered that a change is causing problems? Do you keep the previous versions to facilitate a quick recovery process and get your Users back up and running quickly with the old/previous version?

I must admit I have never heard of an .MDA file before except for System.MDA (which I have never used either). Of course what I've never heard of could make a whole New World. I already have a database that contains tables only. So I would rename that database and add and customize Checking's code?

OBTW Chrisbee thanks so much for the input but I better learn how to do this a'natural it's usually better in the long run.

Thanks again,
Trudye

 

[MichaelRed]

at the considerable risk of redundancy, use search. At the certain fate of repition, look toward the 'top' of the (web) page for this term and invetigate the two approaches to clicking on the button / link (left & right). Explore the 'search by form' results. Read the numerous articles (threads) previously posted on the subject. Most of the answers to your (current) question are embedded within the existing 'literature'.

Sorry to intrude on your facilities.






MichaelRed

 

[FancyPrairie]

1. Custom Database Property ... Within the Database window, select File|Database Properties. Then select the tab labeled "Custom".

2. You always want to keep a previous copy of your database in case the changes you made (and distributed) make problems worse. You will need to restore the previous version.

3. The entire FE database is copied to the user's machine. Regardless of whether you made a one line change or a complete overhaul.

4. An .MDA file is just a way for Access to identify wizard and library databases. It's just another database with the extension of mda rather than mdb. Other than the extension, there is really no difference.

 

[MichaelRed]

" ... there is really no difference ..."

Really? You can [edit | Change } Modify] an MDA? Please tell me how you do this?





MichaelRed

 

[FancyPrairie]

Yes, you can [edit | Change } Modify] MDA files. Assuming, of cource, that the owner of the MDA file grants you those rights. For example, I can create a library database (mdb), and give group Users all rights to the database. Then I can rename the database with an mda extension. Consequently, anybody can do want they want with it.

 

[Trudye]

MichaelRed I would love to do a SEARCH, but first of all I have no idea what to serach on, and second the SEARCH function is temporatily defunct.


and invetigate the two approaches to clicking on the button / link (left & right). Explore the 'search by form' results

Sometimes I'm a wee bit slow. Not sure what you mean here. It may become clearer when the Search function returns and I can see the Search page again.

Thanks much,
Trudye

 

[MichaelRed]

Searching in these fora always works better with a bit of creativity (thouoghtful words | expressions | phrases). A modest starting point might include [Update |FE |BE | Remote | Network | ... ]. Following these, try different combinations and 'glean' additional items/terms from the content of the original threads which seen promising.

AS for the availability of "search", it has been the stepchild of the site since I first wandered hereto and is, perhaps, the feature which most often changes 'status', although I haven't seen much increase in functionallity. It seems liken unto the phrase "it is the worst ... except for any )all) hte alternatives.

Some have suggested that it is possible to get Goggle to do better searches on Tek-Tips than Tek-Tips does on itself, but I havn't needed 'sttuuuffff' so desperatly that I've sidetracked to actually do it, so can only mention it in passing.





MichaelRed