Question To Deploy or not to Deploy (1 Viewer)

[Laurad]

Dear gods of the Database, forgive me for I have sinned.. (sort of). I have just read the "Ten Commandments of Access Database Programming" and I have failed on some, but not all, but will do my best to heed the commandments in future.

I have been working in a very small primary school for 14 years and as a favour many years ago began a small Access database to keep track of the pupils with particular interest in the ones with special needs. (The school is strong in terms of helping many such children.). The program has grown over the years and now the Special Education Needs department are very reliant on the database/software which I have developed slowly as needed over the years. I have been lax on things like splitting the database, which I will do now, but I would also like to lock it down so that only I can amend it. My Access skills are minimal.

I have been researching and have a number of questions I would love some advice on, that would not entail very complicated changes, if possible.

Originally, the database was written for one Teacher, but gradually more and more Special Needs teachers began to use it and they need to be able to access it from any one of about 40 computers as they move around the school.

Currently there are shortcuts on the computers to the one .mdb file on the server. It seems to work that multiple users can access the database and update as necessary (currently it’s data and code in the one .mdb file.

Firstly I need to upgrade to accdb.

Secondly, I should split the data and code. I am able to do that and have done so in the past and I am sure this will work well.

Thirdly, I would like to lock down the code/forms/queries so that only I can modify the software. I have read that the database can be "deployed" so that it can be installed on computers that do not have MS Access. I do not need this option as all of the computers in the school have MS Access, so I think I need to compile it. I read on the Microsoft support forum that creating an accde file would protect the code, but when I did this, I could still modify the code.

I followed instructions for compiling using some software called "inno setup" which I installed and which seemed to work and compiled. This created a file called also called .accde.

My questions are firstly, am I on the right track?

Secondly, with a front and back end, do I have to install the front end on every computer which is going to use the database? This could mean 40 computers. This means also that for every modification I have to update 40 computers with the new code. This is very labour intensive. Is there any reason why they can’t all log into the same front end which will be located on the server? There will never be more than about 5 people accessing the database at one time, but they do need the ability to do so from any one of the 40 computers.



I hope I have covered everything, apologies for length of post.


Thank you.
[FONT=&quot]Laura[/FONT]

 

[CJ_London]

I read on the Microsoft support forum that creating an accde file would protect the code, but when I did this, I could still modify the code.

this implies that you did not create a proper accde file. Compiling the code just does that and you do need to do it prior to creating the accde to ensure all the code is compilable. To create an accde it depends which version of access you are using but in 2010 you go to File>Save & Publish>Save Database as>Make accde - the original accdb file is retained.

do I have to install the front end on every computer which is going to use the database?

Yes

This is very labour intensive

You can automate the process

Is there any reason why they can’t all log into the same front end which will be located on the server?

Yes - bigger risk of corruption, poorer performance, increased risk of collisions - you may have been running it this way for some time without problems, but the higher risk remains.

 

[Laurad]

this implies that you did not create a proper accde file. Compiling the code just does that and you do need to do it prior to creating the accde to ensure all the code is compilable. To create an accde it depends which version of access you are using but in 2010 you go to File>Save & Publish>Save Database as>Make accde - the original accdb file is retained.

I originally wrote in Access 2003 and the file is still an .mdb file. I did convert it or "save as" accdb, then I had the option to "save as" accde which I did. I'm not sure what I did wrong why the code is then not hidden. There wasn't an option (I do not think) to "publish".

I downloaded a compiler called "Inno Setup Compiler" which compiled it and left it with the same name .accde and then the code was hidden. Did I need to do that or does Access 2013 compile?

You said I did have to install new code on every computer every time there was a modification and that I could automate the process - could you possibly explain how I might do that as it would be impossible for me to install on 40 computers every time I made a change.

Many thanks,
Laura
ETA - I have just run the Save As "Make ACCDE - File will be compiled into an executable only file" option after I "compiled" the code in the VB code area. Maybe this did the trick because the ACCDE file is now non editable which is what I was striving for. I wonder if the reason it did not get "locked" previously was because I did not run the "compile" to begin with.

 

[gemma-the-husky]

laura

out of interest, why exactly do you need to upgrade to accdb? mdbs/mdes seem to run fine in all later versions of access.

the problem with having data and code combined in a single database is that it makes delivering improvements very difficult. Splitting the two means that you can modify the code portion off site, and then replace the front end with a new improved version, without having any further issues. It also makes data backups easier.

Although everyone says you must not give multiple users access to the same database, that IS the way MS have enabled it, and depending on what you are doing it may be OK, although we prefer not to do this. Just make sure you have a decent data back up strategy, and have backup copies of the database front end.

Having the dbs installed on multiple pcs implies multiple purchases of access (although you can use the run time version) which may not be achievable in all organisations.


mde files/accde files

the idea is for you to develop a normal mdb/accdb (off site, if you like) - and then use the access facilities to produce a mde/accde. This compiles and removes ALL code from forms/reports and modules. These objects cannot be edited or amended by any user. Obviously you have now clarified the right way to do this

you cannot produce an mde/accde if there are any logic/compile errors in your code - although if there were you would not have ended up with an mde/accde. You do not need 3rd party tools. Access includes options to produce them. The exact location of the option (and so many other options) changes with each version though!

finally, you may have done this - but you ought to put option explicit at the top of every module, and also include good error handling, as otherwise errors will just crash the programme, as the database will not be able to enter debug mode.

Hope this helps.

 

[Laurad]

Hi Dave - thanks for your indepth reply.

I am not sure what "mdbs/mdes" files are. The database at school is still the .mdb format. In order to create the accde file I was informed by a message that I had to first save as accdb, then I could create the accde file. I am still working on a copy of the database at home while I learn - the original is still in the school and being used. I had not split it either, but will do so as I do understand the reasons and sense behind it.

All of the computers in the school have MS Access installed, so I do not need a runtime version.

Thanks for the advice about not being able to create accde file if there were any logic/compile errors. When I compiled the code, it compiled without any error messages and then I was able to create the accde file. That seemed to be the reason why it did not create a locked down version to begin with, because I had not compiled first. I am glad I do not need 3rd Party tools.

Thank you also for the advice about "option explicit" and the error handling, I have been lax on both but will go through the code and ensure it's more robust.

I am not going to be at the school too much in the near future and need to be able to supply them with updated code when required by them. I hope eventually to be able to email code to them, but will then have to teach them how to run the linked table manager, or can this be automated somehow also? I also need a way of creating an automated way for the code to be updated on every computer on which it is installed. Would this be a batch file on the server?

It all helps, thank you so much.

 

[CJ_London]

also need a way of creating an automated way for the code to be updated on every computer on which it is installed. Would this be a batch file on the server?

It could be a batch file on the server. However below is some code I wrote some time ago and could probably be updated but still works

The code does the following- 1) it checks the name of the frontend on the user machine against the copy on the server - if it is different it deletes the copy on the user machine and copies across the new version. 2) it sets trusted locations if not already done 3) it then opens the front end on the user machine and closes itself.

