New Version Updating - More Complicated!! Or maybe it's just Me. 1

[laakins]

Okay,
I'm in an multi-user enviroment & most of the time they use multiple databases. The frontend of the database has to reside in a specific path on the users machine depending on what type of machine the user signs in on (which I can obtain by getting the username from the system). I've been trying to get a handle on it all. I've created a library database along the lines suggested by FanciePrairie (thread181-468123 & thread181-600174), but my issue comes up with how to go about updating the new versions. Most of the examples here talk about batch files or vbscripts and deal with 1 batch file per database. I don't want a batch file for each database, there are too many, I want to be able to tell it which database, etc. I've never written a batch file myself so I'm not even sure if this can be done or if I have to go about it a different way. In addition, I want to be able to copy the database to a different, & correct, location if for some reason the user has moved the front end. The location again would be different depending on the machine. Plus, is there any way to know if the user opened a database using a shorcut or not, and where that shortcut is located? Any help or ideas would be greatly appreciated.

 

[AvGuy]

Spend some time studying replication and see if that doesn't give you some ideas.

AvGuy

 

[MichaelRed]

replication is for the DATA, not the programmatic objects.

there are other approaches. see thread181-30072 for one example (this was noted in one of the threads you preciously referenced) It is -perhaps- a bit more effort to get the process set up, however (in my opinion) it is as effective and requires less effort (on MY part) and if the 'whole' regime is set up properly, is safer than the generic 'blind copy'.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[FancyPrairie]

It sounds like you have a lot of databases to maintain and that some users may have more than one database on their machine. I have the same problem. What I describe below makes it real easy for me to maintain all of my databases. It is not as complicated as it may appear but will take a little time to develop (well worth it when it is done). What you can do is pick the pieces you need immediately and build them into you library database. You can then build the other pieces at your leisure.

As you can see, there are many ways that you can do what you want, but this works for me.

From my desk I can force users out of a given database, send a new copy or version of the FE db to one or more users, see who's logged in to a given database, and broadcast messages to one or more users.

In every database I create, the AutoExec macro calls a startup function who, in turn, calls a startup function in my library database. The reason I have a local startup function is because on rare occasions I have to do something specific for that database at startup.

1. I pass the name of a table to my library startup function so that I can determine which database it resides in. I can then determine whether my tables are linked to the "Live" database or a "Test" database (stored in a public variable).

