mySQL vs SQL Server as backend (Access (All))

Topic

New Reply

With Access as the frontend, does anyone have any experience using mySQL as backend database? Any pros/cons to be aware of? What about vs. SQL Server? Specifically, any stability issues of one vs. the other (i.e., when users lose connection, etc.)?

Reply | Quote

Replies

Hi Mark,
I only have second hand experience with mySQL, but have looked into it a time or two. There is an ODBC driver available for mySQL and I understand that people seem to think it works fairly well. I have used SQL Server a fair bit, and its ODBC driver is rock-solid in my view. My impression of the difference between the two is that mySQL is inexpensive (almost free) as it comes out of the open source community, and for small to moderate size database does a pretty respectable job. SQL Server on the other hand is an industrial strength database, with extensive logging capability, excellent performance, readily scaleable to large databases, and has more friendly administration tools. Hopefully one of our folks who work with mySQL regularly can amplify on its strengths and weaknesses.

Reply | Quote

Wendell,

Thanks for the comments. The application is current Access97 front and back. The BE was only up to about 40MB, although a couple of tables with (with very small records) were getting quite large. We recently moved alot of old records to an archive database, which brought it back down to 20MB. We’ve had few problems until recently, when all of a sudden it has had to be repaired/compacted frequently.

One problem I’m finding is that the guy who does the hardware just installs the original Office97, and never any of the Service Releases! At one time I had most of the workstations up to SR-2b (except for some I couldn’t get past SR-1 because of the version of NT workstation installed). But every computer upgraded in past couple of years is back to the initial Office version. Drives me crazy.

Anyway, the need isn’t so big that it requires SQL server, which is why I was wondering about mySQL. Given I want to get the workstations on same version of Access (maybe 2002 or 3), if we can save a little $ on the server, it might help.

Reply | Quote

Hey, Mark,
in case no-one at the Lounge enough MySQL experience to help you out, it might be worth having a look ‘outdoors’. I didn’t immediately find a real comparison between SQL Server & MySQL but a quick Google search using the combination “Access front” “MySQL back” gave already some results which might give some help:

-“Using MySQL from Microsoft Access” probably is too basic, except maybe a link to the To make Access work in their MySQL manual which might provide usefull technical information

– http://www.geocrawler.com/mail/msg.php3?ms…981&list=13%5B/url%5D
=> about some bug/setting problem
– Dev Shed Forums MySQL Help
=> I assume that the MySQL community has also some other discussion boards which might be worth visiting, or leaving your question there

– Update MySQL with MS ACCESS data on a daily basis?
=> some people at Utter Access might have some working experience with MySQL

