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...)
I should have mentioned that: When we looked at it MSDE is 'free' only if you have 1) SQL Client licence, 2) VisualStudio licence ($$!), 3) Office 2000 Developer licence ($$! - Normal users get Office Professional, not Developer).
Unless I'm wrong, you don't get MSDE included with the normal version of Access.
--
Business. Numbers. Money. People. Computer World.
I wouldn't say SQL on NT will out-perform MySQL is just about every way. I personally was using SQL 7.0 on dual xeon 2g rdram and it got its ass kicked by mysql on the same system.
Like the people who compain about NT crashing every 3 days, you are only demonstrating your incompetence.
If you don't have the slightest idea how to tune MS-SQL, don't advertise the fact. Just keep your mouth shut and maybe learn about the system that you're using.
Cheers,
--Shoeboy
Let me preface this by saying that I have done a lot of work over the last few years using Access. For some things, it's a great tool. For others, it's not.
It shouldn't mean leaving your job to do what you believe in.
It's fairly trivial to convert a set of access tables and queries to SQL create statements that will re-create your design in MySQL, so...
Design your database in Access. Design it well - Get the tables right now and you will save yourself a lot of trouble.
A bad design in Access won't be any better in MySQL, trust me.
Be patient and do what you're told - do it in Access. When you design it, keep in mind that you might be upgrading to MySQL. Find out where it might break and avoid those areas.
Find out the advantages to using MySQL and promote them when the time comes. One example would be distribution of the data over the company intranet. Explain that web-based reports can save you installing Access on everybody's machine. In my case, that was the decision point - The company had not bought licenses for everyone to have Access on their desktop. Explain that a switch to MS SQL Server will also require client access licenses. Explain that MySQL can be had for free. (Actually, don't say 'free' - say 'MySQL doesn't require CAL's for the clients and will do what we need.')
Remember that MySQL can be had for NT, too. That may sit better with the PHB's.
Write some VBA to create MySQL create statements from your Access tables. Attach the new tables using MyODBC. Move the data from Access to MySQL. The other developers and your boss will be more comfortable seeing their data as familiar Access ODBC-linked tables than they will seeing it as text output on a terminal screen.
Write documentation that Windows people will understand. Do a newbie howto that tells exactly how to create the database and build the tables, step-by-step. A lot of the fear of using open source tools is that they can't find people who know it. Make your docs such that any MCSE can install and administer it. Burn a CD that has all of the required binaries and a script that will install everything.
You'll probably have to downplay the Open Source aspect of all of this. Show them that to use MySQL, they don't have to abandon Windows. Just let them know that they are choosing a robust, stable place to put their data that will work on several different server platforms.
Also, keep in mind that MySQL may not be the right tool for the job. Be prepared to use whatever is best to get the job done well, be it Access, MS SQL, MySQL or whatever.
Good luck!
Cheers,
Jim in Tokyo
-- My Weblog.
Well, I agree, but there is definitely some boss management that has to be done here. You don't want to put yourself in a situation where you are responsible to implement a strategy that is going to fail.
The problem is that Access (W/o sql-server) is actually less scalable than FileMaker Server. I know this because I run into people who are living in the crashed ruins of both Access and FileMaker projects -- it's a good part of my business.
In the application development domain, most people will end up taking FileMaker further than Access. This is because FileMaker is not a relational database -- it's an easier model for non-expert database designers, more forgiving. It's usually possible to recover failed homegrown FileMaker solutions with some general business application expertise. Similar Access solutions are usually a total loss.
Access is really a gateway drug for MS-SQL and VB. It's very well tuned to give people in the early stages of development positive experiences, but then it breaks down as performance requirements increase and the application increases in complexity. The solution to this is MS-SQL (or another client server database) and to hire an experienced business application developer (and possibly a platform change).
It's not like you can't create Access applications (using the SQL Server back end) that are scalable in complexity and performance. It's just that in practice the successful Access "applications" I've seen are personal or small departmental data entry systems of very limited scale and purpose.
Personally, I use Access for lots of "quick-and-dirty" kinds of things, such as doing data conversions. I think it really shines there (aside from its tendency to pessimize queries to external data sources). I would probably not use it for serious business application development. If you truly have outgrown FileMaker, then you should turn your attention to tools that are designed for professional application development, such as Delphi, PowerBuilder and VB. Actually, these days given my druthers (which as a developer you seldom have) I'd use Java, or for some applications Zope and Python.
I sympathise with Daniel's boss. Microsoft looms large in the imaginations of Mac enthusiasts -- implacable, evil and undefeatable. Implacable, maybe, but evil is questionable and undefeatable is certainly not true. It is simply not the case that there is no other way to survive but to go completely into Microsoft's embrace. It's just that a lot of the creativity that made the Mac platform an exciting place to be in the late eighties has moved elsewhere. Remember getting the huge stack of floppies of incredible freeware applications from your user group?
Where has all that engergy gone? You should take your boss on a tour of sourceforge. Daniel should take his boss on a tour of SourceForge. The difference now is that the free software/open software movements are not a single corporate entity that whose air supply can be squeezed off. They're more like a highly opportunistic fungus that spreads around attempts to control it and leaves spores (source code) where it has been extirpated.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
No, no! He obviously wants to run the postgres setup.exe under wine! ;)
- - - - -
Napster-to-go says "Fill and refill your compatible MP3 player", which is a lie. It's not MP3. It's WMA with DRM.
How many times do we have to tell the "pointy haired boss" that politics does not make good software?! You need to impress upon your boss the fact that all marketing and promises aside, Microsoft Access is not a scalable or stable solution!
When will people learn to delegate responsibility fully to the people who know how to do their jobs best? By not trusting you, the knowledgable staff member, on your design decisions, your management is suffering themselves no small number of headaches for the future of their company. By locking you into an environment that you are 1) not comfortable with, 2) do not trust, 3) doubt will fit the bill, and 4) dislike, they are seeding the crop of their own distruction.
Take a stand. Review your software design goals, and research the proposed tools and environments to do the job. Include in your research their proposals, and work to disprove them based on your own knowledges and instincts. If they can't trust you, their developer, and would rather go with what a marketing drone would recommend, tell them to hire the marketing drone to write the software.
On a less general note, my advise about writing RDBMS-based software:
What can I say. Access + MySQL sounds like a loosing combination. Access and MSSQL sounds like a better combination, but I would opt for something along the lines of PostgreSQL and Java (though the client-side SWING sucks ass). If you design the application with at least a three-tiered approach, client-server-database, you will be allowed some flexibility in which direction you can migrate with DB selection and client selection.
Anyway, I have to go to sleep so I can get up tomorrow and program... Good luck! Oh, go check out some of the projects like Enhydra.org or jboss.org if Java+Appserver+RDBMS+web raises an eyebrow or two.
--
assert(expired(knowledge));
Go with PostGreSQL instead.
--
It's rare that you're presented with a knob whose only two positions are Make History and Flee Your Glorious Destiny.
I have done something similar to the above with Postgresql and MS Access.
I have a stock Redhat 7.0 box with Postgresql running with an MS Access front-end. It works quite well. Use the postgres odbc driver and create link tables in the access database.
The biggest quirk that I have seen it that booleans don't work well, use integers. Access likes to use (0,-1) as false,true. The odbc driver translates booleans as a single char. use a "yes/no" data type in Access and an "int4" in postgres and the rest should be fine.
This system has been up and running for over a year. although, it's not under very heavy use. The secretaries update personel info in the database. They only have to update things once. The web server pulls data from postgres (ala php), so the secretaries update my web pages for me! makes my life easier.
I'm part of a team who works with PostgreSQL & ODBC connected to ColdFusion on a dynamic website.
:). I can at least tell you that it's been done, this is what the tools were DESIGNED to do, and that I've personally seen it all put together before. I can also tell you that it wasn't any major feat to get it working (it stumbled into our lap basically). The guy hated working with the command line psql client (read: he couldn't)... so he slapped Access up against it.
One of the guys, who has zero education when it comes to coding, databases, and computeres in general (don't ask me why or how he's a programmer.. I have no idea) managed to whack Access up against the PostgreSQL database via the ODBC portal I had done to it (can't remember how... it wasn't much of a thing).
It certainly isn't a revolutionary idea, people do it all the time... it's a two step process:
Setup ODBC to the database
Setup Access to hit an ODBC source (doesn't matter WHAT it is)
Yes, you don't want an "RTFM" post but really.. that's what you'll get
If you do a search in the MS knowledge base, look for access, thread, multi, and a few other things, you'll dig up the MS KB articles that say "Don't use Access! It's unstable!" It amuses me how the same people/companies that wouldn't use Word for creating a thousand page catalog, and wouldn't use Excel to write a corporate accounting system would, in fact, use Access to create an 'enterprise' database.
Vintage computer games and RPG books available. Email me if you're interested.
I agree that MySQL isn't ready for primetime yet, but it isn't the only open-source free RDBMS.
You are being equally rediculous by insisting on using MySQL even when it is a non-optimal solution, as your boss was to require you to use Access! While SQL Server is usually less efficient for non-transaction based databases (since MySQL doesn't support transactions), it works well with Access. Instead of jury-rigging a MySQL Access ODBC driver, you should use MS Jet for SQL Server. That interface will likely allow for faster DB interaction than a jury rigged driver.
If you really have a problem using MS products, you should find a job where you are able to develop on an OS platform. Even though I tend to use more MS software than I have to, I understand where you are coming from. It is much easier and more efficient to develop a DB front end using Perl/DBI than Access/VBA.
Lenny
ByteMyCode.com: A Web 2.0 code sharing community.
We've got some clients who are comfortable with Access, so they use Access with the MyODBC driver, and hit the MySQL on the back end. They simply update their data in Access, and it's "live" on the site instantly.
:)
That's the only major downside to this - people who work with Access aren't always used to thinking that thousands of people will be affected by their data changes in real time, so we don't give this functionality to all client.
It's also nice for them to be able to create their own reports based on live data from the website, rather than having us build web-based reporting tools. Yes, it's nice extra work, but when you don't have time and/or they have a budget, letting clients develop things how they want with their own tools is a nice option.
creation science book
The poster didn't say whether they already HAVE SQL on NT, or whether it might be an option later. Agreed, if they HAVE it already, just use it. If they DON'T have it, spec out the requirements. No doubt SQL Server has many good points, and for most jobs will be stronger than MySQL.
But... if they don't need it, why spend the money? We just completed work for people who bought SQL2000 and 2 W2k boxes, THEN brought in someone to build the website, before it was specced. We built on it, and yes, it worked fine, but for their needs, ONE (maybe two) Linux boxes with MySQL would STILL have been overkill. I have 1 box handling 2-3 times their load, and it's a 300mhz with 128 megs of RAM. They've got 512 meg systems, dual processor 750 mhz, RAID, etc.
Again, nothing wrong with SQL Server, but it's overkill in many situations. We prefer to inform clients of their needs first, and make purchase recommendations after needs analysis, not before.
creation science book
It comes down to this:
If you're the DBA and you've been given the responsibility of implementing the data storage at your company, you should be the one who decides what system is used.
Gently explain this to your boss. Tell him you understand that he may have a preference toward Access, but you know from past experience that Access will not meet your long-term needs and you recommend something different.
If your boss still insists, it's time to move on. You work for a micro-manager and that's the pits. Trust me.
--
All opinions presented here aren't mine.
Urm...it doesn't support transactions? Check your facts first!
--
--
I Hit the Karma Cap, and All I Got Was This Lousy
Interbase has been open sourced and is multiplatform. So even if your boss is determined to use a NT/2000 server you can always move to Linux later and keep the exsisting database. MySQL lacked any stored procedure support the last time I was using it. Now that I've worked with oracle, ms sql, and interbase I don't think I would want to make a database application/site that didn't support a stored proc. I'm sure there are many ways around them but from a programming aspect I find stored procs have their uses to make the overall programming easier to maintain and understand.
Also if (which I doubt you would get any from M$ for free) free support isn't a concern then the downloadable version of interbase works quite well. I'm currently redesigning a project from paradox to interbase. Access like paradox is a non intellegent database which requires all work to be done on the client side. The SQL server has intellegence on the server side. The other aspect of a SQL server is that the users don't have to have any access to the directory where the database actually is. I have had users accidentally delete tables before since they have to have read/write access to the tables.
Depending upon your project MySQL could be just fine, it can be run on NT also. I think if you look at the requirements of what your company needs, and which database would better fill those needs, that would make a better argument for the M$ for everything mentallity. Business oriented people will be more impressed by the business side of the argument than the technical aspect.
If ignorance is bliss, the world is full of blissful people
It might be better to pick one of the following:
1) implement with Access like the boss wants or
2) find a better job where you can use Linux
This is not a troll, or a flame, or offtopic. I'm serious.
If tits were wings it'd be flying around.
I don't have too much experience with Mysql but here are some problems I have run into with postgres.
1) Make sure the ODBC driver uses cursors. If not Access will attempt to pull all data from your tables for a simple select queries. This is especially true in forms bound to tables (a bad idea in the first place).
2) the default setup of access runs queries in a case insensitive matter. Make sure you turn on the case sensitive option. Do this even if you go with ms-sql it will make migration easier when you ditch it later.
3) Make sure every table has a primary key and the index name of that primary key sorts alpheitcally first. Name your primary keys AAAAAPkey_tablename or somthething.
4) put in a timestamp field in every table it helps access out.
5) Keep your table names relatively short. Access and SQL server let you have very long table names while Oracle, postgres, db/2 will limit it. If you want to upgrade your sql server later you will be glad you kept your table names short.
6) On a similar note don't put stange character in you field or table names. Don't name objects fax# or discount% also don't use a number as the first character of an object name. Access will let you but your dabase might not. Basically it's best to limit yourself to alphanumerics and maybe the underscore.
7) Make all your table names uppercase. This is not strict but it's a good idea. If not at least try to keep a good method for capitilization and stick with it.
8) Try not to depend too heavily on stored procedures. Although they can buy performance they make switching databases very hard.
War is necrophilia.
perl -e 'fork||print for split//,"hahahaha"'
Imagine you have an order entry form, with the top half devoted to order related fields and a sub form that has the line items for the order. Now both the main form table and subform table have pkeys that are auto_increment. In plain old access this is no big deal, but with access/(odbc)SQL you have a big problem.
According to the MySQL mailing list and the MSKB, this is because Access will re-select the data it just inserted to find the row it THINKS you are working on. That's how it get's the database default fields to take effect and the auto_inc fields. Since, for example, you might have many orders with the exact same order line information, it may or may not find what you want in that subform. Same thing for the order header, if it's a stock order from week to week, then it may not find it.
Their solution is to make every form that adds records have a field that inserts 'now' so Access can find your row better.
An ugly kludge if you ask me, but I worked with it. And when I was done I started installing ODBC drivers on every machine and making sure the DSN was right and making sure the proper version of Access was on the machine. This was the other big problem, the amount of software required to make the system work on the client side, it's un-believable. So I've abandoned Access/anySQL and now work with just web-based applications. Only software required is a browser and thanks to MS and the popularity of the internet, that means everyone will have it in one form or another. It also means people can work from home, the office, vacation, whatever.
The only drawback was rapid app development, sql forms and even forms in general require so much bitch-work to get working it's just frustrating. So I've built some perl modules that handle SQL forms generation, binding to fields, verification, and insert/update/delete functionality from data descriptions. The bonus of this is I can make the updated forms definition a module and re-use it. Now, in access, if they say "we want 1 more decimal of precision here..." you've gotta find the damn field on every form and change it there, no re-use. The only thing missing (and actively being developed) is a reporting module to generate text/html/pdf/etc reports.
Free Online Woodworking Resources Directory
I'll drink to that. I don't know what exactly Access does, but it fails miserably with even moderately-sized tables. We use Oracle, and figured it would be nice to provide an easy-to-use interface for staff. So we bought Access, installed the drivers, and linked the tables. It would take as much as 20 minutes for windows to open; searches and so on would take longer than anyone wanted to wait. None of the tables in question is more than a few million records, which in any case should be immaterial since you'd expect the front end to use the back end to do the heavy lifting. My best guess was that Access rolls through all the data itself, totally missing the point of why there's a real database at the other end of the line.
Anyway, I ended up having them whip up a PHP/web interface instead, and everyone's happy now.
"Patriotism is your conviction that this country is superior to all other countries because you were born in it." -- GBS
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.