To use it, you need to create a new empty db and copy this code into a module.

Then create an autoexec macro which calls UserAppExists

There are the following assumptions which rely on consistence of naming your dbs and versioning.

So for example lets say your application is called 'SchoolApp', it will consist of 3 files

SchoolApp v2.1.accde (the front end)
SchoolApp Data.accdb (the back end)

and this new db which is called

SchoolApp.accde - tip, you don't need to make an accde, you can just rename it as .accde

And they all reside in the same folder on your server. The code looks at the name of the current app (SchoolApp.accde) and finds another file called SchoolApp??...??.accde. It won't find the backend because that is .accdb. WARNING - you must have a db called SchoolAppsomething.accde otherwise the routine will find the SchoolApp.accde and you will be in an endless loop - you could modify the code to prevent this happening

Each user is provided with a shortcut to SchoolApp.accde on the server - you can simply email this to them and they can put it where they like, typically desktop but could be mydocs or elsewhere

When they want to run the app, they just click on the shortcut.

The local front end is stored in the user profile localAppdata folder so hidden away from the user, although they could still find it if they wanted.

When you have a new version, simply place on the server with a new name e.g. SchoolApp v2.2.accde and delete the 'old' front end (I actually move it to an archive folder in case I need to restore it) . - the next time the user want to open the app, the routine will detect the version has changed and update the users front end with a new copy.

If for some reason, there is a problem and you need to revert to the previous version, you just replace the file with the previous version, the routine works off the fact the names are different, not that one 'is greater than' another.

