Lock Access database 3

[cthai]

Currenly I have this mesgbox that pops up when user are using the wrong/old version of the database in access... is there away to lock access to force the user to download the lastest version? I understand that access is on the client site, and when the user click 'OK' on the pop up box the user can still use the form. I want to stop that and force the user to download the correct version.

is there away to do this?

thank you

 

[lameid]

Look at the FAQ's.... I know it is in there but I got to catch a bus so I can't find it now for you

 

[cthai]

Hi lameid -

thanks! I'm new at this and didnt even know there was so much information in FAQ's! I took a look at it and didnt find anything that you can force the user to update to a new release of the access db -

Right now I have a msgbox that tell the user they are currently using the wrong version. and i also have a table that let me know who is still on the old version, i would like in the code to lock the older form so it force the user to go to the F drive to download the correct version.

RunCommand acCmdAppMaximize
DoCmd.Maximize
Version_In_VBA = "AccessDB 4"
Label6.Caption = Label6.Caption & " " & Version_In_VBA
modGlobalVariables.setAccessDBVersion = Version_In_VBA
DoCmd.RunSQL "Execute dbo.spAccessDBcurrent '" & UserName() & "', '" & AccessVersion() & "', '" & Date & "'"
'MsgBox AccessDBVersion()

StrSQL = "SELECT DatabaseVersion FROM dbo.tblAccessDatabaseVersions WHERE (UserName = 'currentAccessuser')"
dbs.ConnectionString = Application.CodeProject.BaseConnectionString
dbs.Open
Set Reader = dbs.Execute(StrSQL, , adCmdText)
Version_In_DB = Reader.Fields("DatabaseVersion")

If Version_In_DB <> Version_In_VBA Then
MsgBox "You are using an old version of Access Database, please go to the F drive and download an updated one."

 

[StarPassing]

To force the user to go to the F drive and download the correct version, simply add this next line below your msgbox line:

application.Quit acQuitSaveNone

This will close his program.

Tom

 

[cthai]

Hi StarPassing !

thanks! I didnt even think about forcing the applicantion to close... but that's a great idea.

Much appreciated.

 

[kjv1611]

What about... could you automatically open or download the correct version for them? Perhaps prompting for a "save as" location if it's not a standard location? That might be a little more user friendly about it... then you could open the new database after copying the new file, and close the current/old/wrong one.

 

[addy]

There is a way of checking for a new version and automatically updating it if it is out of date. It is something we use on virtually all of our 'Front End' Access applications.

For this to work (as we do it, you can probably modify slightly if required), you need a central location to store the latest 'Master' version (Network drive which everyone can access, for example), a database in this same location (can be elsewhere but makes sense to keep it together) which will hold a table with the Master Version Number in it, and a local table in the 'Front End' copies which will hold the Local Version Number.

So, create a Database called Master Version (in the Network drive), create a Table called tbl_VersionMaster and add a text field called VersionNumber. This will be the Version Number you update when you want to roll out a new version.

In your Front End, create a Table called tbl_VersionLocal, add a text field called VersionNumber. This holds the current version of the front end DB.

In a new Module, add the following:

Option Compare Database

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

So, in the database, I load a Form on startup which acts as a 'Splash Screen'. I then use a Onload event to run the update process. So:

Dim strVerClient As String, strVerServer As String

strVerClient = Nz(DLookup("[VersionNumber]", "[tbl_VersionClient]"), "")
    strVerServer = Nz(DLookup("[VersionNumber]", "[tbl_VersionMaster]"), "")
    
    If strVerClient = strVerServer Then

'OK, the local version number matches the master version so we don't need to update. Generally you would add some code here to close the 'Splash Screen' form and open your main navigation form

Else

Dim strSourceFile As String, strDestFile As String
        Dim strAccessExePath As String, lngResult As Long
    
        'Create the source's path and file name.
        strSourceFile = "EnterThePathToYourMasterVersionHere.mdb"
'Change this value to the path to the Master Version you have on the network
        strDestFile = CurrentProject.FullName
    
        'Determine path of current Access executable
        strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "
    
    
        If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.
        MsgBox ("Please see system administrator")
        Else 'copy the new version of app over the existing one.
        lngResult = apiCopyFile(strSourceFile, strDestFile, False)
        End If
    
        'Modify strDestFile slightly so that it can be used with the Shell function
        strDestFile = """" & strDestFile & """"
    
        MsgBox "Application Updated. Please wait while the application restarts.", _
        vbInformation, "Update Successful"
    
        'Load new version, then close old one.
        Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
    
        DoCmd.Quit
    
    End If

In your Database, you need to link the tbl_VersionMaster table which is held in the Master Version database created earlier.

