Solved Running a split database using the MS free Access Runtime software (1 Viewer)

[PaquettePaul]

Hopefully, I will get my thoughts down correctly.

I have a database that will be placed in a multi-user environment. So, best practice is to split the database into software and database files. Then the software version gets saved as an executable/compiled version so that end users cannot use or change the software. The database file gets saved on a server and the executable get stored on the client workstations linked in the office network. This approach also allows new versions of the software to be passed out without impacting the actual database (unless the database structure itself is also being modified).

My understanding is that when the user starts their executable, they can select/connect to the actual database files on the server using a configuration setting.

The problem seems to be that the site where the application is being stored will only be using the free runtime version of access rather than a developer version. From our initial look at this, the ability to link the executable access file to the database file is only available with a developer version of Access.

First question is am I correct in that I cannot link a split database that resides on different computers if i only have the runtime version installed?

Second, if I am wrong, do you have any instructions or reading material that I need to look at to resolve my dilemma?

As always, help is very much appreciated.

 

[CJ_London]

First question is am I correct in that I cannot link a split database that resides on different computers if i only have the runtime version installed?

you can using vba. Plenty of examples about - here are a couple

Connecting to access backend from access 2013 frontend using VBA

I have 5-6 clients to whom I will be selling my Access based Product. Where there is Back-end Database file (Password Protected). Access accdr Front-end file which saves data in back-end file. The

stackoverflow.com

Automatically relink Microsoft Access tables | Microsoft 365 Blog

Automating the relinking of tables just makes good sense. This is especially true if you are distributing your database to others. It can save you a lot of headaches, trouble, travel, and time. You do not have to be physically present, nor do you need to remotely access another computer...

www.microsoft.com

They probably will need some modification to meet your specific requirements.

just google something like 'access vba link to back end file' to find more

Assuming all users are link to the same BE then if you link using a UNC path rather than mapped drives, them your FE shouldn't need to be relinked anyway

 

[PaquettePaul]

Thanks. I will try these out

 

[Pat Hartman]

executable/compiled

Do not confuse the Access .accde file with an executable. It is not even close. Best to refer to the two files as FE (for the application objects) and BE (for the table objects). If you call the FE the "executable", you will begin to think of it as such (language is very powerful) and it is most certainly not an executable. In fact, if you have the full version of Access, you can open the app and modify tables, queries, and macros doing considerable damage. I also suggest renaming the .accde or .accdb to .accdr as an extra perecaution. This trick tells Access to pretend to be the runtime so even if a user happens to have a retail copy of Access, Access will pretend to be the runtime. In my example below, I distribute an .accdb. This is an internal app and the users are trustworthy so I didn't remove all the menu options which happens when you distribute using the Runtime. Renamning your .accdb to .accdr is an easy way to test how the app will work using the runtime.

If you want to go further, and I normally do, I keep two version tables. One in the FE and one in the BE. When I distribute a new version. the app compares them when it opens and if the two are not equal, then it displays a message and refuses to open. This solves the problem of them accidentally opening an old local version of the FE by bypassing the shortcut.

The issue you didn't discuss and so I'm worried about how you handled this is how you are distributing the FE. There should be a master copy of the FE stored on the server. You then have two options for the distribution process. You can use one of the installer databases you will find here or you can use a batch file with four instructions. I don't even add error trapping since any failure will require intervention by IT.

This is the batch file I use. You could create a script if you prefer.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

It creates a local folder. No error is raised if the folder already exists so the file can be used both for first time users and existing users.
Then it deletes the current FE. Again, no error is raised if the file does not exist.
Then it copies the FE from the master folder to the local folder. This could error if the FE does not exist in the master folder.
And finally, it opens the local copy of the FE. This could error if there are reference errors or some other issue.

So, the first two, don't cause errors. And the last two are very unlikely to occur once you have installed at least once for each user.

Then the user is sent a shortcut to place on their desktop. The shortcut runs the batch file which I also leave in the same directory as the master copy of the FE on the server. That way, If I ever want to change the batch file, I can change it on the server without having to change local copies on user PCs.

 

[PaquettePaul]

Hmmm. I thought that if I save the database as accde (file saved into an executable only file) then no one would be able to view the code. Is this not correct.

Delivery of new versions will be somewhat remote with installation instructions. I am developing the app in Pennsylvania and send it to my older brother in Ontario, who does the testing and acts as user rep, and when he/we are satisfied, the database will be stored as an accde and split, then sent on to my other brother in Alberta. Once in Alberta, he will store the two files on his office workstation. The brother in Ontario has remote access to the computer in Alberta and may have to do the install and configuration. Brother in Alberta is a great flyer/instructor but not computer savy.

It is expected that other instructors will access the application, although hopefully through the office computer rather than their own. I agree that there is a possibility of someone mucking about with the database or deleting the files but with daily backups, the damage should be minimal (maybe six flights a day with paper backup). Currently everything is done through a spreadsheet (yikes) But the needs have outgrown the spreadsheet capability.

