473,726 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Thinking of Access/jet to SQLServer or MySQL

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
Nov 23 '05 #1
2 2248
Well, of course, I can only tell you from the MySQL point of view - as you
expected. Just a few thoughts about it.
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.


The commercial license for MySQL, as far as I know (but please check it back
to be sure) costs about 500 dollars or 500 euros (I'm not sure, but the
difference is not that big ;-)) per server. That's probably something where
SQL server can't compete.

MySQL 5 (I highly recommand you to choose version 5, if you go for MySQL) is
very conform to the SQL:2003 standard, so whatever your developers have to
learn about MySQL does also apply in a high degree to other RDBMS. At least,
it won't be harder to learn MySQL from scratch than SQL server. For support,
take a look at http://www.mysql.com/network/ - for 245 euros/server & year
you get support and a lot of additional services compared to the community
version (that most people use). I'm not sure, but maybe it even includes a
commercial license - if that's the case you'll certainly be better of with
MySQL Network.

Performance is definetly no problem with MySQL. It's of course important to
choose the correct database design, but this applies to any database system
;-). Just want to say, if you might hear critisism that MySQL offers bad
performance (maybe some hard core SQL Server freaks could come up with this
lousy argument) - that can only be a result of bad database design or an
incredible weak hardware equipment (in which cases all the other RDBMS also
would fail). Here's also an interesting link to read:
http://www.linux-mag.com/features/monty.html

Another argument for MySQL is that it's plattform independent. Should you
ever choose to use a Unix/Linux server (or most other operating systems),
you'll have no problem at all.

There's one argument for SQL Server (in comparison to MySQL) that comes to
my mind - SQL Server still has some features that MySQL doesn't have
(although much less since MySQL 5 is out). But I (my personal opinion - of
course you'd probably hear different opinions in a SQL server newsgroup ;-))
would say, unless you really absolutely need a feature that SQL Server
provides and you can't live without it or create a decent workaround, I
would choose MySQL rather than SQL Server. If you're missing an important
feature in MySQL, you could also check out PostgreSQL
(http://www.postgresql.org), which is another very good Open Source database
server.

Markus
Nov 23 '05 #2
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" <so*****@micros oft.com> wrote in message
news:43******** *************** @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

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
389
by: Stormblade | last post by:
Hey all, I have a web app that uses SQLServer 2000. I am switching to MySQL 4. 1.1. I have re-created all the tables but I'm running into 2 problems. 1. In SQLServer I can create a date field and as the default value I
0
1431
by: Paradigm | last post by:
I am using Access 2K as a front end to MySQL running on a Linux server. I am having trouble connectiong to the server. MySQL control centre connects and I can connect using a DSN data source. But if I connect using the following code Dim constr As String Dim tbl As DAO.TableDef constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" & SQLServerUser _
2
1316
by: Ray | last post by:
Hi folks, I have a database that goes to a client for evaluation/purchase as an Access2k2FE / Access2k2 BE. When they decide to upsize to MySQL or SQL Server, I would like an easy way to allow them to switch table sources. When I try to use Linked Table Manager, and the Find a File dialog pops up, it only gives me the option to look for .mdb or *.*. I would have thought it could allow me to choose an ODBC data source, but it
6
2287
by: Paradigm | last post by:
I am using Access 2K as a front end to MySQL running on a Linux server. I am having trouble connectiong to the server. MySQL control centre connects and I can connect using a DSN data source. But if I connect using the following code Dim constr As String Dim tbl As DAO.TableDef constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" & SQLServerUser _
1
3343
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ... www.soft30.com/download-1-11975.htm - 31k - Cached - Similar pages MDBSecure 1.0.8.0 - Soft30.com Utility which makes it easy to create secure MS Access Databases, ... MS Access 2000/2003 format. 30 day money back guarantee, 30 day trial. ...
1
1928
by: Rosny | last post by:
From the article http://software.newsforge.com/software/05/01/27/170244.shtml?tid=132&tid=75&tid=131&tid=13 "Flickr runs on MySQL, the most popular open source database, and it has from the beginning. Today Flickr has around a quarter of a million users and serves around 5,000 pages per minute, generating about 100,000 database queries. " Now if we run above scenario in Windows 2003 Web edition and SQLServer 2000 on Windows 2003...
26
2384
by: codercode | last post by:
I'm working on a Visual Basic .NET using Access database. However, my client already have a 30MB database with Sybase ASA and Sybase is way too much for that. Is there anyway I can migrate from a Sybase to Access? or is it possible at all. If it is not, can these two databases co-exist together?
22
5813
by: teejayem | last post by:
Hi, I am new to programming with databases and was wanting some help. Is there any way to password protect an access database and access sent sql commands to it via vb.net code? Any help would be much appreciated. Thanks in advanced.
0
1093
by: thatsrohit | last post by:
Hi , I need One Help I have one database(bibleGallery) which is in sql server2000. with eight tables 1) tblArtists, 2) tblBasePrice, 3) tblcategories, 4) tblPricegroup, 5) tblProducts,
0
8890
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9402
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9182
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8101
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2157
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.