Slashdot Mirror
Microsoft Access As A Client For Free Databases?
"Can this be done effectively? Is there any good documentation on connecting Access to Non-Microsoft DB servers in general, and MySQL in particular? No 'ODBC RTFM' flames, please; I'm looking for something a little deeper here."
I'm not very familiar with ODBC beyond it's basic use, so I really can't answer this question, but might this be possible if the MySQL server were somehow treated as an ODBC source? It would be tremendous coup if somehow the power of the Access front end could be used with MySQL (or PostgreSQL, or mSQL or any other open sourced RDBMS)? MySQL does have some ODBC functionality and as well as other ODBC related links on their site.
For those of you who want to immediately play around and see if this is possible, you can find the MySQL ODBC driver, here and a PostgreSQL ODBC driver here.
(My apologies. The story went live right as I was still editing it to include links in the last two paragraphs...)
Plus, MS SQL Server is a better database than MySQL. You're just asking for problems trying to run the DB backend on MySQL on Linux (or NT, even), with an Access frontend.
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)
- - - - -
Napster-to-go says "Fill and refill your compatible MP3 player", which is a lie. It's not MP3. It's WMA with DRM.
I have actually looked into the feasibility of doing something like this before, and I think you may run into some problems. The main problem is that what Microsoft calls SQL in Access is very misleading . . . while it is structured, and is a query language, it isn't SQL. The Access version of SQL really just gives the ability to have text based representation of what can be done using the GUI queries which are needed to compete with Paradox and other proprietary databases.
In addition, Microsoft has extended the MS-SQL language to use its own proprietary extensions. Microsoft has taken the attitude with Access SQL that they take on everything else - they program what they want and the standards be damned.
The major differences between Microsoft Access SQL and ANSI SQL-92 are listed in a table on page 190 of "Access Developer's Handbook" by Litwin, Getz & Gilbert from Sybex.
Here's a quote from the above book, "Access SQL is a hybrid SQL. It differs considerably from each of the SQL standards and doesn't completely support any of the ANSI SQL standards. It lacks large chunks of the standards, particularly in the areas of security and cursors. Sometimes it supports the same functionality found in one of the standards, but with a different syntax... In other cases, similar functionality is provided elsewhere in Access... Finally, Access SQL has some useful extensions that are not present in any of the standards..."
Overall, you may have some difficulty using Access with MySQL. If your boss is making you use MS on the frontend, it really will save you some trouble to use MSSQL on the back. If you do decide to go ahead with MySQL, my only advice is to make sure it is a well-researched decision. If you don't know exactly what you are doing and it doesn't work, it will just bolster your boss's attitude that all linux / open source solutions are problematic.
Black holes are where god divided by zero
http://www.devshed.com/Server_Side/MySQL/ODBC/
"Learn it from start to finish. Installing MyODBC, creating a new data source through the ODBC Data Source Administrator, linking a MySQL database into a new MS Access database, and finally updating the MySQL database through an MS Access GUI."
Enjoy
This paid my last vacation, it mi
ODBC Socket Server is an open source database access toolkit that exposes Windows ODBC data sources with an XML-based TCP/IP interface.
It has clients for PHP, Perl, C (in Windows, Mac, and Linux), Java.
Let's see if I have this right. You don't want to use Microsoft SQL Server on a Win2K box, and you'd rather use MySQL on a Linux box. You make no mention at all why you want to do this.
I have no clue what kind of application you have -- what the nature of your data is, or the nature of the front end is. I have absolutely no idea why you think a web browser front end, PHP middleware, and MySQL back end is the superior solution. (Or, perhaps by PHP front end, you were refering to that GTK/PHP toolkit mentioned on Slashdot a few days ago, and you want all the users to have a Linux box on their desk. I don't know.) I have absolutely no idea why you believe Microsoft SQL Server and a MS Access front end to be an inferior solution.
What I do know are these things. MySQL and Microsoft SQL Server are two vastly different applications. As a DBA, you are obviously aware that on a technical level, Microsoft SQL Server is the vastly superiour database. In a shop that is almost entirely Microsoft, with only a few Mac's, the MS SQL Server is going to vastly easier to keep running, unless you plan on hiring consultants or tech's dedicated to keeping the only linux box alive.
Face it... they're on completely different planes -- the only place that MySQL beats MS SQL is on speed, on only a subset of the queries that MySQL is able to perform. You won't find any other comparisons of the two databases, becuase MySQL simply will never, ever be able to do any of the things that MS SQL does, and does well. A fair, unbiased consumer reports comparison of the two databases would be nothing but hundreds or thousands of checkboxes that give MS SQL two or three or four stars, and give a little "N/A -- not available" mark to MySQL. It would be a joke.
I'll freely admit that there are many places why MySQL is useful. The standard Linux or BSD box, running Apache/mod_perl/PHP, with a MySQL database is a tottally rocking deal. If you're a decent Unix hacker, it's easy to keep running, it's extrodinarily flexible, it's easy to learn to use, and it's fairly well documented. For 98% of the websites out there, it's plenty good enough. You can even set one up in your office on a spare pentium 75, just to try stuff out before going live with it. I love the combo, and it's what we use on a lot of the sites we develop at work. But for the people who need it or want to pay for it, we use a real database. MySQL is a great database for developers who are aware that it is not a great database, and can explain why.
But for running a dedicated database machine, hooked up exclusively to a group of MS Windows clients running a MS Access front end, in an office where the DBA isn't smart enough to make ODBC work, and the DBA's boss only uses Mac's, I can't think of a single reason you'd want to use MySQL. Not one. Having a wierd box in the corner running a wierd database on a wierd operating system that only one guy in the office understands, duct-taped up to a bunch of Windows clients, seem like the perfect recipe for a maintenance disaster a few years down the road. What a crappy idea.
Slashdot is jumping the shark. I'm just driving the boat.
Here's a step by step of what I did to get PostgreSQL 7.1beta running with ODBC that might be useful.
You might also take a look at this page./
http://www.iserver.com/support/virtual/mysql/odbc
Amazing what a Google search can turn up is it not. Just a hint Most of us will *never* think of anything so unusual that it is not already on the web. Do a search first and then ask questions.
Cypherpunks: Civil Liberty Through Complex Mathematics. Those who live by the sword die by the arrow.
You can hate Microsoft as much as you want, but MS-SQL is a whole hell of a lot better than MySQL. There's places where all these wonderful OS applications don't work nearly as well as a proprietary solution, and databases is definitely one of those places.
MySQL is a great DB for fast read access where things like row locking aren't very important. Great for the web when you don't want to spend a lot of money. But if you've already got SQL then using MySQL is just plain dumb. SQL on NT will out-perform MySQL in just about every way. Not using it just because you don't like Microsoft could get you fired, and IMO it would be justified.
As an extension of the above, and table creation must take place in MySQL and then a link in Access created. Table mods must also be performed in MySQL afaik.
I haven't done this in ~2 years, but that's how it worked then, so take it (above comments) with a few grains of salt.
--
Never meddle in the affairs of dragons,
Never meddle in the affairs of dragons,
for you are crunchy and good with catsup.