One other thing you can do in your application is to put some code in an event regularly triggered by the user - perhaps the current event for the navigation form. Design the code to do a name comparison between the users front end copy and the one on the server (you can adapt some of the code below) and if it detects a change it can display a message saying something like 'there is an update available, please exit the application and log in again'.

Finally new users - simply send them a copy of the shortcut and it will do the install

Option Compare Database
Option Explicit
  
Public Function UserAppExists(Optional AppDir = "AoF") As Boolean
Dim WShell As Object
Dim FSO As Object
Dim UsrDB As String 'name of existing user front end in user application directory
Dim UsrPath As String 'required path to user directory where user front end is located
Dim DBPath As String 'path to application directory where master front end and this db are located
Dim DBName As String 'name of db without versioning
Dim VerName As String 'name of db with version
Dim UsrApp As Access.Application
     On Error GoTo RedirectionErr
    
    'get required path to user application directory
    Set WShell = CreateObject("WScript.Shell")
    UsrPath = WShell.expandenvironmentstrings("%LOCALAPPDATA%") & "\" & AppDir
    Set WShell = Nothing
    
    'get path to application directory
    DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
     'get application name without version
    DBName = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1)
     'get application name with version
    'note this assumes there is only the latest version in the directory
    'and previous versions have been deleted or archived
    VerName = Dir(DBPath & Replace(DBName, ".", "?*."))
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
     ' if user application directory does not exist, create it
    If Not FSO.FolderExists(UsrPath) Then FSO.CreateFolder UsrPath
    
    UsrPath = UsrPath & "\"
    
    ' if application does not exist or does not match the current version delete and copy
    If Not FSO.FileExists(UsrPath & VerName) Then
        
        'delete any existing versions of the front end in the user application directory
        FSO.DeleteFile Replace(UsrPath & DBName, ".accdb", "*.accdb")
        
        'copy the new version from the application directory to the user application directory
        FSO.CopyFile DBPath & VerName, UsrPath & VerName
        
    End If
  
     'Add trusted locations
    AddTrustedLocation UsrPath
    AddTrustedLocation DBPath
    
    'open the front end in the user application directory
    If VerName <> DBName Then Shell """" & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & """" & " " & """" & UsrPath & VerName & """", vbNormalFocus
    
    Set FSO = Nothing
    Application.Quit
    Exit Function
    
RedirectionErr:
    Select Case Err
        Case 52, 53: 'file not found, can be ignored
            Resume Next
        Case Else
            MsgBox "There is an untrapped error - Err " & Err & vbCrLf & Err.Description & vbCrLf & "Please report this error code and description to your administrator" & vbCrLf & "This application will now close", vbOKOnly, "REDIRECTION ERROR"
            Set FSO = Nothing
            Application.Quit
    End Select
    
End Function
  
 
Public Function AddTrustedLocation(strPath As String)
On Error GoTo err_proc
'WARNING: Ensure you have the rights to set registry keys in the registry
 Dim intLocns As Integer
Dim i As Integer
Dim intNotUsed As Integer
Dim strLnKey As String
Dim reg As Object
Dim strTitle As String
  
    strTitle = "Add Trusted Location"
    Set reg = CreateObject("wscript.shell")
    
    'Specify the registry trusted locations path for the version of Access used
    strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(Application.Version, "##,##0.0") & "\Access\Security\Trusted Locations\Location"
     On Error GoTo err_proc0
    
    'find top of range of trusted locations references in registry
    For i = 999 To 0 Step -1
        reg.RegRead strLnKey & i & "\Path"
        GoTo chckRegPths        'Reg.RegRead successful, location exists > check for path in all locations 0 - i.
 checknext:
    Next
    MsgBox "Unexpected Error - No Registry Locations found", vbExclamation
    GoTo exit_proc
 chckRegPths:
