I have created Access application which is split into frontend and backend .
There are around 1000 users who will be using the file.
So instead of opening the shared file , I want to build a functionality so that when the user open it then a copy of it gets saved to their personnel drive i.e. U drive and the access frontend which is saved in u:\ drive gets open . The purpose of this is to make sure each user will be working on its own copy and it won't cause any disturbance if there are 100 concurrent users using the application.
Have a google for auto updating front end. This will give you a way of managing updates and making sure users are always on the latest version.
You should be storing the FE locally on the users machine NOT on a network share, even if it's a unique folder for that user. Access works best this way.
Minty, If I save FE locally on the users machine then whenever a change is made in the system then a new copy will need to be made on the users machine. Is it right?
Yes - correct. That way if their local copy gets corrupt you have two bonuses ;
1. Only the single users work is disrupted for a short period of time.
2. Simply copy the master network copy back over the locally corrupt one and they are back up and running.
Have the batch file check the master location for, say, BLOCK.txt, and if it exists, terminate. (This lets you keep everyone out when required.)
Have the batch file check for an existing local copy of the application- if it exists, delete it.
Then have it download the master copy to the local machine. I recommend putting it in C:\Users\Public\ in a subfolder sharing the application's name.
Finally, have the batch file run the app.
Once that's done, save the batch file in the master file location on the network and just distribute a shortcut pointing to the BATCH file to all your users. (You'll want to give the shortcut a neat icon, too, so it's not the default 'batch file' icon.)
If you don't want to do that, then as Minty said, there are all SORTS of posts on this forum about this topic. I know of at least one thread in the Code Repository subforum that covers this topic, too.
With 1000 users I expect you are on a domain. Use Group Policy to distribute the FE. This way you can distribute a file without the master being accessible to users.
Galaxiom, sorry what do you mean by distributing FE? At the moment I have asked users to save a copy on their personal drive and then start using it. SO whenever I make a change to the master file , I ask users to copy a new version on their drive and delete old one.
Just wondering what else could be done so that they don't have to delete the old copy and it should replicate the old one with new version automatically.
Without being rude Aman with 1000+ posts your ability to search either these forums or use google seems very limited or you just aren't trying or there is a big language barrier that is hidden by your good use of English in your posts.
When the user opens the FE, they get told they do not have the latest copy and a batch file created on the fly copies the latest and then reopens the FE.
Of course we do. I never said otherwise - I said to have it look for BLOCK.txt, which can be an empty text file. It's only purpose is to cause the batch file to abort if it exists.
Yeah, the code I put in the Code Repository is based on Bob Larsen's code, too. That's what I always used until I wound up at the place we're at now. The main reason we still use batch files here is, I think, inertia.
Well, that and the fact that it doesn't require a version file.
Frothingslosh, I have taken your code as below. This code is written in a module.
I have created tables as you suggested in that thread,
1. Local table in access frontend: This table is called tbl-fe_version, this table has 2 fields (ID,fe_version_number) . ID has autonumber datatype and fe_version_number has text datatype.
I have stored in the field "fe_version_number" value Test- AutoStore New Version
2. The 2nd table has been created in the backend which is linked to the frontend. This is named "tbl-version_fe_master" which has 2 fields (ID,fe_version_number) . ID has autonumber datatype and fe_version_number has text datatype.
3. The 3rd table is again created in the backend which is linked to the frontend. This is named tbl-version_master_location ,which has 2 fields (ID,s_masterlocation). s_masterlocation field has S:\Access Databases\AmanFolder
When I try to run it, then nothing happens. I am doing anything wrong? I haven't changed the code at all.
Any help will be much appreciated.
Thanks
Code:
Public Function CheckFrontEnd() As Integer
' ************************************************************
' Created by : Scott L Prince
' Parameters : None
' Result : Determines if backend can be reached, and if front end is the current version.
' Returns : 0 - Misc Error
' : 1 - No current version found in Version Manager file
' : 2 - Front end being run from master location
' : 3 - Master file path not found in Version Manager file
' : 999 - Front end current
' Date : 5-30-14
' Remarks : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes :
' ************************************************************
Dim FrontEndVersion As String 'Front end version number
Dim MasterVersion As String 'Master version number
Dim MasterPath As String 'Location of the master FE file
Dim BatchPath As String 'Location of the batch file that does the actual update
'Determine master version number.
MasterVersion = DLookup("fe_version_number", "tbl-version_fe_master")
'Determine if the database containing the version information can be accessed.
Select Case MasterVersion
Case "" 'Master version number cannot be found, or backend/version manager is missing.
CheckFrontEnd = 1
Case Else 'Version data found.
'Look up the path for the master file location.
MasterPath = DLookup("s_masterlocation", "tbl-version_master_location")
'Determine if the master file is being run rather than a local copy.
If Nz(MasterPath, "") = "" Then
'No master path was found. Return error value.
CheckFrontEnd = 3
ElseIf MasterPath = CurrentProject.Path Then
'The actual master file is the one being executed.
CheckFrontEnd = 2
Else
'Master file path found and is not being run. Determine the version number of the active front end.
FrontEndVersion = DLookup("fe_version_number", "tbl-fe_version")
'Compare the version number in the front end to the master version number.
Select Case (FrontEndVersion = MasterVersion)
Case True 'Version numbers match.
'Return "OKAY" result.
CheckFrontEnd = 999
Case False 'Version numbers do not match.
'Create the path for the batch file used to update the front end.
BatchPath = CurrentProject.Path & "\UpdateDbFE.cmd"
'Check for an already-existing BatchPath, and kill it if it exists.
If Dir(BatchPath) <> "" Then Kill BatchPath
'Notify the user that the application will update.
MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
"Your program is not the latest version." & vbCrLf & vbCrLf & _
"The front-end needs to be updated. The program will now close and then should reopen automatically.", _
vbCritical
'Execute 'UpdateFrontEnd'.
UpdateFrontEnd CurrentProject.Path & "\" & CurrentProject.Name, MasterPath
End Select
End If
End Select
End Function
Private Sub UpdateFrontEnd(ByVal LocalFilePath As String, _
ByVal MasterFileFolder As String)
Dim BatchFile As String
Dim MasterFilePath As String
Dim Restart As String
'Set the file name and location for the file to copy
MasterFilePath = MasterFileFolder & "\" & CurrentProject.Name
'Set the file name of the batch file to create
BatchFile = CurrentProject.Path & "\UpdateDbFE.cmd"
'Set the restart file name
Restart = """" & LocalFilePath & """"
'Create the batch file
Open BatchFile For Output As #1
Print #1, "@Echo Off"
Print #1, "ECHO Deleting old file..."
Print #1, ""
Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
Print #1, ""
Print #1, "Del """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Copying new file..."
Print #1, "Copy /Y """ & MasterFilePath & """ """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Starting Microsoft Access..."
Print #1, "START /I " & """MSAccess.exe"" " & Restart
Close #1
'Run the batch file
Shell BatchFile
'Close the current application so batch file can execute.
DoCmd.Quit
End Sub
Honestly, man, it's easier to just write up the batch file like I suggested in the first place. I'll even attach an example.
I'd help with the Access version, but I just got handed something this morning with a seriously short deadline and don't really have the time to troubleshoot. Just from the sound of it, though, if you're executing it and getting no result at all, it sounds like it's determining that the front end is current.
My suggestion would be to place a Stop command as the first line in the CheckFrontEnd procedure, and then step through the code as it executes so you can see what's going on. Another option is to use the immediate window to print the value being returned by the procedure so you can see what it's reporting.
I'll try to check the thread when my phone tells me it has replies, but I doubt I'll be able to help much until this evening.
Edit: Helps if I attach the batch file! :banghead:
Frothingslosh, I have taken your code as below. This code is written in a module.
I have created tables as you suggested in that thread,
1. Local table in access frontend: This table is called tbl-fe_version, this table has 2 fields (ID,fe_version_number) . ID has autonumber datatype and fe_version_number has text datatype.
I have stored in the field "fe_version_number" value Test- AutoStore New Version
2. The 2nd table has been created in the backend which is linked to the frontend. This is named "tbl-version_fe_master" which has 2 fields (ID,fe_version_number) . ID has autonumber datatype and fe_version_number has text datatype.
3. The 3rd table is again created in the backend which is linked to the frontend. This is named tbl-version_master_location ,which has 2 fields (ID,s_masterlocation). s_masterlocation field has S:\Access Databases\AmanFolder
When I try to run it, then nothing happens. I am doing anything wrong? I haven't changed the code at all.
Any help will be much appreciated.
Thanks
Code:
Public Function CheckFrontEnd() As Integer
' ************************************************************
' Created by : Scott L Prince
' Parameters : None
' Result : Determines if backend can be reached, and if front end is the current version.
' Returns : 0 - Misc Error
' : 1 - No current version found in Version Manager file
' : 2 - Front end being run from master location
' : 3 - Master file path not found in Version Manager file
' : 999 - Front end current
' Date : 5-30-14
' Remarks : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes :
' ************************************************************
Dim FrontEndVersion As String 'Front end version number
Dim MasterVersion As String 'Master version number
Dim MasterPath As String 'Location of the master FE file
Dim BatchPath As String 'Location of the batch file that does the actual update
'Determine master version number.
MasterVersion = DLookup("fe_version_number", "tbl-version_fe_master")
'Determine if the database containing the version information can be accessed.
Select Case MasterVersion
Case "" 'Master version number cannot be found, or backend/version manager is missing.
CheckFrontEnd = 1
Case Else 'Version data found.
'Look up the path for the master file location.
MasterPath = DLookup("s_masterlocation", "tbl-version_master_location")
'Determine if the master file is being run rather than a local copy.
If Nz(MasterPath, "") = "" Then
'No master path was found. Return error value.
CheckFrontEnd = 3
ElseIf MasterPath = CurrentProject.Path Then
'The actual master file is the one being executed.
CheckFrontEnd = 2
Else
'Master file path found and is not being run. Determine the version number of the active front end.
FrontEndVersion = DLookup("fe_version_number", "tbl-fe_version")
'Compare the version number in the front end to the master version number.
Select Case (FrontEndVersion = MasterVersion)
Case True 'Version numbers match.
'Return "OKAY" result.
CheckFrontEnd = 999
Case False 'Version numbers do not match.
'Create the path for the batch file used to update the front end.
BatchPath = CurrentProject.Path & "\UpdateDbFE.cmd"
'Check for an already-existing BatchPath, and kill it if it exists.
If Dir(BatchPath) <> "" Then Kill BatchPath
'Notify the user that the application will update.
MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
"Your program is not the latest version." & vbCrLf & vbCrLf & _
"The front-end needs to be updated. The program will now close and then should reopen automatically.", _
vbCritical
'Execute 'UpdateFrontEnd'.
UpdateFrontEnd CurrentProject.Path & "\" & CurrentProject.Name, MasterPath
End Select
End If
End Select
End Function
Private Sub UpdateFrontEnd(ByVal LocalFilePath As String, _
ByVal MasterFileFolder As String)
Dim BatchFile As String
Dim MasterFilePath As String
Dim Restart As String
'Set the file name and location for the file to copy
MasterFilePath = MasterFileFolder & "\" & CurrentProject.Name
'Set the file name of the batch file to create
BatchFile = CurrentProject.Path & "\UpdateDbFE.cmd"
'Set the restart file name
Restart = """" & LocalFilePath & """"
'Create the batch file
Open BatchFile For Output As #1
Print #1, "@Echo Off"
Print #1, "ECHO Deleting old file..."
Print #1, ""
Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
Print #1, ""
Print #1, "Del """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Copying new file..."
Print #1, "Copy /Y """ & MasterFilePath & """ """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Starting Microsoft Access..."
Print #1, "START /I " & """MSAccess.exe"" " & Restart
Close #1
'Run the batch file
Shell BatchFile
'Close the current application so batch file can execute.
DoCmd.Quit
End Sub
Yeah, as I understand it, the 3 table version I quoted was what was in the original. It was also originally set up to use a completely separate Version database file containing both the current version number and the master file location.
Were I to update my code, I'd probably change the names to make them WAY more legible and move the 'master file location' data to the front end somewhere. (I don't, as a rule, like hard-coding that kind of information.)
Also, I had hoped the OP would reply again before lunch.
That remark was addressed to the OP not yourself.
I was unsure if he had copied the instructions correctly, so just raised it as a comment.
Whilst I could amend it now, as I understand the code now, I left it as is when I inserted it, and only added a form to update the version more easily and create the accde.
I have had to make small changes for bug fixes or an enhancement and this makes life so much easier.
Ah, I thought you were pointing out a possible improvement, and were I actually using that code here (we went the batch file route here), I have all SORTS of things I might tweak in it.
I also use this code for updating the frontends in my office. It works very well, but I would recommend making one modification.
Currently the update process runs if the Frontend version is not equal to the master version. It would be better to run it only when the Frontend version is smaller then the master version.
I had one case where my development Frontend got wiped, because it was newer then the master frontend. I was accidentally linked to an older development backend. Meaning all my new code was gone...
So I would recommend to trigger the update only when the frontend version is actually smaller then the master frontend.
This bit:
Select Case (FrontEndVersion = MasterVersion)
Actually, the last time I used that code, I had a part of the startup routine that bypassed this procedure altogether if it was the development version running.