Then, at tek-tips
– at a first glance, halfway Convert MS Access to MySQL? there’s some Access BackEnd Structure conversion method which might be worth looking at
– in some other post about unable to update / delete records via odbc from access to mySQL someone sighs “truthfully I would suggest shying away from MySQL if you need a true relational database”, but I was short of time for reading the details;
– Moving from an Access Back End
– MySQL in a Windows environment says “I have heard all kinds of great things about MySQL…much faster than Access or SQL Server, handles more concurrent users (not sure if I can trust benchmark tests, because all of them seem to have a bias).”
(there might be even some info at the FAQ pages, but I’m thrown out of my friends pc so I couldn’t check more in detail… I hope you find some useful info in all this…

BTW: thanks for the question – I’ll keep an eye on this thread too

Reply | Quote

Hey, Mark,
in case no-one at the Lounge enough MySQL experience to help you out, it might be worth having a look ‘outdoors’. I didn’t immediately find a real comparison between SQL Server & MySQL but a quick Google search using the combination “Access front” “MySQL back” gave already some results which might give some help:

-“Using MySQL from Microsoft Access” probably is too basic, except maybe a link to the To make Access work in their MySQL manual which might provide usefull technical information

– http://www.geocrawler.com/mail/msg.php3?ms…981&list=13%5B/url%5D
=> about some bug/setting problem
– Dev Shed Forums MySQL Help
=> I assume that the MySQL community has also some other discussion boards which might be worth visiting, or leaving your question there

– Update MySQL with MS ACCESS data on a daily basis?
=> some people at Utter Access might have some working experience with MySQL

Then, at tek-tips
– at a first glance, halfway Convert MS Access to MySQL? there’s some Access BackEnd Structure conversion method which might be worth looking at
– in some other post about unable to update / delete records via odbc from access to mySQL someone sighs “truthfully I would suggest shying away from MySQL if you need a true relational database”, but I was short of time for reading the details;
– Moving from an Access Back End
– MySQL in a Windows environment says “I have heard all kinds of great things about MySQL…much faster than Access or SQL Server, handles more concurrent users (not sure if I can trust benchmark tests, because all of them seem to have a bias).”
(there might be even some info at the FAQ pages, but I’m thrown out of my friends pc so I couldn’t check more in detail… I hope you find some useful info in all this…

BTW: thanks for the question – I’ll keep an eye on this thread too

Reply | Quote

Thanks for the links, I’ll be checking them out.

As I was mentioning to Wendell, the situation isn’t such that it necessarily requires all that SQL Server can deliver. It is just getting to the point where it probably needs more than a totally Access solution. So I was thinking mySQL might be an economical way to go, and yet do everything I need for the situation. At least no one has said “DON’T USE IT!”, but then no one said ‘SURE, NO PROBLEMS!” either!

Reply | Quote

Marc, in addition to the links above…

1. Some other I’ve found in the meanwhile:
– another recent Utter Access thread: Mysql OR Sql Server OR Oracle “(…) l’m developing a DB to be deployed company wide (3 sites, 150 employees) with MySQL as a backend, and Access as a frontend. We’ve run it on Win2k with 256mb of ram (my PC ). Its being moved soon, however l have had no reason to complain, about performance. Its has almost all the bells and whistles of MS SQL, stored proceedures (the only one missing l know of) being added in V5, of which a test version has been released. Theres also a huge list of GUI tools use with MySQL, some free, some not. (…)”
– Open Source Database Technologies says “Primarily cost, stability, and security are the reasons to choose an Open Source server-based database management package, rather than, for example, Microsoft’s SQL server.”
– MySQL Developer Zone

1. Be certain to have a look at this one: Microsoft Access As A Client For Free Databases?… not just because it might well be the longest thread you’ve ever seen, but even more because it’s a very lively discussion from the Linux world, covering such a wide range of aspects (from philosophical to technical) and elements that, if you’re able to wade through all side-discussions and emotional outbreaks, it might prove some very good value. Just some citations:
– “MySQL doesn’t support all of SQL, or all of ODBC, and Access will try to do things that it will not allow. Stick with SQL Server (says the Linux guy) (…) MySQL doesn’t, but PostgreSQL comes a lot closer, and as someone else pointed out earlier, it actually outperformed Oracle in a few cases.”
– “i’ve done this – works pretty good… there are some issues with the date format in access, though so be warned.”
– “If you are careful, you can keep your apps compatible with MS-SQL Server.”
– “You’ll be able to use the exact same frontend code either for MS SQL, MySQL, Oracle or .mdb tables. I suppose Postgres should work just as well, although I never worked with its Windows ODBC driver.”
Etc Etc
=> Tip: over there, posts seem to get a score (header, first line): might be handsome to make a first rough selection while scrolling through the thread…e.g. “Things to watch out for. (Score:4, Informative)” (about PostgreSQL but quite interesting anyway)

2. Maybe I drift off too far from the subject but… FWIW… have you ever thought about PostgreSQL?
If so, there seems some good ‘how to’ reading at Using Microsoft Access with PostgreSQL<A target="_blank" HREF="http://.
If not, still, (but I’m a complete newbie here) maybe PostgreSQL topics might (!!) also apply to MySQL and therefore provide some guidance. At Dev Shed forums’ PostgreSQL vs M$ SQL>http://.
If not, still, (but I’m a complete newbie here) maybe PostgreSQL topics might (!!) also apply to MySQL and therefore provide some guidance. At Dev Shed forums’ PostgreSQL vs M$ SQL for example, someone mentions “(…) the inability to switch off case-sensitivity … My major app is a call centre and if one operator types ‘Smith’ and another types ‘smith’ … the simple query
SELECT * FROM tblContact WHERE LastName = ‘smith’;
will not match ‘Smith’
One thing that PostgreSQL will do which I believe M$SQL won’t is partial indexes … that is, if you have data where 80% of the data is the same and only 20% varies – you can create an index on the 20% so you *can* speed up queries on the 20%”

This’ll be it…. good luck!

Reply | Quote

Marc, in addition to the links above…

1. Some other I’ve found in the meanwhile:
– another recent Utter Access thread: Mysql OR Sql Server OR Oracle “(…) l’m developing a DB to be deployed company wide (3 sites, 150 employees) with MySQL as a backend, and Access as a frontend. We’ve run it on Win2k with 256mb of ram (my PC ). Its being moved soon, however l have had no reason to complain, about performance. Its has almost all the bells and whistles of MS SQL, stored proceedures (the only one missing l know of) being added in V5, of which a test version has been released. Theres also a huge list of GUI tools use with MySQL, some free, some not. (…)”
– Open Source Database Technologies says “Primarily cost, stability, and security are the reasons to choose an Open Source server-based database management package, rather than, for example, Microsoft’s SQL server.”
– MySQL Developer Zone

1. Be certain to have a look at this one: Microsoft Access As A Client For Free Databases?… not just because it might well be the longest thread you’ve ever seen, but even more because it’s a very lively discussion from the Linux world, covering such a wide range of aspects (from philosophical to technical) and elements that, if you’re able to wade through all side-discussions and emotional outbreaks, it might prove some very good value. Just some citations:
– “MySQL doesn’t support all of SQL, or all of ODBC, and Access will try to do things that it will not allow. Stick with SQL Server (says the Linux guy) (…) MySQL doesn’t, but PostgreSQL comes a lot closer, and as someone else pointed out earlier, it actually outperformed Oracle in a few cases.”
– “i’ve done this – works pretty good… there are some issues with the date format in access, though so be warned.”
– “If you are careful, you can keep your apps compatible with MS-SQL Server.”
– “You’ll be able to use the exact same frontend code either for MS SQL, MySQL, Oracle or .mdb tables. I suppose Postgres should work just as well, although I never worked with its Windows ODBC driver.”
Etc Etc
=> Tip: over there, posts seem to get a score (header, first line): might be handsome to make a first rough selection while scrolling through the thread…e.g. “Things to watch out for. (Score:4, Informative)” (about PostgreSQL but quite interesting anyway)

2. Maybe I drift off too far from the subject but… FWIW… have you ever thought about PostgreSQL?
If so, there seems some good ‘how to’ reading at Using Microsoft Access with PostgreSQL<A target="_blank" HREF="http://.
If not, still, (but I’m a complete newbie here) maybe PostgreSQL topics might (!!) also apply to MySQL and therefore provide some guidance. At Dev Shed forums’ PostgreSQL vs M$ SQL>http://.
If not, still, (but I’m a complete newbie here) maybe PostgreSQL topics might (!!) also apply to MySQL and therefore provide some guidance. At Dev Shed forums’ PostgreSQL vs M$ SQL for example, someone mentions “(…) the inability to switch off case-sensitivity … My major app is a call centre and if one operator types ‘Smith’ and another types ‘smith’ … the simple query
SELECT * FROM tblContact WHERE LastName = ‘smith’;
will not match ‘Smith’
One thing that PostgreSQL will do which I believe M$SQL won’t is partial indexes … that is, if you have data where 80% of the data is the same and only 20% varies – you can create an index on the 20% so you *can* speed up queries on the 20%”

This’ll be it…. good luck!

Reply | Quote

I wouldn’t abandon an Access back-end just based on size or record count – as long as you have an adequate maintenance program to backup and compact/repair on a frequent basis it is a solution that many people use quite successfully. On the other hand if the workstations aren’t bein maintained properly, you will like as not have problems regardless of what your back-end is, and going to 2002 or 2003 won’t help very much – they get regular fixes as well. If you need logging capability for recovery purposes, or performance is suffering on a slow LAN, or you can’t stand to take the database down for a few minutes to compact and repair, then I would consider a true database engine for sure. If you have no more than 5 or so users, you might also look at the MSDE – it comes for free with 2002 and 2003 and is SQL Server 2000 in a performance detuned version with limited admin tools.

Reply | Quote

My primary reason for considering moving to a different backend is the increasing frequency of database corruption we have been having. Given that this is the application that the company uses to run its business, I better backend is probably advisable. MSDE is out of the question because of the # of users. This application is in a manufacturing facility (hot and dusty), and I have some doubts about the integrity of the equipment on the shop floor and of the LAN itself. This also would prove an incentive to upgrade the older machines; for example, one is still using Win95!

Reply | Quote

My primary reason for considering moving to a different backend is the increasing frequency of database corruption we have been having. Given that this is the application that the company uses to run its business, I better backend is probably advisable. MSDE is out of the question because of the # of users. This application is in a manufacturing facility (hot and dusty), and I have some doubts about the integrity of the equipment on the shop floor and of the LAN itself. This also would prove an incentive to upgrade the older machines; for example, one is still using Win95!

Reply | Quote

I wouldn’t abandon an Access back-end just based on size or record count – as long as you have an adequate maintenance program to backup and compact/repair on a frequent basis it is a solution that many people use quite successfully. On the other hand if the workstations aren’t bein maintained properly, you will like as not have problems regardless of what your back-end is, and going to 2002 or 2003 won’t help very much – they get regular fixes as well. If you need logging capability for recovery purposes, or performance is suffering on a slow LAN, or you can’t stand to take the database down for a few minutes to compact and repair, then I would consider a true database engine for sure. If you have no more than 5 or so users, you might also look at the MSDE – it comes for free with 2002 and 2003 and is SQL Server 2000 in a performance detuned version with limited admin tools.

Reply | Quote

Thanks for the links, I’ll be checking them out.

As I was mentioning to Wendell, the situation isn’t such that it necessarily requires all that SQL Server can deliver. It is just getting to the point where it probably needs more than a totally Access solution. So I was thinking mySQL might be an economical way to go, and yet do everything I need for the situation. At least no one has said “DON’T USE IT!”, but then no one said ‘SURE, NO PROBLEMS!” either!

Reply | Quote

Wendell,

Thanks for the comments. The application is current Access97 front and back. The BE was only up to about 40MB, although a couple of tables with (with very small records) were getting quite large. We recently moved alot of old records to an archive database, which brought it back down to 20MB. We’ve had few problems until recently, when all of a sudden it has had to be repaired/compacted frequently.

One problem I’m finding is that the guy who does the hardware just installs the original Office97, and never any of the Service Releases! At one time I had most of the workstations up to SR-2b (except for some I couldn’t get past SR-1 because of the version of NT workstation installed). But every computer upgraded in past couple of years is back to the initial Office version. Drives me crazy.

Anyway, the need isn’t so big that it requires SQL server, which is why I was wondering about mySQL. Given I want to get the workstations on same version of Access (maybe 2002 or 3), if we can save a little $ on the server, it might help.

Reply | Quote