ADE or MDE: Does this sound logical?

[Mark]

Hi

Sorry, this is all a bit "after the fact", but I'm interested in oppinions
please as I may adopt this approach again:

I recently developed an application for a client which has to run on a very
slow and unreliable network. Replication is not on the menu for various
reasons, MSDE backend and the front-end needed to be MS Access (XP) as they
are already familiar with VBA etc.

I decided that instead of using bound forms and an ADP that I would use an
MDE instead. This is a rough description of what I did:

- Front-end MDE holds identical tables to the backend (mostly)

- When a record is opened for editing, it gets flagged as locked (date, time
and user ID) and copied to the relevant front-end table(s) or just held in
type variables

- All data is accessed entirely though ADO and stored procedures (there are
no user permissions on tables and no views)

- Front end forms are either bound to front-end tables (for more demanding
screens containing subforms etc.) or the form is unbound and populated
during form load (for simple one record screens).

- Unless loading a list, only the selected record (i.e. the one being edited
or viewed) is retrieved from the server

- As soon as a field is edited the server is updated too (there is a stored
procedure for every possible update with no dynamic SQL)

- Each user has a local MDE which is opened exclusively

- All connections are dropped immediately data is retrieved / updated etc.

- No locking on any front-end tables

- The most amount of data a user could lose (not taking into account the
actual backend getting destroyed) would be the current field

Writing all the sprocs was a pain but the end result is a blindingly fast
application. I now see Access as a front-end in an entirely different light.
As each MDE is opened exclusively, is on a local drive and there are no open
links everything seems to happen in the blink of an eye.

Apart from developement time increasing (due to increased coding
requirements for data manipulation, locking and unlocking) does anyone have
anything else to add to the mix, or reasons as to why this may be a bad
approach.

Thanks in advance

Mark

 

[Sylvain Lafontaine]

The weaknesses of using either a MDB file with linked tables or an ADP
project for accessing a remote SQL-Server are well known and by keeping
yourself away from their « automated access features » you didn't fall in
any of these pitfalls.

However, if all you want from Access is only to use its GUI interface as a
« dumb client », I don't see why you should keep it instead of switching to
the .NET Framework; which offers a much more powerful environment for the
guy who want to do it all by himself.

For the beginner, using Access for its relatively simple GUI interface might
seem to be a good idea; however, simple tasks like having unbound controls
on continuous forms or manipulating image without blowing the memory show
its shortcomings.

 

[Mark]

Cheers Sylvain

I totally agree about using the .NET environment. Maybe the next step, for
now the only option was Access.

Have a good weekend

Mark