Thinking of Access/jet to SQLServer or MySQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Baker - 16 Nov 2005 00:15 GMT

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 MS SQL Server
bias here in this forum ;-).

Thankyou for your thoughts
Ian

Reply to this Message

Sylvain Lafontaine - 16 Nov 2005 03:07 GMT

SQL-Server Express 2005 is free and its capacity if slightly superior to
Access. By using linked tables and adding the keyword dbSeeChanges here and
there (Access will tell you conveniently where to do it by rising an error
message when it will be missing).

Quite probably, things will go a little slower than with using Access as the
backend but by using Views here and there where you have jointed tables, you
will greatly enhance the speed. See
http://support.microsoft.com/kb/q209123/ for having updatable views.

For MySQL, I don't know but you shouldn't see any big difference between
SQL-Server and MySQL because you will use linked tables. For about Views, I
don't know.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> 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
[quoted text clipped - 28 lines]
> Thankyou for your thoughts
> Ian

Reply to this Message

Sylvain Lafontaine - 16 Nov 2005 04:16 GMT

Sorry, a sentence was missing in my previous post:

SQL-Server Express 2005 is free and its capacity if slightly superior to
Access. By using linked tables and adding the keyword dbSeeChanges here and
there (Access will tell you conveniently where to do it by rising an error
message when it will be missing), you can do the job in a few hours with a
near zero learning curve.

Quite probably, things will go a little slower than with using Access as the
backend but by using Views here and there where you have jointed tables, you
will greatly enhance the speed. See
http://support.microsoft.com/kb/q209123/ for having updatable views.

For MySQL, I don't know but you shouldn't see any big difference between
SQL-Server and MySQL because you will use linked tables. For about Views, I
don't know if there will be a difference between these two when using Access
as the FE.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> SQL-Server Express 2005 is free and its capacity if slightly superior to
> Access. By using linked tables and adding the keyword dbSeeChanges here
[quoted text clipped - 42 lines]
>> Thankyou for your thoughts
>> Ian

Reply to this Message

aaron.kempf@gmail.com - 16 Nov 2005 23:14 GMT

you know; you could use an Access Data Project against MSDE or SQL
express also.

i mean-- that's the newsgroup you're in.

And these MDB sissies just walk all over mdb-folk

Access Data Projects. It is an awesome way to make Access scale and
it's a lot easier to deal with LONGTERM than mdb hell

Reply to this Message