How to Continuously Develop and Deploy an Access 2010 Database Application

Question

I've been developing an Access 2010 Database Application, let's call it Skill.accdb. I'm at the stage where I want to start deploying it so users can start using it and continue developing at the same time. I've been reading

• MSAccess-Deployment-Best-Practices,
• Deploy-an-Access-2007-application, and
• Ways-to-share-an-Access-database

which talk about splitting, compiling, installing, etc., but they're very short on actual procedural steps to follow.

I have split my database application into a back-end and a front-end: Skill_Back.accdb and Skill_Front.accdb. The back-end is on a network shared drive and some users have copies of the front-end. So far so good, especially if I never have to do any more development. But I can't quite see how to now continue development on the front-end without disturbing the "live" back-end.

How do I have a development version of the front-end pointing to a dummy version of the back-end so that I don't mess up the live version? And then, when I wish to release a new version of the front-end, how do I get it to point again to the live version of the back-end?

If I re-split (ie, use the Move Data | Access Database tool again) to point to a not-yet-existing dummy back-end, I'm told all the tables are now empty -- not what I want. If I re-split to point to a copy of the live back-end, I'm asked if I want to replace it -- also not what I want. If I do, again I'm told "There are no tables in this database. The back-end database will be empty." and yet I see that it (the front-end) is still pointing to the live back-end!

What critical concept/tool/procedure am I missing? (I'm not asking about compiling or runtime or building an installer -- those are later problems.)

addendum

Further to following the advice in the fine answer below, I found a couple of other articles that do describe procedures:

• MS Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability
• Splitting your Access database into application and data

Catalase

Answer 1

This isn't a website, or a file; it's a database. You can't update every copy of your front end on each user's computer, it just doesn't work that way.

A strategy I use is to "version" my front-ends, and when I deal with databases I don't uses Access' built-in Move Data tool.

How do I have a development version of the front-end pointing to a dummy version of the back-end so that I don't mess up the live version?

You need (at minimum) 2 different environments

1. Production (what your users use)
2. Testing (where you make your changes and test them)

For Testing, you need your own back-end and front-end. This is where you will do your continuous development. You never want to disturb what your users are working with, especially the data.

For production, you can make your changes and roll out the new version via shared network drive where the users can download the copy, much like the first one. This is where you'll use the Linked Table Manager to reconnect the tables to your production back-end and your new front-end.

So the work flow would be:

1. You have a production back end and front end the users are using.
2. You have a copy of both of those databases to work with, and it's not related to what they are using.
3. You link your Front End tables to your copied development version of the back-end via Linked Table Manager.
4. You change/make updates/continue to develop on your front end.
5. You test them/ensure everything works
6. You place your new production front end version (v2) on the shared network drive and connect it to the production back end
7. Your users download (copy) the new front end version and use it.
8. They see your changes and are amazed by what you did and you get a $5,000.00 raise for knowing what to do and being a great developer.

Linked Table Manager

In the Navigation Pane, right-click one of the linked tables to open the shortcut menu. From there, choose Linked Table Manager.

That makes it easy to manage the links. Put a checkmark in the box next to each linked table you want to change. Or if changing them all, click Select ALL. Also check the box labeled "Always prompt for new location" (at the lower left corner of that dialog window). Then Access will ask you to locate the database source for the links.

If you need more info just say something.

Linking New Tables from Back End to Front End

If I have a back-end database with tables and I wanted to link those tables to a front-end on my desktop, I would follow this process:

1. Click on the External Data tab at the top of the Access ribbon
2. Click on Access
3. Select the correct File name/directory for your back-end
4. Select Link to the data source by creating a linked table.
5. Select the tables you want to import (probably can click Select All)
6. Click "OK".

Now you will see the tables in your front end. You can then use the Linked Table Manager to manage these links should, for example, your back end database be moved to a different location.

Misguided

It all seems so obvious new -- it was definitely a case of what/where is the missing Linked Table Manger! – Catalase

I'm a little confused: What you describe under "Linked Table Manager (extended)" seems to be an alternative way of linking tables (quite different from the LTM). Not sure of the advantage to this one over LTM -- maybe it allows links to a greater range of different DBMS tables? – Catalase

Linked Table Manager is Linked Table Manager. Sorry for the ambiguity - what can I clear up? There is nothing different. – Misguided

What you're describing (under 'Extended') is External Data | Access (aka Import Access database). It's a different icon/tool from External Data | Linked Table Manager. – Catalase

The External Data route @MarkC mentions is essentially how you get your front end database to link to the back end database's tables in the first place.. i.e. if you didn't want to use the split database wizard, you'd set up your tables in a back end database then create a front end database and then in the front end database you'd link to your back end tables via External Data > Access. LTM is then how you re-establish these links should your back end database be moved to a different address. – Aegyptus

Thanks for the clarification, @MattHall. I wonder if that info should be edited into the answer body, for all to benefit... – Catalase

@MartinF Have suggested an edit; hopefully it will pass review :) – Aegyptus

What if you need to change the back-end? Let's say you start with a fresh copy of the prod in to dev. You do your development but by the time you are done working, the prod back-end has changed! You can't just copy your dev back-end out to prod! You have to go do the exact same changes to prod and hope you don't F it up. – Overspread

Answer 2

I would suggest this:

http://www.databasejournal.com/features/msaccess/article.php/3286111/Automatically-Deploy-a-New-Access-Client.htm

The article references Access 2003, but I have used it for Access 2010 databases. Essentially, create a 2010 compiled front-end and upon start-up, it checks if the client version matches the server version. If they are different, the new server version is copied over to the client's machine. It's pretty slick.

Machinegun

Please add any relevant part of this link to your answer, remembering a link can go broken and/or outdated – Afloat

Answer 3

I know this is a very old question, but I recently used a super simple technique that can be of use to others.

For deployment, I put the front-end (FE) database (can be MDE, MDB or accdb, works with any type) in a shared folder, along with a small batch file wich copies the FE to the user's %TEMP% folder.
Then I deploy only the .bat file on each user's desktop.
This way, every time a user starts the app, a fresh copy of the FE is copied locally and started.
For upgrades, I only have to put the new FE in the shared folder. This also removes the need for regularly compacting of the FE!

Hillell