'Check if Currentdb path already a trusted location
'reg.RegRead fails before intlocns = i then the registry location is unused and
'will be used for new trusted location if path not already in registy
     On Error GoTo err_proc1:
    For intLocns = 1 To i
        reg.RegRead strLnKey & intLocns & "\Path"
        'If Path already in registry -> exit
        If InStr(1, reg.RegRead(strLnKey & intLocns & "\Path"), strPath) = 1 Then GoTo exit_proc
 NextLocn:
    Next
      
    If intLocns = 999 Then
        MsgBox "Location count exceeded - unable to write trusted location to registry", vbInformation, strTitle
        GoTo exit_proc
    End If
    'if no unused location found then set new location for path
    If intNotUsed = 0 Then intNotUsed = i + 1
      
    'Write Trusted Location regstry key to unused location in registry
    On Error GoTo err_proc:
    strLnKey = strLnKey & intNotUsed & "\"
    reg.RegWrite strLnKey & "AllowSubfolders", 1, "REG_DWORD"
    reg.RegWrite strLnKey & "Date", Now(), "REG_SZ"
    reg.RegWrite strLnKey & "Description", Application.CurrentProject.Name, "REG_SZ"
    reg.RegWrite strLnKey & "Path", strPath, "REG_SZ"
  
exit_proc:
    Set reg = Nothing
    Exit Function
  
err_proc0:
    Resume checknext
  
err_proc1:
    If intNotUsed = 0 Then intNotUsed = intLocns
    Resume NextLocn
 err_proc:
    MsgBox Err.Description, , strTitle
    Resume exit_proc
  
End Function

 

[Laurad]

It could be a batch file on the server. However below is some code I wrote some time ago and could probably be updated but still works

Thank you so much, a lot for me to take in and consider. I appreciate it very much.

 

[gemma-the-husky]

Hi Dave - thanks for your indepth reply.

I am not sure what "mdbs/mdes" files are. The database at school is still the .mdb format. In order to create the accde file I was informed by a message that I had to first save as accdb, then I could create the accde file. I am still working on a copy of the database at home while I learn - the original is still in the school and being used. I had not split it either, but will do so as I do understand the reasons and sense behind it.

All of the computers in the school have MS Access installed, so I do not need a runtime version.

Thanks for the advice about not being able to create accde file if there were any logic/compile errors. When I compiled the code, it compiled without any error messages and then I was able to create the accde file. That seemed to be the reason why it did not create a locked down version to begin with, because I had not compiled first. I am glad I do not need 3rd Party tools.

Thank you also for the advice about "option explicit" and the error handling, I have been lax on both but will go through the code and ensure it's more robust.

I am not going to be at the school too much in the near future and need to be able to supply them with updated code when required by them. I hope eventually to be able to email code to them, but will then have to teach them how to run the linked table manager, or can this be automated somehow also? I also need a way of creating an automated way for the code to be updated on every computer on which it is installed. Would this be a batch file on the server?

It all helps, thank you so much.

it was a plural

mdb's or mde's are, if you like, old style formats
accdb's or accde's are a newer style,.

but newer access versions still quite happily run mdb files.

I also continue to use mdb format, because I do not (think) I need anything that is available in accdb versions. I find it easier to develop in A2003, although A2010 and A2013 are not too bad. I didn't like A2007.

You really need to develop on the lowest level profile that your users have.

----
option explicit. In a code module, you have tools/options. one of which is "require variable declaration". This adds option explicit at the top of every new module What this does is ensures that any variable you use has to be declared first. This is a great help. It makes sure you think about the variable types, and prevents hard to find errors caused by typos in variables.

there are quite a few ways to resolve front end/back end linking in code, and to manage the installed version of your app. It's better to try and automate all this stuff than to need users to be proficient in using these access features. try our code samples, or just google them.

you ought to give some thought to support. It depends on your employment situation, but once an organisation becomes reliant on something, you need to decide how to address the problem if and when it goes wrong. I also assume the code you have developed does not belong to you, but belongs to your employer, although that also is not a "given"

This a legitimate reason why in-house IT departments are often averse to non-sanctioned access developments. Once they proliferate and even become mission-critical, someone needs to deal with support issues.

 

[CJ_London]

no problem

Forgot to say, the 'Optional AppDir = "AoF"' parameter in the query can be changed to 'Optional AppDir = "SchoolApp"' or similar - Aof is the name of the folder where the front end is stored in the localappdata folder. I tend to use something obscure just as another way to make it that little bit harder for users to find things they don't need to find!

 

[CJ_London]

This a legitimate reason why in-house IT departments are often averse to non-sanctioned access developments. Once they proliferate and even become mission-critical, someone needs to deal with support issues.

