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

[Charlotte Foust]

Arthur,

Most, if not all of the databases I work on are too small to need a "real
server".   And the bulk of them are user-built, not developer designed.
Like you, I build databases that could be ported to a server fairly easily,
but I don't assume they're going to go there.  I'm under contract to a
state department that has thousands of databases littering the landscape.
While some of them are headed towards a shared SQL back end, most aren't.
Would I prefer to work with a server back end?  Sure, but that decision
often rests with the IT department Server people, not with the developer.

Charlotte Foust
(916) 206-4336

On Fri, Sep 23, 2016 at 8:42 AM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> 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
> ​
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>