By going with accde, I was hoping to at least protect the software rights by making the code inaccessible. Is this the correct approach?


I will send your comments about the batch file installation to my brother in Ontario.

 

[CJ_London]

I thought that if I save the database as accde (file saved into an executable only file) then no one would be able to view the code.

correct - you cannot view the code in .accde. It's just not the same a an executable file (.exe) which a) is fully compiled and b) does not require a 'host' application - such as access.

With regards runtime - if you are expecting some users to install runtime rather than a full version of access (or you supply as .accdr) then it is important then you are aware that users do not have access to the navigation window or full ribbon (which is good,) and perhaps more importantly shortcut menus (i.e. on right click) are disabled. If you need them, you need to write your own

 

[PaquettePaul]

Btw, I just tried out the first sample code to reselect a backend database and it worked great. The only thing I changed was to get the user to select the backend database first using a file finder dialog.
you guys are really helpful.

 

[PaquettePaul]

Yes, the reconnect to database function is password protected in the code as there is no database.

 

[Pat Hartman]

then no one would be able to view the code. Is this not correct.

I said that in an accde, if you have the full version of Access, you can modify queries, macros, and tables, NOT code. As part of the compile process, all the source code is removed from the database and only p-code remains. As it happens, people have developed un-compilers and they can convert the p-code back into source code although the variable names might be nonsensical.

Running with the runtime removes certain functionality from the database so you have to make sure that you use menus/forms/reports as the only interaction with the user. You will also have to provide certain menu options like the ability to print a report.

You can simulate the effect of running with the runtime by renaming your .accde or .accdb as .accdr The "r" tells Access to pretend to be the runtime engine and so lets you see what the users will see once the app is distributed.

@isladogs has written extensively on securing a database so you might want to look up some of his threads on the topic if you are worried about people breaking into the database. Just remember, NOTHING is perfect and Access databases can always be cracked.

An .accde is NOT an executable. An executable file with the .exe extension is run by the operating system. The .accde still needs Access to be installed in order to run. I hate to be pedantic about this but words have meaning and you can get yourself into trouble by not understanding the difference between an .exe and an .accde

Access files can never become an .exe because an .exe is not changeable once it is created. Access files are containers and one of the things they contain is tables which grow and shrink. So, for Access files to become executables, Access would need to stop being Access.

 

[gemma-the-husky]

Hmmm. I thought that if I save the database as accde (file saved into an executable only file) then no one would be able to view the code. Is this not correct.

Delivery of new versions will be somewhat remote with installation instructions. I am developing the app in Pennsylvania and send it to my older brother in Ontario, who does the testing and acts as user rep, and when he/we are satisfied, the database will be stored as an accde and split, then sent on to my other brother in Alberta. Once in Alberta, he will store the two files on his office workstation. The brother in Ontario has remote access to the computer in Alberta and may have to do the install and configuration. Brother in Alberta is a great flyer/instructor but not computer savy.

It is expected that other instructors will access the application, although hopefully through the office computer rather than their own. I agree that there is a possibility of someone mucking about with the database or deleting the files but with daily backups, the damage should be minimal (maybe six flights a day with paper backup). Currently everything is done through a spreadsheet (yikes) But the needs have outgrown the spreadsheet capability.

By going with accde, I was hoping to at least protect the software rights by making the code inaccessible. Is this the correct approach?


I will send your comments about the batch file installation to my brother in Ontario.

The source code is removed, and the application is turned into a pseudo-executable. It's not quite the same as a .exe file though, as the executable access mde/accde still needs the access environment shell, if you will.

It ought to be pointed out also that the mde/accde is by no means fully secure. Have a look at the demo on everything access for example. I use code from that site to remove the information that can be used to actually reconstruct your original source code.

@Pat Hartman just mentioned this last point. It's certainly worth using something that obfuscates attempts to reverse engineer your code.

 

[Pat Hartman]

The source code is removed, and the application is turned into an executable.

The .accde is NOT an executable. Please don't call it that. That only confuses people into thinking the .accde is something it is not. And as I carefully explained, the .accde is not even close to being an executable.

 

[gemma-the-husky]

The .accde is NOT an executable. Please don't call it that. That only confuses people into thinking the .accde is something it is not. And as I carefully explained, the .accde is not even close to being an executable.

I changed the description to pseudo-executable, although I had previously qualified the description anyway.

 

[Pat Hartman]

When it comes to programming, "executable" has a very specific meaning. It means a file that is immutable and which can be executed without a middleman by the operating system.

Compiled code that must be interpreted using some other program is not executable. A file that can be modified is not immutable. I'm done, you can call the .accde whatever you want to. But using "executable" in the name misleads people who don't actually understand what "executable" means. I wouldn't be so pedantic about this if the .accde prevented ALL updates but it does not. Users with a full version of Access can modify queries/macros/tables and calling this file "executable" gives people a false sense of security because the .accde is absolutely NOT immutable. The only thing not updateable are objects containing code.