In my experience, IT departments are just not geared up to respond quickly and in depth to resolve ongoing requirements to meet changing situations - they are constrained by budgets, lack of personnel, other priorities and a set of business processes which can take months or even years to complete - admittedly all required to protect the business but prevents the business from responding to rapid change.

In addition, the people who require these changes are not necessarily able to clearly define their requirements, understand the implications, etc - but will 'know what they want when they see it'.

 

[The_Doc_Man]

I'm agreeing with Gemma (Dave) and CJ. The biggest reason that general IT departments don't like Access projects usually isn't because it is an Access project. They just don't have the manpower/resources to maintain what SHOULD be a separate formal project for the company or organization.

At least in our site, there is a huge difference between the IT group (mostly sys admins and db engineers for the big-boy databases) and the project people who are responsible for (sustainment of existing / creation of new) projects.

The biggest portion of this is the budgetary issue. IT departments don't have traditional developers - project teams do. IT departments don't do database schemas - project teams do. IT departments don't worry about deployment - (you guessed it... ) project teams do. Because each of those steps cost money in the form of salaries and benefits that probably isn't in the IT budget.

It may seem that I am going far afield here, but I'm going to a specific place. The solution (long-term) on your problem is to find the right person(s) who will declare your application to be mission-critical or at least mission-significant. Then you can talk about setting up a maintainer and doing a transfer of technology to that person. But you must be prepared to advise the powers-that-be about the dichotomy they face: Either (a) they find a way to fund a maintainer, even if only part-time, or (b) they face the very good odds that they will have to replace the entire support system and suddenly lose their support and reputation at the same time.

For what it is worth, this is the philosophical danger of using Access to develop some type of application that suddenly becomes important to the organization. If you had done it using ORACLE Server and ORACLE Web Tools, nobody would have questioned that this project would require formal support. But there is a mind-set that because Access comes in the same box with a word-processor and a spreadsheet manager, it must be trivial to develop something in it.

Back to the question of deployment of upgrades - CJ's option is one among many good ways you could find via the Google-brain search. If you have a split FE/BE situation, the FE can be distributed and the BE can (must) be shared - mostly due to file locking conflicts if the FE is NOT shared. I agree with the others that you don't need to move to .ACCDB files - an .MDB works find and can be compiled to an .MDE just fine, even on Ac2013, which I have on one of my sub-nets.

 

[spikepl]

Thx CJ & The_DOC_Man

I'm still collecting facts describing the advantages of Access over the traditional acquisition process, and you just gave me some more tidbits.

Access can be a fantastic means to bypass an obstinate IT-department (I have witnessed that myself) and sometimes the only way to satisfy needs that otherwise would get stuck or simply buried for ever in a traditional process.

 

[JLCantara]

Here is a very complex issue hiding behind a cute smile: a distributed, user friendly, application in a context that as no IT team...

1 - DO NOT UPGRADE to Office365.A2013: I did it 3 months ago and I regret it to tears: A2013 is a piece of shit. With A2013, all the things your talking about are gone! So stay with your version (A2010?).

2 - An Access APPLICATION (not a database) cannot be compiled since Access.exe produces P-Code (pseudo code). Thats why you need Access.exe or its RT version to execute an Access application: otherwise, it would, like VB, offer the .exe option.

3 - I had, at Domfer, a very similar site: 25 PCs, spread all over the plant (300,000 sq feet), were using an app controling all manufacturing operations (~1300 access objects). First, all PCs had a link to the server located app but I switched to local app (forgot the reason I switched).

4 - Updating local app (FE - front end) is no problem: app usage was cut from 12:15 till 12:30 while a scheduled batch file, installed on every PC, was doing the transfer of FE on a daily basis. Hence every one was up to date on a daily basis.

5 - It is not possible, as of today, to make secure A2013 app: I am working on it for the past 3 month and found yet no 99.9% solution. MS is making sure that any A2013 is non secure... and I don't know why. It is still possible with YOUR version.

6 - I don't know if you can use the workgroups utility, but if you can USE it: at Domfer I was using Aceess 97 with workgroups and the app was armored!!! Of course the shift login trick was neutralized... I was using group permissions not user; a personal procedure was handling the locks on every access object - because that's the way it has to be done!!!

7 - As you see, your not swimming in shallow water: don't be discouraged, this site will help you all the way through!

Good luck, JLCantara.