Microsoft Access Office VB VBA Help and Examples
           
             

Running Access 2000 and Access 97

The fourth in an occasional series of rants by Chris Shepherd

Now here's a question for you - when is opening a file not opening a file? When you are "Enabling" it. What am I talking about? Well here goes:

When you open an Access97 mdb file in Access 2000 you are faced with a choice of converting or opening the database. Knowing that you want to allow your Access97 users to use the db as well you choose open - because open won't change the file. WRONG!

If you watch carefully the status bar gives you some clues as to what is happening. When you open the file it seems to do a save even better if you look at your database properties it has now roughly doubled in size! The catch here is that you haven't opened the file in the way that we would conventionally understand it you have in fact "Enabled" the database for use by Access 2000 clients. Of course this is staggeringly well documented (if you happen to have the Office 2000 "Notes from the Field", TechNet or MSDN).

This is a classic case of mushroom customer management (keep them in the dark & feed them bullshit). To be fair most users will never notice the problem as they use single user databases or multi user databases that are not in constant use. But if you have multi use databases that are in frequent use you will start to get a problem. Once an Access97 user has changed an object (by for instance clicking on the save icon to save their data - when in fact it just saves the form layout) the next Access 2000 user to access the DB has to enable it. And here is the crunch - they need exclusive access to the db to do this! So you end up running round the office getting people to log out of a system that they need to do their jobs....hmmm Nice.

Fortunately there is a solution to this using some neat code written by John Hawkins for a slightly different purpose. What you need to do is split the db in to a backend and a front end db - using the db splitter add-in in Access97. And add a splash screen to your front-end db.

 

Add this code to your start-up splash screen: 
Public Const Serverpath As String = "\\Server\TS\" 
Public Const dbFileName As String = "TimeSheets2.mdb" 
Public Const BatchFileName As String = "TT2upd.bat" 

Private Sub Form_Load() 
   Dim Dbs As Database 
   Dim rst As Recordset 
   Dim dbslocal As Database 
   Dim rstlocal As Recordset 
   Dim remoteversion As String 
   Dim localversion As String 
   Dim RunLocation As String 

   On Error GoTo ErrHand 
   'Set the variable that holds the location of the Frontend to update from 
   FrontEndDB = Serverpath & dbFileName 
   'Set the variable that holds the location of the backend data database 
   BackendDB = DriveMapping & dbBEFileName 
   'Set the variable that holds the location of the update batch file 
   Updater = Serverpath & BatchFileName 


   DoCmd.Hourglass True 
   Me.TimerInterval = 0 
   'Ensure that the system is being run from the C: drive 
   If Left$(CurrentDb.Name, 1) <> "C" Then 
      MsgBox "You cannot run the database in this location. We will try to " _
         & "create a local copy for you now.", vbOKOnly 
      Shell Updater, vbNormalFocus 
      DoCmd.quit 
   End If 

ErrHand: 
Set Dbs = Nothing 
Set dbslocal = Nothing 
Me.TimerInterval = 3000 
DoCmd.Hourglass False 
End Sub 

Then Create a Batch file, detailed below, in this case called TT2upd.bat and store it in the same directory..... 

@echo off 
echo Ready to copy new version... 
echo Press any key once access has closed... 
pause 
echo on 
md "c:\Program Files\TS\" 
copy "\\server\TS\timesheets2.mdb" "c:\Program Files\TS\timesheets2.mdb" 
start c:\Progra~1\TS\timesheets2.mdb 
Exit 

Now this solves the problem for the moment - but what happens when you update the db - you don't want to be running round installing this on loads of PCs. So add a version table to the database with a single numeric field "Version" then change the splashscreen code to: 
Private Sub Form_Load() 
   Dim Dbs As Database 
   Dim rst As Recordset 
   Dim dbslocal As Database 
   Dim rstlocal As Recordset 
   Dim remoteversion As String 
   Dim localversion As String 
   Dim RunLocation As String 

   On Error GoTo ErrHand 
   'Set the variable that holds the location of the Frontend to update from 
   FrontEndDB = Serverpath & dbFileName 
   'Set the variable that holds the location of the backend data database 
   BackendDB = DriveMapping & dbBEFileName 
   'Set the variable that holds the location of the update batch file 
   Updater = Serverpath & BatchFileName 


   DoCmd.Hourglass True 
   Me.TimerInterval = 0 
   'Ensure that the system is being run from the C: drive 
   If Left$(CurrentDb.Name, 1) <> "C" Then 
      MsgBox "You cannot run the database in this location." _
         & "We will try to create a local copy for you now.", vbOKOnly 
      Shell Updater, vbNormalFocus 
      DoCmd.quit 
   End If 
   'Check if local database is latest version' 
   Me.Refresh 
   Set Dbs = OpenDatabase(FrontEndDB, , True) 
   Set rst = Dbs.OpenRecordset("Version") 
   Set dbslocal = CurrentDb 
   Set rstlocal = dbslocal.OpenRecordset("Version") 

   remoteversion = rst!Version 
   localversion = rstlocal!Version 
   If remoteversion > localversion Then 
      MsgBox "A more up to date front end is availiable on the server. " _
         & "We will now try and copy it locally.", vbOKOnly 
      Shell Updater, vbNormalFocus 
      DoCmd.quit 
   End If 

   Me.lblVersion.Caption = "Version - " & localversion 
   ErrHand: 
   Set Dbs = Nothing 
   Set dbslocal = Nothing 
   Me.TimerInterval = 3000 
   DoCmd.Hourglass False 
End Sub 

Now all you have to do is increase the version number on the server and all your users get the latest version. So now your Access2000 users won't upset your Access97 users - any more than they usually do. 

If you have any comments about this article or would like to suggest other niggles with Access please Contact Chris

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.