[AccessD] Basic Question (Probably) that I just don't know

[Gustav Brock]

Hi Arthur

I can't add much to the comments already posted, except for one thing: SQLite is certainly not an alternative for an accdb or any other decent database as SQLite is severely limited regarding data types. In fact, I think why so many use it, is only because they just don't know better.

/gustav
________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Arthur Fuller <fuller.artful at gmail.com>
Sendt: 23. september 2016 17:42:30
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Basic Question (Probably) that I just don't know

I know it's not proper to answer a question with a question, unless a) you
are a detective interrogating a suspect; b) you are a lawyer embroiled in a
trial; or c) some other reason.

My questions are:

1. Does Access still support replication? The reason I ask this is because
many years ago I had enormous success with replication, and here is how I
set it up: each user-workstation had a copy of the FE and a replica of the
BE; synchronization was scheduled for every 15 minutes. The master replica
did a 2-way synchronization every 15 minutes with each local replica. This
eliminated trips to the server to read the BE, with the result that the app
was way faster than it was when I used the traditional Local-FE+Server-BE
model. The down-side was that 15-minute interval. IIRC, Access Replication
Manager wouldn't let me make the interval smaller, so at any given moment,
the local data might be 15 minutes our of synch, but if that event horizon
is acceptable (and also that Replication is still available in Access) then
that's a much better setup than the traditional one.

2. Why would a seasoned developer would choose an MDB or ACCDB back end
when so many actual database servers for free (SQL Express, MySQL, MariaDB,
PostGreSQL, SQLite... the list goes on), all of which totally sidestep the
Access locking issues, and all of which extend the power of the BE way, way
beyond what's available in the standard Access BE: stored procedures,
views, Compiled Table Expressions (CTEs) -- incidentally, these have just
become available in the latest release of MySQL
Let me also plug a firm with which I have no affiliation, called BullZip,
which makes utilities to port Access databases to any of the real server
databases mentioned above. These utilities are all free and ought to be in
your toolbelt; even if you don't have an immediate need for a port to MySQL
or MariaDB, as a learning experience, you might want to install said db
software + the appropriate BullZip utility, port your data, copy your FE
and redirect the copy to point at the new BE, then do a few benchmarks.
I've been then and done that and I have dramatic benchmarks to prove it,
such as a performance comparison with ~80 users hitting the BE
simultaneously -- and that's without converting all the queries to stored
procedures.

BullZip's Access to MySQL <http://www.bullzip.com/products/a2m/info.php>*
BullZip's Access to PostGreSQL
<http://www.bullzip.com/products/a2p/info.php>
BullZip;s Access to MS-SQL <http://www.bullzip.com/products/a2s/info.php>

* Since MariaDB is 100%+ compatible with MySQL, this utility will work with
both DB servers.

Each of these products works splendidly. I have tested all of them, and
ported numerous databases using them.

As mentioned above, a simple port of the data to a real DB server is in
itself a huge gain, but in reality is only a glimmer of what's possible
once you re-think your application.

Before doing a port, I have a couple of recommendations that will affect
your result significantly, The most important is to locate all the
RecordSources and RowSources in your FE that depend upon SELECT statements,
and to convert them all to Access Named Queries. (While easy, this process
may be time-consuming, proportionate to the number of SELECT
statements.) Why do this? Because then they become available, during the
port, for conversion into Views; from there, you can convert the
appropriate Views into Stored Procedures.

The easiest ways to locate all the statements that demand conversion are
Rick Fisher's *Find And Replace,* and *SpeedFerret*, both excellent Access
add-ins.

When developing database apps, I always plan for success. In practice, that
means that I anticipate significant growth in both the sales and the number
of users of the app. The only alternative, as I see it, is to plan for
subsistence, or for failure, neither of which is acceptable (it might be to
the client of interest, but it most certainly is not to me -- I want
results on the Bottom Line before I feel satisfied. I've worked for a bunch
of non-profits, in which the Bottom Line is a rather elastic measure, but
still calculable; for instance, I did an app called Volunteer Manager,
whose success is not measured in dollars but the increase in volunteers.

One last thought: the port from an Access BE to a genuine server DB
facilitates the move to web/mobile apps, and that is a rapidly-increasing,
even dominant market segment. So Access developers have to be planning an
Exit Strategy, since it is quite clear that MS has little or no interest in
providing one. But one step at a time: first, get the data out of Access
and into a serious DB server; second, look at the various alternatives for
delivering equivalent + enhanced apps for web/mobile deployment.

Just my perspective.

Arthur