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
| 