Supporting a distributed runtime database application.

[Gaz]

I have developed an Access 2003 database which is used by a small company.
The MDE file together with a folder of supporting files (Excel templates) is
kept on their FTP server and downloaded as needed. As there are only 2 users,
they can easily keep track of the master version. They can also backup the
database whenever they choose to. From time to time I need to upgrade, which
I do manually by importing all tables from the active MDE into my new Access
application, then replaceing the old MDE with the new version.

I now need to make the application available to more users, including other
organisations who may not have Access installed. I have purchased VSTO2003
for the Access runtime distribution license and I intend to package my
application with its supporting files. My concerns are:
1. How will my users backup their databases?
2. How will my users pass their databases to other users?
3. How will I provide upgrades without disturbing users' existing tables?
I am assuming, for the moment, that installations of the database will all
be on single-user PCs and not on multi-user servers.

I'd appreciate any advice, or suggestions for the best way to distribute the
application.

 

[Arvin Meyer [MVP]]

You should have split the database into a front-end and back-end with each
user having the front-end on their workstation, linked to the data on a
server. See:

http://www.granite.ab.ca/access/splitapp/overview.htm

Access 2003 has a backup procedure using the File menu, however it may not
be available in the runtime. If the database is split, the data backup is
done at the server and the front-ends do no need to be backed up since a new
is always available on the server.

If the database is properly built as a split application, there will be no
user tables. You can easily automatically replace front-ends using my
Starter database:

http://www.datastrat.com/Download/Starter.zip

or Tony Toews AutoFE updater:

http://www.granite.ab.ca/access/autofe.htm

 

[Douglas J. Steele]

The recommended approach is to split the database into a front-end
(containing the queries, forms, reports, macros and modules), linked to a
back-end (containing the tables and relationships). Only the back-end should
be on the server: each user should have his/her own copy of the front-end,
ideally on his/her hard drive.

To help ensure that each user always has the correct version of the
front-end, Tony Toews has a free Auto FE Updater you can download from
http://www.granite.ab.ca/access/autofe.htm

All that needs be backed up is the back-end. Since it's just a file, you
back it up like you'd back up any other file. It's important, though, that
no one be using the file while it's being backed up (otherwise the backup
could be inconsistent). Simply check whether or not the locking file (.LDB
file) exists in the same folder as the MDB file. (there's no point in making
the back-end an MDE)

I'm not sure what you're after with question 2 ("How will my users pass
their databases to other users?")

 

[Scott McDaniel]

I have developed an Access 2003 database which is used by a small company.
The MDE file together with a folder of supporting files (Excel templates) is
kept on their FTP server and downloaded as needed. As there are only 2 users,
they can easily keep track of the master version. They can also backup the
database whenever they choose to. From time to time I need to upgrade, which
I do manually by importing all tables from the active MDE into my new Access
application, then replaceing the old MDE with the new version.

I now need to make the application available to more users, including other
organisations who may not have Access installed. I have purchased VSTO2003
for the Access runtime distribution license and I intend to package my
application with its supporting files. My concerns are:

Sounds as if you've not split the database ... if that's the case, you really, really should do so now. Splitting makes
frontend updates (forms, reports, queries, and modules) much easier, and it also helps to protect your data a bit (since
a non-split Access file seems to be more likely to corrupt).

1. How will my users backup their databases?

If you split the database, then users can backup their databases with a 3rd party solution (i.e. just let their current
backup solution handle this), or you can build a routine that would back it up for them, at their request (this is most
often accomplished with the DAO.CompactDatabase method, since this will both compact AND make a copy of the database).
Note that you can't do this if your application is running and connected to the backend, so you might have to build a
small Access or VB project that does this, then shell out to that process from within your application.

2. How will my users pass their databases to other users?

With a split database, they'd just copy/paste or email their database to other users.

3. How will I provide upgrades without disturbing users' existing tables?

Again, with a split database, this won't be a problem

I am assuming, for the moment, that installations of the database will all
be on single-user PCs and not on multi-user servers.

I'd appreciate any advice, or suggestions for the best way to distribute the
application.

Be VERY careful when distributing the Runtime to users with existing versions of Access on their machines (earlier
versions included). As you might know, Access will "register" itself when it's used to run a database, and then the next
time a user opens a database, Access will use the currently registered version. If your users ALWAYS (a) open the
correct version of Access and then (b) open their database, you won't have a problem ... but depending on users to
follow a strict course of action will always get you into trouble <g>. Sagekey offers an installation utility that
alleviates this problem (www.sagekey.com) but it's not cheap ...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Gaz]

Thanks Arvin

But if the installations are on standalone PCs, should I still use a split
database? I have found in the past that it is not possible to link a FE to BE
with a relative link, so users would have to ensure that the BE on their PC
had the correct path.

 

[Douglas J. Steele]

I split even standalone PCs.

You can determine the current location of the front-end using
CurrentProject.Path. You can then use that to ensure that the front-end is
linked to a back-end in that same folder.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[George Nicholson]

Yes, you can/should still have a split db, expecially given your questions.

- Any relinking routine can be modified to look in a list of places (last
used loc per registry, the app directory, My Docs, etc.) for the BE before
it "fails". Once all other avenues have been tried you can prompt the user
to browse for a file (or not).

- re: your question about "sharing" dbs between users: they just pass around
their BE files. Not sure what they are going to do with them though. What
comes to mind is either complete dataset replacement (just relink the db) or
the importing of additional data (thinking recipes, collector's data, etc.).
The latter would require a whole selection interface (what data do you want
to import, etc..), but it would certainly be doable.

- re: providing future upgrades without disturbing existing data. *If* the
data structure changes, those changes can be made programatically to
existing tables whenever it encounters an "old" BE version, or the data
could be imported into "new" version tables. But after apps get to a
certain age modifications are more likely to be confined to just the FE
(i.e., additional functionality with existing data vs additional data
requirements).

HTH,

 

[Arvin Meyer [MVP]]

The advantage to a split database, even on a standalone PC, is that you can
update a front-end with ease without affecting data at all. That assumes of
course that the users aren't writing any ad hoc queries or reports. If they
are, they'll need to import them. For my power users that do write some of
their own work, I set them up an archive folder where they can move their
existing front-end (back-end too, for local back-up) so they can import
their own objects into my new front-end.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com