To start with, make these modifications to your database and set both the local and master versions to 1.0

Make sure all your users have a copy of the new front end and place a copy of it on the network drive (make sure the path is exactly the same as the one entered above, i.e. 'strSourceFile = "EnterThePathToYourMasterVersionHere.mdb"')

When you need to roll out an update, make your modifications to the master version (on the network drive) and then update both the tbl_VersionLocal and tbl_VersionMaster tables to a new version number (e.g. 1.1).

So, when your users next open their front end, their local version will be 1.0, but the master version (in their linked table) will show as 1.1. This will kick off the update process and will copy over the latest master version and will restart their database.

This will now show both their local version and master version as 1.1 and so the database will then open as normal.

Any probs, post them here and I'll try to help

 

[kjv1611]

Thanks for posting that, addy! I know that I hope to get some things moving in that direction with some of our databases... but so far I've just been patching some, and others trying to determine what should be or will be converted to a new system which will replace another dedicated system and several access databases... or what should be moved to Microsoft Reporting services.... etc etc...

I, regardless, hope to begin at least testing with an idea of that sort in the VERY near future... we shall see.

cthai,

Assumimg addy's suggestion works, that is REALLY the way to go, I think. This not only makes it user friendly, but it makes it manager friendly, and you friendly. Reason being you'll end up having people ask questions of where to get the database, how to copy it... what to do when it asks "do you want to overwrite?" that sort of thing... if you automate the process, no questions, it just works.

 

[cthai]

Hey Addy!

thanks for the idea - I'm going to give this a shot - we are rolling out a new version in 3weeks - i do have a table in the backend telling me who is still using an old version. i'm going to create and rename it to your suggestion and test it out hopefully this will work so that went we roll out the new version it will be less of aheadach! will keep you posted -

 

[addy]

No worries - if you have any issues let me know. I have rolled this out in about 10 systems here at work. Once you get it right for the first time, it's so easy to include it in all systems.

If you get stuck, I'll help out.

 

[lameid]

Wow, I looked high and low and I can't find that FAQ... What's worse is I can't find my version of the code either.


Generally people write VBA that checks the version number in the local frontend and the current version in the backend, if it is out of date it executes a file that copies the new version. My version used a batch file... I think the FAQ that has disappeared used some VB code. In this scenario the catch is having the application wait for Access to close.... Or you can use Addy's method... The difference being that his seems to open the database in a different location which whould have issues if users have shortcuts to the file.

Come to think of it I think I posted my code a long time ago before I saw the FAQ... It might be around. If anyone digs it up please post the thread here.

 

[kjv1611]

Yeah, that sounds like a great idea... I'd be interested in looking at your version, lameid, and comparing the two... possibly coming up with a combination - if some bits seemed better in one than the other... or more preferred, whatever the case might be.

I did a quick glance search, but probably not searching correctly.

 

[lameid]

Ok I looked... I think I might have posted earlier than the results search...

Any way I found a thead with a link that ultimately redirects tot heis site....

http://www.autofeupdater.com/

Don't have the time to read the site to have an opinion but there it is.


Thread referenced: thread702-1515215

 

[kjv1611]

Hmm, well if that's it... I thought you were referring to a script you had put together yourself rather than a commercial product. I think I'd try addy's suggestion first - with or without modifications - before trying to advise my employer to spend probably a few hundred to a few thousand dollars for such a product.

 

[kjv1611]

Actually, looks like it used to be free - or donation based. But the guy got to where he wanted to make it commercial instead.

Here's that link:

http://www.autofeupdater.com/pages/licensingfaq.htm

 

[lameid]

No I had my own script... I lost it in leaving a job or less likely have not dug deep enough... I also think my post is too old for the search to find.... I just pointed out what me looking for my post did turn up.

 

[addy]

My version 'overwrites' the current front end version by copying across the new master version, overwriting the existing file, so it always opens in the same location.

The basis of my code I received from someone else a long time ago and I have modified to suit.

Wish I could find the original website now so I can credit the person and point you in the direction of their site. But it was about 8 years ago now and I cannot find it at the moment.

 

[addy]

Well blow me I've found it

The link is here:

http://www.databasejournal.com/feat.../Automatically-Deploy-a-New-Access-Client.htm

The guy's name is Danny Lesandrini. I remember emailing him when I came across this link and he got back to me within a day and helped me out.

Like I said, this link is from 2003 and was the basis of our implementation of it.

 

[cthai]

Hey Addy!

The codes works great! 2 weeks to go before we roll out the new version.... this is a great script... and thanks a bunch for sharing!

 

[kjv1611]

Thanks for the update, addy.

And I understand, lameid. If you ever happen to come across what you used, I'd be interested in seeing it.

Thanks.