MySQL Server Forum - Thinking of Access/jet to SQLServer or MySQL

Author

Thinking of Access/jet to SQLServer or MySQL

Ian Baker

2005-11-15, 8:23 pm

We have developed an Access/Jet database (2000, XP & 2003 versions) that has
been used by clients all around the world for several years and is extremely
robust with 55 tables, 172 hard stored queries (plus about the same in VBA
SQL code), 60 forms and about 18,000 lines of code. The biggest advantage
with Access is that it doesn't cost the client anything for if they don't
have Access already then we provide the Runtime version which makes our
application extremely cost effective for the client.

The most concurrent users recorded has been 23 without any problems and in 5
years we have only ever had 1 corruption when a server fell over.

We now need to expand the concurrent user abilities with the most being say
around 175 at the worst possible time with the objective being to allow
employees in an organisation to enter a record (for eg. logging an IT Help
Desk Call themselves rather then contacting the Help Desk and getting them
to log the call). We know that which ever way we go there will be a massive
learning curve and there WILL be an impact to the cost to the client as only
some clients have MS SQL Server.

Strategically we POSSIBLY may down the track be moving away from Access and
to ASP.NET as the front end (or both) but at the moment we believe we have 2
options - either MS SQL Server or MySQL for the backend. I must stress that
the most important factors are cost to the client, ease of self
installation, size of learning curve for us and the ease of future
enhancement development and support to the client.

We would very much like to hear from others on their thoughts on which way
we should go and why although I do assume that there may be a MySQL
bias here in this forum ;-).

Thankyou for your thoughts
Ian

Ron Hinds

2005-11-23, 7:23 am

We have already completed a project exactly like you describe. We chose SQL
Server as the backend for a number of reasons:

1.) Easy to install;
2.) Easy to maintain with a host of GUI tools for managing AND development;
3.) Microsoft provides support for SQL Server (you only have to support your
app);
4.) It works better with Access than anything else out there - in fact
Access is optimized to work with SQL Server and vice-versa.

I only recently started looking at MySQL for another purpose. While it seems
to be a decent database, I was astonished to learn that it didn't support
features like stored procedures, triggers and views (which are extremely
useful with Access Jet for performance reasons) until the most recent
version, which only came out in the last month or so. The MySQL ODBC driver
seems to be a bit buggy, too.

MSDE is a redistributable (minus the GUI tools) version of SQL Server that
is as full-featured as the other versions with only some throttling of the
engine after a certain number of connections, which makes it ideal for the
smaller installations as it is cost-free. I believe the Standard Edition can
easily handle the larger loads you are talking about paired with some decent
hardware. SQL Server also comes with a COM object interface (SQL-DMO
[Distributed Management Objects]) that is easily manipulated from Access VBA
code. We are able to do backup and restore from Access with only a few lines
of code.

I'm sure I'll be flamed by the MySQL faithful ;-) so I'll just repeat: I'm
not disrespecting MySQL - I definitely see the potential and usefullness of
it in certain circumstances, and we will, in fact, be using it. But IMHO it
just isn't as well suited to *this* particular purpose as SQL Server is.

"Ian Baker" <someone@microsoft.com> wrote in message
news:437a7b29$0$1075
4$afc38c87@news.optusnet.com.au...
> We have developed an Access/Jet database (2000, XP & 2003 versions) that
> has
> been used by clients all around the world for several years and is
> extremely
> robust with 55 tables, 172 hard stored queries (plus about the same in VBA
> SQL code), 60 forms and about 18,000 lines of code. The biggest advantage
> with Access is that it doesn't cost the client anything for if they don't
> have Access already then we provide the Runtime version which makes our
> application extremely cost effective for the client.
>
> The most concurrent users recorded has been 23 without any problems and in
> 5
> years we have only ever had 1 corruption when a server fell over.
>
> We now need to expand the concurrent user abilities with the most being
> say
> around 175 at the worst possible time with the objective being to allow
> employees in an organisation to enter a record (for eg. logging an IT Help
> Desk Call themselves rather then contacting the Help Desk and getting them
> to log the call). We know that which ever way we go there will be a
> massive
> learning curve and there WILL be an impact to the cost to the client as
> only
> some clients have MS SQL Server.
>
> Strategically we POSSIBLY may down the track be moving away from Access
> and
> to ASP.NET as the front end (or both) but at the moment we believe we have
> 2
> options - either MS SQL Server or MySQL for the backend. I must stress
> that
> the most important factors are cost to the client, ease of self
> installation, size of learning curve for us and the ease of future
> enhancement development and support to the client.
>
> We would very much like to hear from others on their thoughts on which way
> we should go and why although I do assume that there may be a MySQL
> bias here in this forum ;-).
>
> Thankyou for your thoughts
> Ian
>

Sponsored Links