Is MySQL really better than MSAccess

Anon

I am not sure if this email is directed to the proper people or not, but in
any case it would be appreciated if a few questions could be answered.

I have been building a admin database with MS Access (MSjet) that is linked to my Intranet through ASPs that I built trough dreamweaver. I have the intent of upgrading it to MySQL or SQLServer if required. I have heard so many comments concerning MySQL and SQLServer that I have been considering converting over to it for a while.

My questions are s follows:

Can an MS Access database be converted to an MySQL or SQLServer database without too
much re-write? (queries, front-end code etc), also the ASPs and web code?
Can an MySQL or SQLServer database be hosted on a Windows NT Server, or does it have
to be Unix or lynix?
Can MySQL or SQLServer work as a desktop application under a Windows environment,
for development purposes? And for actual running?
What is the "wall" when it comes to the performance of Access and that of
MySQL or SQLServer and which one is better
We are running NT(IIS) at the moment to do our ASPs with, if we go to MySQL or SQLServer
do we have to go to PHP or JSP or something

That’s all I can think of for now. Any input would be much appreciated.

Norvan Vogt
vogtn@paperfoam.com

[deleted]

depends on what you consider "too much rewrite"
If you convert from MsAccess to MySQL or SQL server you'll have to convert all database calls, and you may run into some incompatibilities between access and mysql or mssql.
MySQL can be on win and unix, MS-SQL can be only on Win.
Servers never run as desktop apps, hence the name "server".

4.... no four?.. :-)

MS-Access is not really a database server that you want to use for high-load apps. MySQL and MS-SQLserver have a wall that you will probably never reach, unless you set them up on very low-end hardware.
No.

Anon

FYI:
(3) You can run MySQL and MS SQL Server on your desktop for development purposes. Although it is not recommended, you could run them as "server" to serve a small group of users, usually under ten.

Anon

You can use MSAcces9x Upsizing Tools to migrade your MSAccess db to MSSQL server (version 6.5 or 7.0) with all your rules, indexes, etc.

hope this can help you

ohenewa

i have answers for one or two of ur questions.
Yes SQLServer database can be hosted on a Windows NT server, 'cos i work in NT environment and i use SQLserver database.
You can always use the 'import...export data'
feature to transport ur access database to sqlserver without any work, the wizard will direct u through it.

Anon

yup

Anon

I have extensive experience with MySQL and MSAccess... As a matter of fact here at U of M they teach us Access, and I hate it...

Access has too much fluff with it - too much junk... I know that MSAccess is slower the PostgreSQL, and Postres is about 2-3 times slower then MySQL.

Having database at a backend of your site is a neccessity right now, just make sure that this database is not MsAccess....

Anon

FYI:
Tim Purdue has done some comparisons on MySQL vs. PostgreSQL, and the results were surprisingly in favor of PostgreSQL.

Anon

True,

Postres can take care of transactions and it can handle a higher load...

However, MySQL is faster and that is all I am saying...

Both database packages have their strengths and weaknesses...

Anon

Well, this wasn\\'t right place to post a question, as U are using MS-technologies. Guys here will probably say MS sucks LINUX dont and many of them wont take your problem seriously.

U didn\\'t give detail of waat U are using and waat R UR plans.

U cant certainly use MySQL in the current setup of yours despite the fact that MySQL is a free product. The reason for that is MySQL is not for Windows, U would certainly need a LINUX box, too much maintaince cost, quite a lot of hassle \\'cause UR switching technologies and OS (for DB Servers) too. U can use NE language on server side which supposrts ODBC, almost all DBMS have ODBC drivers. If U want performance UR apps certainly have to B re-written, \\'cause even though MS-Jet engine supports ODBC, it aint optimize for it.
U can upgrade to MS-SQL Server, only if UR company can afford it, it will be best RDBMS on UR Setup.
I would discuss few things.
There are two types of Databases.
Dektop database
Another type of database
Desktop databases
Desktop databases such as MS-Access id not for a big company, if U have fewer than 50 local users, then its A OK nad that if U dont have a big Server. Because if U ask for SELECT * from authors WHERE FirstName=\\"Jeff\\" the Desktop database will most return all the records in the authors database and client is responsible to search for the record.
MS-Access has improved a lot and would not do such a thing, but still in response to some similar queries, it will behave as Dektop Databases. Access 2000 supports 2GB DB.
In this type of DB, when U submit such a query like the one mentioned above, Server will most probably do the processing and send client the result. Client may do the sorting job etc. U guess right lesser records are to B transferred, lesser bandwidth required or lesser time to reach client. MS-SQL Server, Oracle are examples of those DBs.

Back to the original question of yours. U said UR using IIS and WinNT, I would most certainly recommend MS-SQL Server 7 or SQL Server 2000, it would cost U some money but its worth buying. SQL Server 2000 is strongly competing with Oracle and is breaking the perfomance and cost barriers. Access\\' Wizard or import option enables U to directly port UR Access DB, UR buisness logic may/may not need to B updated.
1. May, if U want performance here, use ADO.
2. May not, DAO can connect to SQL Server through ODBC, but performance suffers.

====================
MS-Access cant be compared to My-SQL, as the type of DBs are different. My SQL is fast but is not suitable for commercial purpose, many wont recommend it. If U have a large size DB, both DBMS will go down.
U certainly dont have to compare MySQL to MS-SQL Server because SQL Server is too good for MySQL.

Anon

Yes, MySQL is alot better than Access.

I think you can get the most speed from your system by ditching IIS and going with apache and php along with MySQL. It runs great on windows as well as linux. I use it both ways.

IIS is a very top-heavy system and you will find apache for windows to be lean and mean.

The catch? Well, php is more stable when run as a cgi in windows and there is some performance loss. For max performance, go with linux, apache, php as a dso and mysql.

The performance increase is unparalleled. You'll have to mess with your application code a bit, but there is no better way to get that level of performance.

If only MySQL has subselects, though...

Anon

The post I'm replying to is a rather egregious case of missinformation.

MySQL does run on Windows. I have it installed on both Windows and Linux.

Some of the professional sites that use MySQL for major database chores include:

Yahoo! Finance, MP3.com, Motorola, NASA, Silicon Graphics, and Texas Instruments!

In fact, it is a very commonly used databse on internet sites of all kinds. Not that it is internet specific, but that is a place where it has been used heavily.

Charlie

Anon

I have been a unix administrator for many years, and from my experience I have very rarely encountered and issue with MySQL database server.

MySQL is free, and robust enough, to where one could probley complete any major database project. I seen projects with tables as large as 1 million records, running stable on a P2 400, with 256 megs of sdram running slackware.

I do find it insane in a production environment one would use MySQL for Windows. For educational and testing purposes this is ok. But the fact is MySQL doesn't proform nearly as well in a windows environment then in a linux or unix environment.

MS-SQL Server is horrible for the basic reason that it costs a boat load of money to purchase. There is alot of overhead because of the Windows OS, and more demanding system requirements.

MS-Access is even worst because it was really never ment to be a database server software. I feel that having more then 5 people using an Access database at one time is risking it, because of table locking.

Sincerely,

Al Fire