Call Lib_StartUp ("tblName&quot

2. It then checks to see if I'm the CurrentUser. If I am the CurrentUser, it then checks to see if the database (project) has been registered. If not, it pops up a form which forces me to define/document the project. This is some of the information I collect (you can add or delete what you want).
1. Project name/title
2. Name of the FE database
3. Name of the BE database
4. Name of the Workgroup file that is used by this db
5. Name of the share where the Master copy of the FE database resides
6. Name of the share where the Live version of the BE database resides
7. Name of the folder where the FE db resides on the user's machine
8. Name of the share where the workgroup file resides
9. Name/phone number of primary and secondary support personnel
10. Comments to describe the purpose, etc of the database
11. Latest revision date

With this information you can do a number of things. For example, when you need to copy a new version to the users' machine, you know where to copy from (5) and where to copy to (7). Also, if you're on vacation and someone else is supporting your databases, they may not know how to startup your database (i.e. what workgroups to use, etc). Consequently, you could have a form with a combobox that lists all of your databases (1) and when they select one, it opens the databases like this: \path\msaccess.exe Item5Above\Item2Above /wrkgrp Item8Above\Item4Above.

3. It then checks to see if the pc has been registered. If not, it pops up a form which forces me to define the computer. This is some of the information I collect (you can add or delete what you want)

1. Name of the pc (environ("computername&quot)
2. Name of the primary user/owner of the pc
3. Phone number of primary user
4. Location of the pc (office number,etc)
5. Shutdown flag (if set, db immediately shuts down and new users are not allowed to login)
6. New Version flag (if set, a new version of the database is copied to the user's machine. Note the project table above will let you know where to copy from and copy to.)
7. Broadcast Message flag (if set, a message from the administrator (you) is displayed on the user's machine)
8. Reregister computer flag (pop this form up at startup. Sometimes when I install a database on a user's machine, they are not there so I can't get their phone number, etc. This allows me to get it at a later date.)

4. It then checks to see if the user has been registered. If not, it pops up a form which forces me to register the user. This is some of the information I collect (you can add or delete what you want)

1. Name of the user
2. phone number
3. Location
4. Name of form they want opened at startup (some users always go directly to the main edit form, others go directly to the report criteria form. This allows the user to specify which form they want to go to at startup) (A shortcut can also be put on their desktop, but this works better for me)

5. If then checks to see if a new version of the database (NewVersionFlag is set) needs to be copied down to the user's machine (This relates to your original question). If it does, then (instead of using a batch job), I launch my library database, like this:


strAppName = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE" & " " & CodeDb.Name & " /user " & gstrLibUser & " /pwd " & gstrLibPwd & " /wrkgrp " & gstrLibWrkGrp

strCommand = " /x MyAutoExec /NoStartup/Cmd " & _
"NewVersionFileCopy" & ";" & _
strComputerName & ";" & _
strCopyFrom & ";" & _
strCopyTo & ";"

Call Shell(strAppName & strCommand, vbNormalFocus) '1 Library is launched and the function NewVersionFileCopy is executed via the macro MyAutoExec

Application.Quit 'Current FE exits (library db open now)

Note that gstrLibuser, gstrLibPwd and gstrLibWrkGrp are public variables. The first 2 are constants and the third one has been read from my project library. You can also get it via a system command (forget offhand)

At this point your database has exited and the library database is opened. Now the function NewVersionFileCopy simply copies a new version to the user's machine and then launches the user's FE db and the library db exits. Prior to exiting, the NewVersion flag will need to be cleared.

Note that NewVersionFileCopy knows where it is to copy it from and to based on the command line arguments used to launch the library database.

 

[AvGuy]

Michael:

Replication works both on data and programmatic objects.

 

[MichaelRed]

? " ... Replication works both on data and programmatic objects ... " ? There is no reference to replicating any 'objects' in My help. I have not attemoted to use this for several years (ver '95 or '97) and at that time, is was certainly restricted to DATA. Perhaps the evoloution of Ms. A. has 'improved' the functionallity, it is just not apparent / visible to me.

Sorry to interrupt ...



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[laakins]

I appreciate the responses. Forgive me, FancyPrairie, but it's Friday & for some reason half my brain is still home in bed sleeping. I'm not quite understanding everything you stated above.

I've already started a "library" database (though I haven't implemented it's use yet, still setting everything up - hence this question) where the 2 main tables are a 'log' table to keep track of usage in all of my databases and a 'database info' table to hold some of the information listed above in #3. I can create a table suggested in #2. I'm not using workgroup files & am not quite sure how that affects everything above. And I have a problem with Item 7 because the database can have 1 of 2 locations depending on the type of machine (but I can determine that via code). In the database I'm testing all this on I have an autoexec macro that runs a startup procedure that checks to see if the database has been registered, if the shutdown flag, msg flag or new version flag has been set.

It's when I look at your #1 & 5 that I wish I had brought my whole brain into work today, especially 1. So I need a little more help on those.

Does anyone know about the shortcut questions I asked above?

 

[FancyPrairie]

My brain is confused too. Let's start with item 1. In my applications, if I'm the CurrentUser, I like to display the word "Live" or "Test" on each of my screens so I don't inadvertantly change data on the "Live" database. Hence Item 1. Supppose I have a table named tblABC that resides on the BE. I have declared a Public Const (gLivePath) that defines the path and name of my Live database (could get this via the Project table). What Item 1 does is determines which database tblABC is linked to and stores that value in another public variable (gLinkedPath). If the gLivePath = gLinkedPath, then I know I'm linked to the Live database. Else Test. So in the OnOpen event of each of my forms, I compare the 2 public variables; if they're equal I set the caption of a label to "LIVE". Else "TEST".

As far as the workgroup files go, I would still include them as if they existed. That way, if you start using security (hence workgroups) at some later date, you don't have to do any retrofitting. About the only place you have to worry about them is when you try to open a database. For example,

If (len(gstrLibWrkGrp) = 0) then
strAppName = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE" & " " & CodeDb.Name & " /user " & gstrLibUser & " /pwd " & gstrLibPwd
Else
strAppName = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE" & " " & CodeDb.Name & " /user " & gstrLibUser & " /pwd " & gstrLibPwd & " /wrkgrp " & gstrLibWrkGrp


I'm confused about your reference to Item 7. That's for broadcasting messages. Please elaborate.

Item 5 is how I copy the database to the user's machine via Access (vs batch file). It is a little more complicated to explain. Remember that when you execute Access, one of the options is /cmd. Which is your command line prompt.

So, backing up, your database is currently executing and has called a routine in your library to check to see if a new version needs to be copied to the user's machine. If so, then the library routine builds the string to launch your library database as another instance of Access (via the shell command above) (you now have 2 instances of Access running on the user's machine). strAppName is the string that contains the commands to launch access and opens the library database. strCommand is the command line arguments that, once the library database is opened, can be read to determine what the function NewVersionFileCopy is supposed to do. As soon as you issue the shell command you issue the Application.Quit command which causes the first instance of access to exit (the user's current database).

At this point, the 2nd instance of access is running (which is your library database). And the function
NewVersionFileCopy is now executing. It parses out the command line. The command line indicates where to copy from and copy to. Now, this is a version of my library database prior to my Project table. You could avoid some of the command line arguments since they can be read from the Project table. Simply issue the FileCopy command to copy the new version to the user's machine. For example,

'***********************************
'*  Let user know what's going on  *
'***********************************

    MsgBox "New Version!" & vbCrLf & vbCrLf & "A new version of your database exists on the network.  Select OK to begin copying the database from the network to your PC.", vbInformation, "Distribute New Software"

'****************************************
'*  Copy new version to user's machine  *
'****************************************

FileCopy_NewVersion:

    FileCopy strCommand(1), strCommand(2)  'strCopyFrom, strCopyTo

'**************************************
'*  Let user know it has been copied  *
'**************************************

    MsgBox "The database has been copied successfully to your machine."

'****************
'*  Clear flag  *
'****************

    Clear NewVersion Flag


'*********************************************************************
'*  Exit Subprocedure and Open new version of user's database  
'*
'*  Note 2 instance of access will be running after the shell command
'*********************************************************************

ExitProcedure:
    
    On Error Resume Next

    Call Shell(SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE """ & strCommand(4) & """ " & strCommand(5), 1)
    
    Application.Quit    'Library database will now exit (only 1 instance of access will no be running
    
    Exit Function
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:

    '*******************************************************************************
    '*  Err = 70:  The original application did not Quit like it was supposed to.  *
    '*             Consequently, this program can not copy the new version because *
    '*             the old version is still open.                                  *
    '*                                                                             *
    '*             Continue trying every second for the next 10 seconds.  After,   *
    '*             10 seconds tell the user what's going on and ask them if they   *
    '*             want to continue trying.                                        *
    '*******************************************************************************
    
    If (Err.number = 70) Then
        
        bytCount = bytCount + 1
        
        If (bytCount > 10) Then
            If (MsgBox("Failed to exit!" & vbCrLf & vbCrLf & "Unable to download the new version of the database.  The original database is still open." & vbCrLf & vbCrLf & "Do you want to continue trying?", _
                       vbExclamation + vbYesNo) = vbNo) Then Resume ExitProcedure
            bytCount = 0
        End If
        
        sngStart = Timer
        Do Until Timer - sngStart > 1#: Loop
        Resume FileCopy_NewVersion
    End If
    
    '**********************
    '*  Unexpected error  *
    '**********************
    
    MsgBox Err.Description, vbExclamation
                   
    Resume ExitProcedure

Note that strCommand(1) and strCommand(2) were parsed from the command line prompt (see CopyFrom and CopyTo). Also, strPathNameOfUserFrontEnd and strWorkgroup came with the command line prompt (you can also get this info from the project table).


Once the database has been copied to the user's machine, and you have cleard your NewVersion flag, issue the shell command again to startup the new version of the user's database (located on his/her machine) and issue the Application.Quit command so that the library database exits.

Now, you can do all of this in script, but this works fine. Just hard to explain.

 

[laakins]

OK, my bad. I didn't have my library database referenced as a "library" in my test database. That would help when I tried to call the procedure. I really need a good slap across the face to wake my brain up.

I'm not sure I need to do #1 quite yet, but I'm sure it will come in handy once I get the rest of this up and running. I'm not sure if I'm up to adding that feature to all the forms in all my databases. I'll added it to any future databases & eventually when time permits add it to the 'old' ones. I think I understand it pretty much now. Thank you.

I'll add workgroups (though our PCS department won't let us use them), things always change in the future. Item 7 was referring to the Project table: 7. Name of the folder where the FE db resides on the user's machine. It will be different depending on what type of machine the user signs on to, but it can be determined by their "username". Without going into to much detail, let me know if that clarifies it.

I think I have a better handle on it after your second explaination. I'll try it & let you know if I have any more problems/questions. I'm pretty sure I will.

 

[FancyPrairie]

OK, so I didn't scroll up far enough to see the "other" item 7. In your case I would have 2 fields in my table that indicated where the FE resides on the user's machine. And 2 more fields indicating machine type.

For Example,

strPath1
strMachine1
strPath2
strMachine2

Now, when you go to copy the data to the user's machine, just determine their machine type. If their machine matches the value of strMachine1, then copy the database to strPath1 ... and so on.