Follow Slashdot blog updates by subscribing to our blog RSS feed


Forgot your password?
Linux Business

Microsoft Access As A Client For Free Databases? 353

Daniel Dvorkin asks: "A few months ago, the small but growing company I work for decided that our current FileMaker-based database solution wasn't working. As DBA, I recommended a MySQL server with a Perl or PHP-based front end. My boss, who despite being a Mac guy has recently developed an inexplicable mania for all things Microsoft, is insisting that I develop everything with Access as a front end. I'm deeply unhappy about this, but I'm hoping that I might be able to salvage a little bit of the situation by using MySQL on Linux rather than SQL Server on NT for the back end." Think something like this might be possible? Read on for more informaiton. Updated!

"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...)

This discussion has been archived. No new comments can be posted.

Access As A MySQL Client?

Comments Filter:
  • by Anonymous Coward
    mysql does act as an odbc source quite nicely. heck there are open source odbc drivers out there for it -- look on freshmeat/google.
  • by Anonymous Coward
    Sure it's bullshit, but it's also Oracle's business model. You buy their database for big bucks, then you pay more big bucks to get their engineers to tune it for your application.

    Oracle put in the ban because people were publishing benchmarks showing dismal Oracle performance. Maybe that's realworld, maybe it isn't, but without an Oracle engineer looking over their shoulder, how would they know?

    It's also why MS-SQL (which is sorta 'self-tuning') is a much better decision for smaller applications because you don't need a PhD DBA to muck with the thing.

    Not to mention the standard benchmarketing is TPC, which Microsoft and IBM kick ass with by using 100s of 'small' x86 Xeon servers in a cluster -- something which doesn't affect any realworld applicaiton in any way (and if it does, you can collect the $1Million from Ellison).
  • by Anonymous Coward
    You can set MySQL up to work as a DSN in Windows, just like Access (for the ODBC comment). Its fairly easy to do with the Win32 version of MySQL and is relatively simple to simple to access if its running on another OS.

    I've actually ran into this problem a few times, and the deciding factor in my judgement was the scope of the app: if it needs to be web-accessible, your PHP/Perl and MySQL solution is probably better. But if its just an inter-office/use-on-the-LAN app, Access is probably a better choice.

    For all of the greatness of PHP, Perl, and Access, there is only SO much a webpage can do. An app with an Access front end can have the same feel as any other VB based application, including combo-boxes and complex grids--thing impossible to do on a webpage. Data manipulation (in a GUI perspective) is far better in a VB based app than a web-page.

    This would be the first step I took...
  • by Anonymous Coward
    Not necessarily - I have seen too many programmers pushing agendas lately, and they are not based on what the business needs, they are based on that the programmer wants to play with. MySQL is not a production database - it is a toy to play with at home, and I would fire anyone that recommended it as a 'serious' solution. Even access has better features such as row level locking and such. If you are serious and need a database - there are lots of good choices - Oracle, Informix, and SQLServer come to mind - they are designed for heavy load, high performance and have the stuff needed to do production - backup, job scheduling, enterprise class performance, etc... Of course, if this is a hobby system, Linux and MySQL will work great. Most businesses are not hobbies. And yes, I am a micro manager (former/present senior programmer and DBA - so what that means is that I don't take crap from newbie programmers playing with toys - we all get fired if the wrong decisions are made, and it's the manager's job to make sure that the right ones are made.) Having a technically savvy management can drive programmers crazy, but it's better than shops where the programmers run wild. And the driving crazy part goes both ways.
  • by Anonymous Coward
    Yes, please do got to and specifically check out the "Non-Clustered Result". You'll notice that no Microsoft solution gets into the top 10 for performance on TPC-C.

    Microsoft (and IBM) are benchmarketing by using totally unrealistic configurations for TPC-C - namely a cluster of 100s of 'cheap' Xeon servers instead of the more usual configuration of one big box.

    You'll notice that Windows Datacenter and the Unisys 32 CPU jobbie don't show up anywhere. But this is the configuraiton that MS will mostly likely recommend if you had a real MS-SQL scalablity problem.

    Which is not to say that MS-SQL is not an excellent product for midsized applications, just to counter the FUD.
  • by Anonymous Coward
    #1, Sybase's ODBC support isn't so good.
    #2, Sybase is only free for development or non-commercial uses. The company I am working with right now checked into the Linux Sybase licensing for their commercial needs and it would have been around $15,000.
    #3, SQL Server was indeed based off Sybase, but that was a LOOOOONG time ago and has since greatly diverged from Sybase. On the PC platform, I would suggest that SQL Server is far superior to Sybase.
    1. Download, install MySQL ODBC Drivers for Windows from []
    2. Create an ODBC source in Control Panel, entering all necessary information for your Unix/MySQL Server.
    3. Create all necessary tables in MySQL.
    4. In Access, New, Link Table, Files of Type: ODBC, choose your just-created ODBC source, choose your tables.
    5. You now have all your MySQL tables accessable just if they were Access tables. You can create forms, reports, whatever, using data stored in MySQL. Only thing you can't do is modify table schema from Access - and if you do that from within MySQL, you need to refresh your table links (Tools, Database Utilities, Linked Table Manager).
  • Unfortunately the poster didn't say anything about what their database needs are - what the access patterns will be, how much load is expected, how much data needs to be stored, how much expansion capability they need, and so on. So saying that he is a genius/idiot for wanting to use one database over another is foolish. For the moment we must assume that his requirements are such that MySQL is appropriate and/or that the Microsoft "SQL" server is not, and either answer his question or ignore it. If the requirements are such that MySQL is *not* appropriate, then probably either PostgreSQL or Oracle is. In any case, the debate is not over whether MySQL is appropriate, because we just can't know based on what's been posted.
  • How is Oracle any cheaper or more open sourced that SQL Server?

    You're assuming I'm the usual slashdot fuckwit who screams Open Source the World at the top of his lungs. My selection criteria are much more rational. Oracle is faster, more robust, and runs on more platforms (most of which are vastly more scalable and reliable) than Microsoft's database. All other things being equal - and they pretty much are if it's between Oracle and the Microsoft database - I'll definitely take the better product.

  • It's really easy to setup a MS Access front end to a MySQL database. Install the myODBC drivers and use them to setup a ODBC connection to your MySQL database. Open an Access file and import a link to each table in the database (the filetype will be ODBC). I knew nothing about ODBC, but after a little RTFMing I found it was very simple, and got it working immediately.

    Here's a section of the MySQL manual you should read beforehand: an ual_ODBC.html

    You especially need to read the section about getting myODBC to work with Access. There are several glitches you'll need to work around.

    That being said, I'd take a PHP or Perl frontend over and Access frontend, hands down...
  • As a DBA, you shouldn't provide one that uses the database. I'm serious -- a production database has no business running SQL that hasn't been vetted by the DBA and run in a test database.

    I agree, 100%. (And I am not a DBA; just a sysadmin that has been bitten by stupid management decisions in the past.)

    What people often fail to understand is that a production database can (and often does) have different requirements than a business database, especially if the term "production" means that it drives a web-based application. If the data model is designed correctly, it is possible to have two entirely seperate databases, with secure data transfers between them at appropriate times (the word "appropriate" is defined on a case by case basis, but it is almost never in real time).

    In the company I work for, we have two, entirely seperate databases. Data is transfered and loaded between the two on a nightly basis (NOT via ODBC; ODBC is very slow, very insecure, and experience has shown that it is buggy at times. The data is dumped, ssh'ed back and forth, and loaded via custom scripts. Perl is your friend ;-) )

    The production database uses replication to two seperate backup databases, and the business database is similiarly protected. Our replication scheme allows us to recover easily from data loss (which has never happened). The dba and our boss (who is a hacker first, and a manager second; a rareity these days) own the data.

    This scheme allows the front end to be customized based on need. The business people are happy with their "point and click" interface, and the dba and engineering team are free to manipulate the data according to production application needs.

    A solid data model can be implemented; all it takes is a great deal of foresight and solid software/database engineering practice.

  • is light years (ok, just years) ahead...

    How long is a light year then? I thought it was shorter than a year, like light beer is less filling than regular beer.

    I do not find in orthodox Christianity one redeeming feature.

  • We did exactly this on a couple of projects at my old employer (Cold Fusion-based web development, most of the web developers were Windows-only). The MyODBC driver works fine, and Access doesn't really care what the backend system looks like.

    There are also 3rd-party scripts to dump a database and definition from Access into SQL for migration into MySQL.

    We did run into issues with how Access treats some data types (I recall time/date as one) vs. MySQL.

    So using Access as a client for MySQL works and is certainly better than using Access alone.

    Longer-term, your employer may be more receptive to replacing Access if you can demonstrate the benefits of cross-platform portability, ease of creating a web interface, or other benefits meaningful to them.
  • OK, fool, that's enough.

    I wrote the statement because the original poster obviously has a lot of feelings about implementing Microsoft solutions.

    MS Solutions might be exactly what is needed, I am not arguing that. But what good does it do anyone for that guy to implement MS solutions, even though he hates it?

    You're so fucking arrogant that you didn't even understand what *my* point was.

    This post should probably be marked -1 flamebait, with a subtitle of (justified).
  • I didn't write that with the company in mind.

    Fuck the company. They can do what they want. I was giving advice to someone who clearly doesn't enjoy implementing MS solutions. He's not doing himself any favors by staying there and slugging it out.

    Life is too damn short to implement MS Solutions if your heart is with the Penguin.

  • Not to defend the practice, but it was my understanding that this was pretty common. Don't Oracle and Sybase extend SQL as well?

    Hell, even MySQL doesn't support ANSI SQL. They support all the impotant stuff (UPDATE, SELECT, DELETE, CREATE), but not triggers, views, or SQL transactions. And they definitely do NOT support roll backs.

    I love MySQL. It's elegant. Its fast. It's Free Software. But I'm going to have to agree with other posters...don't fool yourself thinking that MySQL is going to handle Access well.

    ObJectBridge [] (GPL'd Java ODMG) needs volunteers.

  • I'm in a similar situation: My business runs FileMaker Pro, with FileMaker clients on Mac. I'd like to switch to something else (read: free, non Mac-dependent), but don't want to give up the efficient and easy-to-use front end. (Yes, I've tried FileMaker's Web interface, and it lacks far too much.) Any suggestions?

    I'm running both Mac OS 9.1 and Red Hat 6.2 servers, with Mac OS 9.1 clients throughout. I'd actually consider making them all Yellow Dog Linux clients: FileMaker is pretty much the one essential application I can't give up, and I haven't seen any front ends nearly as flexible or easy to develop for. Thanks,


  • You can also use myODBC to connect to mySQL using ODBC, which, at least in my experience, works more reliably.

    Team FXML's product is excellent for what it does, and has perhaps the best support I have seen for any product, bar none, but it's not as fast or efficient for moving around large amounts of data as ODBC when communicating from a Windows client to a Linux server.

    If you need to do updates to a SQL Server database from mySQL or anything else running on Linux, though, it's a godsend - my company has now pushed literally thousands of orders through it without any problems.


  • Ok, I'll bite, wheres your point'n'click query solution?

    As a DBA, you shouldn't provide one that uses the database. I'm serious -- a production database has no business running SQL that hasn't been vetted by the DBA and run in a test database. The wrong kind of query can bring production to a halt for a manager's whim, and get you in the hot seat to boot.

    That said, it's probably better to provide Microsoft Query and Excel rather than Access for "adh hoc query", if your needs are sufficiently simple. Unless you know how to enter "passthrough" queries, Access insists on reading entire tables and doing the filter/join itself. There is no better formula for poor performance.

    If data need is not simple enough for MS Query, you need to design a data mart and produce preloaded spreadsheets, 'cause the boss won't understand how to get the data to come out. Give the boss a spreadsheet he understands and he'll be 10 times happier than with a point-and-click he doesn't.

    Yes, I am a DBA...

  • Good lord, It's not like YOU have to pay for the SQL server license. SQL server is worlds better than MySQL in terms of maturity of the product and the availability of third party toolsets and software. It's like you have a choice between using a handsaw and an electric circular saw (both of which are available free of cost) and for some reason you want to use the handsaw.

    Not that I don't like MySQL, it is a nice, simple and fast little database that has it's uses, but for any real world applications where cost is not an issue SQL server will be the simpler solution, especially when trying to integrate with an Access front end. Not to mention the fact that there are a hell of a lot more Access developers out there that know SQL server than there are that know MySQL.

    Get over it, use SQL server, or find another job.

  • From this [] page on []:

    Adaptive Server Enterprise for Linux version download

    Adaptive Server Enterprise for Linux version continues to be offered as a free, unsupported release for development as well as deployment. Adaptive Server Enterprise for Linux version is available from the Sybase Adaptive Server Enterprise for Linux version download web page.

    Sure, it's not very recent (latest version is 12.0, but there's a 12.5 beta available for testing), but as I said, it's free as in beer.

    About ODBC support, sorry but I have no idea.
  • That there's another database out there, which is free (as in beer) for both development and deployment on Linux, and it's named Sybase [].

    I've heard that MS-SQLserver is a rip-off of Sybase, but I can't of course confirm this. They are so similar though, that you can use Sybase's client libraries (which are somewhat a brain-fart IMO, but they work somehow) to access MS-SQLserver's data...

    Sybase is somewhat a big monolith if compared to the baredness and nimbleness of MySQL. But it will do the job nicely, I'm sure..

  • Outer join support is included in PostgreSQL 7.1, currently in beta testing. Last I read on the mailing lists, PG 7.1 ought to be released RSN (within the next couple of weeks). So hang on just a little longer and your wish will come true. Some bugs in the Foreign Key support that was added in 7.0.x are also fixed. A lot of the fixed limits (ie. max row size) have also been eliminated and performance is supposed to have improved yet again.
  • Pros of MySQL:

    x Backend may be faster if database is designed and configured properly
    x Online support is better IMHO

    Pros of SQLServer:

    x Compatible security model with Access
    x Can use stored procedures, transactions
    x Uses the Jet Engine instead of ODBC

    Cons of MyODBC:

    x Some data types aren't supported by the MyODBC drivers
    x Clients might have to get the latest Microsoft Data Access Components
  • You know, in most markets, if a product performs badly without a service rep, people would call it a badly performing product...

    In the computer field, people don't seem to question statements like yours.

    I know that some tuning is required for all products, like picking the gear to drive in, but a product that performs so badly in default that it gets beaten by another product in default configuration look really bad.

    And as for the NT thing... For development work, compiling any fairly serious application in VC++ v6, NT is quite unstable. Not only does NT not completely protect memory (a malicious app can clober certain things, so can a buggy app that accesses those directly), but it also doesn't limit ram or CPU usage to provide a certain minimum level of functionality. It doesn't matter if NT technically hasn't crashed, if I hit ctrl-alt-delete and it takes ten minutes to swap until it pulls up the task manager, it's as good as dead.

    I use NT4SP6a and W2kProSP1 on a daily basis at work and neither of them is 'enterprise ready' IMHO.

    You can tweak and tune both so they they perform better, but no matter how well you tweak, they've still got many fundamental flaws.

    I admit I've never used MS-SQL, but based on all other MS products, I wouldn't trust it with a big job (for your corporate website, sure, for your web-based commerce site, hell no.)
  • Both support the transact-sql.

    If you are careful, you can keep your apps compatible with MS-SQL Server.

    Why would you do this? Well Adaptive Server Anywhere is way cheaper than MS-SQL -- $1000 for ten users for a database with transaction logging, ANSI 92 intermediate compatibility, Transact-SQL compatiblity, replication, ultralight deployement etc.etc. It also comes with a web application server that's OK (I prefer Zope) and a very convenient case tool.

  • Nope. The marketing people say it's relational, but it's no more relational -- indeed it is less relational than VB is "object oriented."

    "Relational" in FileMaker parlance means that it implements "relationships". This is not what "relational" means. Relational means that it implements at set of operations that are closed on the set of "relations" -- where relations are relations in the algebraic sense of a set of tuples [e.g. (a,b,c)].

    This closure property is important, since it allows very, very powerful queries to be written succicntly and non-procedurally.

    "Relational" in the FM sense is exactly the model that the relational model was meant to supplant.

    I had a chance to talk to some Claris folks at a MacWorld a few years back when Claris still existed. Their position was that since buyers didn't understand what a relational database was, they were pretty much free to call their system "relational".
  • If you take a creful look at those benchmarks you'll see that the machine was probably being overwhelmed byt the benchmark. They should have at least used a RAID 0 setup so that disk access was fast as possible. In my real world experience I would say that postgres was slower them both ms-sql server and oracle for most things. OTOH it does provide 80% of the functionality and speed of both at a much lower cost (same with interbase BTW). If you can live without that last 20% it's a good thing.
  • A great Idea. As a bonus it will have good compatibily with access.
  • There are a coule of reasons for this.
    One the MS-SQL ODBC driver is cursor based. When you do a select * from sometable or open up a form based on a table sql server does not return the whole keyset it returns a cursor with a few hundred records and waits for further insturctions. Chances are that the mysql or oracle ODBC driver does not use cursors and attempts to return the whole recordset. Check your options and see if you can turn on a cursor based option.

    Secondly yes access sometimes decides it wants to do the query itself instead of sending it to the server. Either turn you query into a passthrough or create a view.
  • You can get an ODBC driver for PostgreSQL. I can attest that it worked fine on NT 4 when I worked with it. It is here []. It's not too difficult; you just install it, then follow the instructions for configuring ODBC on the Windows machine, and create a database account on the Unix machine, which the windows machine can connect to, and you're basically done. It all worked fine for me at work this summer.
  • The above post is somewhat correct. My boss still shakes his head at the cost benefits of Linux. For one particlar problem we have it broke down like this:

    Non-Linux Solution: almost $30,000 equipment and software.

    Linux-Solution: maybe $8,000 worth of hardware.

    We are persuing the Linux solution and it is working so far (we're still in early rollout stages).

    An earlier project had a $14,000 to $2,000 comparision and the implemented Linux solution is working amazingly well.

    The only consession I had to make to my boss was to train my support staff and other sysadmin in Linux. Needless to say the other sysadmin is picking it up really fast due to his background in Unix and the rest of the support staff training is going really well.

    I am tired of the old "How are we going to support this line?" So I'm coming up with my own answer.

    The kicker is my corporation doesn't officially support using Linux for anything. But my boss is giving me support and is tickled at the cost savings Linux affords.

  • I'm doing training on specific setup so its not costing that much. We're looking at 4 operators and 1 admin to train. Even if the other admin was sent to training cost for that would only be about $ 3,000.

    So no, it wasn't a bad decision.
  • Don't you mean can NOT do hot backups. This one should have been an easy find.

    Coded wrong depends on what you want to do for backup. Some sites have the ability to shutdown and do cold backup / or exports for backups.

    The Oracle backup and recovery handbook (from Oracle Press) is an excellent reference in this area.
  • It's really an apples to oranges comparison.

    MySQL is designed as a light-duty database engine.
    Yeah it's faster than MS SQL Server when both are running on a 386 (not that MS will even run on a 386), but MySQL does not scale up to enterprise applications. Run both on a fast quad, dual or even single-chip server, with a large database being pounded by users, and MySQL will hit a brick wall. I saw this happen when an online forum [] with 14000 users (at the time) converted from UBB (using flatfiles) to another BBS system running a MySQL backend on a dedicated FreeBSD server. MySQL couldn't handle the load and just gave up. It's back on UBB now but there may be a Sybase back-end in the future. MS-SQL would handle it just fine also, if it were a Windows box. The point is that for that forum's particular application, even html flatfiles are faster than MySQL.

    But then MS SQL would be overkill for a small application such as the one referred to in the original submission. A simple ODBC driver to MySQL would probably work, and it should work fine with a few dozen people using it. By comparison, the older versions of Jet (native Access) have been known to die with more than, oh, five people using it.
  • Actually, linking to MySQL tables isn't as tedius as described above.

    After defining an ODBC source to a particular database, choose "File:Get External Data:Link Tables" from your menu (or "Link Tables" from right-clicking), choose "ODBC" as your file type, and select your ODBC source. You will then get a multi-select list of all tables within the source's database. For ease of runtime usage, don't forget to select "Save Password".

    Once you link the tables, the are completely accessible as any other tables except that they are read-only definitions. Other than that, if you use VB for manipulation, they are virtually identical in usage from local Access tables.

    True, many posts are written of the limitations, but after two years of integration between Access and MySQL (through a port-forwarded SSL link even) I've not run into anything that made MySQL more difficult to use than Access or SQL Server 6.5/7.0.

    Good luck.
  • "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."

    This also holds true for every other RDBMS out there. Name one database that implements the complete ANSI SQL92 specification, without adding extensions to that specification. Nevermind SQL3.

    (And no wonder. Have you ever tried to get your hands on the actual standards?)

    This is not a defense of Microsoft or Access, by the way. Let's just not resort to the FUD tactics we malign in others.


    That said, my love for free software notwithstanding, I must say that there are things you can easily do with a GUI interface that are difficult to achieve within the page-based metaphor of current web form design. The problem is that you have to pick a GUI, and that tends to constrain you to a particular environment. Web based form designs, on the other hand, can be deployed almost universally.

    I find this dilemma very frustrating. Hopefully HTML (or derivitives) will someday offer a richer language for describing form-based interface layout and behaviour.
  • If you've got serious data handling requirements, DB/2 or Oracle give you a much better migration path. If you're designing a toy, it really doesn't matter if you use Access, MySQL or PGSQL. They're all just toy database systems written for keeping your recipes, your grocery list, and assortment of worthless forum postings.
  • There are many good reasons why a company would want to use mySQL as the DB and Access as the client.

    We have a small database (26 thousand records), requires very fast reads, little is ever added or changed. It provides the journal listings from this page [] (see the alphabetic listing). It does not require transactions. It is currently an Access database on a NT/IIS4 server.

    The staff who edit it want to use Access. In my opinion, Access is a good DB client. It allows "average" users, who are advanced in the wonders of office apps, to edit and maintain the database, and use the find/replace and cut/paste commands they are used to. I can not see any obvious alternative for a client that works under windows and offers the same functionality.

    You will note that the listings on the above page are created by an asp script. The whole web server is on NT. I'm not Microsoft bashing, though this has created a lot of problems. The asp stuff itself just dies (the mtx process goes mad and needs killing). We've spent days looking in to this with no luck.

    We wanted to convert the DB to mySQL (easy, done in a couple of hours), use myODBC and let the staff use Access which uses the myODBC driver. We also re-wrote the asp scripts to php (being stupidly simple in what they do, this took a couple of hours). We would then be free to move our webpages to a nice Solaris box (which is still sitting there doing nothing), apache, php, mySQL, etc.

    Not for love or money could we get the myODBC stuff to work with Access. We could connect to the database using a free (and very simple) client that is on the mySQL website, so the myODBC connection was working, though not with Access. The mySQL site does give some hints on using Access, but these did not help us.

    I'm sure there must be many people in the same situation, i.e. they have a DB used just for some webpages though the staff (not techies, though they do know how to get the most out of office apps) who maintain that information want to use Access (for good reason, it is quite good for maintaining data), but the web server is on a UNIX platform.

    We have had to put the project on hold while we work out a solution that does not involve any functionality loss for the staff who use Access.

  • Have a look at SAP DB []. I quote:
    SAP DB is an open, SQL-based, relational database management system that provides high availability and performance scaling from small to very large implementations. In addition, SAP DB goes beyond relational database technology by offering object orientation as well as support for managing unstructured data. It supports open standards including SQL, JDBC and ODBC; access from Perl and Python; and HTTP-based services with HTML or XML content. SAP DB is platform independent, so users can deploy it for a wide array of projects. Since 1994, the SAP e-Business Solution is available on SAP DB technology. Today SAP DB is being used by nearly 800 customers. On October 5, 2000, at Linux World SAP DB was announced to be made available as Open Source software using the GNU General Public License for the database kernel and the GNU Lesser General Public License for clients and programming interfaces.

    Take a look at InterBase [] from Borland/Inprise as well. I quote from the product overview [] :

    Versioning Architecture for ultimate concurrency readers never block writers.

    Active database, including the most full featured trigger and stored procedure implementation.

    Event Alerters - React to database changes without polling. Exceptional ANSI SQL-92 compliance and full UNICODE support.

    Rich data types - Blobs, multi-dimension-al arrays.

    InterClient - all-Java JDBC driver for low maintenance.

    Designed for business critical distributed database environments, InterBase provides power and flexibility for Internet, mobile, and embedded database applications.

    Scalable from Windows 95/98, Linux, HP/UX, Solaris, and other UNIX systems.



    • Declarative Primary Key
    • Declarative Foreign Key
    • Cascade Declarative Referential Integrity
    • Domain and column-level Check constraints
    • Trigger procedures with the following features:
      • Unlimited triggers per record change
      • Invoked before or after record insertion, deletion, or update
      • Multiple triggers per action, optionally ordered.
      • Forward-chaining (cascading triggers)
    Concurrency Control
    • Optimistic locking
    • Data isolation levels: read consistency, read committed, and cursor stability
    • Shared, and protected lock types for explicit table-level locking
    • Online backups
    • Immediate recovery after failure
    Distributed Database
    • Simultaneously connected databases -limited only by hardware
    • Automatic distributed transaction processing via two-phase commit

  • MySQL is not a production database - it is a toy to play with at home, and I would fire anyone that recommended it as a 'serious' solution.

    As the major shareholder of your company, I would immediately kick you out of the management if you make such commends officially with your name.

    You obviously haven't used MySQL, nor are you aware of its capabilities. The original article is not specific about what they intend to do with the DB, and believe me, there are cases where MySQL is a very good choice. It won't solve all you problems, but if its strengths fits the task, then use it.

    There's no need to play the big boss here, making these uninformed generalizations.

  • and it was a bit of a nightmare. At first we were impressed with how easy and convenient this was, compared to writing a web interface for the MySQL database. But once the data entry people started using the Access->ODBC->MySQL path, ugly things emerged.
    1. Acess always requests at least three rows in a table. So if you're looking at row 1500, it requests 1499, 1500 and 1501. Maybe that's to allow fast load of the 'next' record? If so, it isn't used, because when you go from 1500 -> 1501, Access loads 1500, 1501, and 1502. So basically it just adds useless traffic to your database.
    2. Access seems to get into random moods where it sends tons of selects to the database. This usually happens when it's been idle for a few minutes. It can generate enough traffic to swamp MySQL.
    3. Access has a 'search' feature which data entry people like. If you want to find an employee with first name T-something, you type a 'T' in the first name box on the form, and tell Access to search. Now you might expect that Access would issue a query: SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE 'T%'. Wrong. It requests every single record in the table. You probably think I mean: SELECT * FROM EMPLOYEE. Nope, even worse. It iterates over the primary key and issues one select per row. This swamped our database a few times.
    In the end, we gave up on Access and wrote a perl/web front end.
  • Well like I said, I'm not a db pro, but would security and views not help here? Is it an all or nothing thing? Do you really have to give root access to your data surfers?

    No, you don't have to give the data surfers much access. If all they want is reporting, you could limit them to SELECT privs on relevant tables. But they can still bring the database to its knees with enough SELECTs, especially if they're burdensome SELECTs. You could create a view that 'hides' the join across two tables, but you can't stop the user from selecting heavily from that view. Access has several modes of misbehavior in which it spews a high volume of SELECTs. Also, last time I checked, MySQL didn't have views.
    Once again, can't you just offer up views to limit what access sees (and do the joins to boot)?

    Even if you eliminate the join issue, Access still won't send a proper SELECT that lets the database do the filtering. Rather it will suck the entire table across the network and do the filtering on the client side. A really disastrous design decision.
    The bottom line is, above a certain level of size/complexity/value, you have to have separate 'production' and 'data mining' databases. Access is not the only reporting tool that can threaten the performance of a databse. Lots of homebrew Perl scripts can be just as dangerous. A production database should preferably be protected from arbitrary SQL queries.
  • Shoeboy, you flaming troll! Just in case anyone takes this obvious -1 flamebait seriously:

    Why should he bother to learn MS-SQL? For all the money he spent on it, it should'nt require all that. If it does, he might as well use the free standard.

  • Dont need Wintendows binaries. You can run the server under Linux and let the clients run their front ends on Windows. --- Kinda like with Samba.
  • Thank you for proudly waving the GNUflag in our faces. Pity we're not talking about PostgreSQL.

    PostgreSQL isn't a GNU product. It's distributed under the UC Berkeley license instead. Besides, this thread started on the topic of how worthless MySQL is for complex databases, so someone had to pipe in with alternatives.

    btw, if Oracle released a report stating that they had benchmarks proving that their DB was the best at everything, but you couldn't actually see those benchmarks, would you give the claim any credence? If not, why should we trust someone else's rumor? Just because it says good things about an OS application?

    Both Oracle and Microsoft prohibit vendors from publishing benchmarks using their names without their prior approval. The original source [] does, however, show the benchmarks with Oracle and Microsoft's offerings marked as Proprietary 1 and Proprietary 2. However, on the section of how the benchmark was done [], they list among other things, the version of the software the used, with "Proprietary 1" being at version 8.1.5 (probably Oracle), and "Proprietary 2" being at version 7.0 (probably Microsoft SQL Server). The amount of 'tuning' they did for PostgreSQL is quite a bit more than they did for any of the other vendors, so it's entirely possible they might've had slightly higher performance (especially on the TPC-C benchmark, where PostgreSQL, Proprietary 1 and 2 were neck and neck with the three products still tying at 100 users. The test probably would've been more interesting with 200 or 300 users, since they probably would've started to deviate a little).

    Just about the only real features that are missing from PostgreSQL that it's competitors have is replication and full text indexing.

  • MS-SQL Server (I think version 4.52 or something like that) was built in a joint effort with Sybase a long time ago, because Microsoft didn't have the expertise to develop a DBMS. Then at some point, they concluded the partnership and split the code. So the original Microsoft SQL Server was based on Sybase's DBMS. But really, starting from MS SQL Server 6.5 on, it's been mostly Microsoft's own merits (take that whichever way you like).

    So if you say it's a rip-off, then yeah, I guess you could say that, though it was consented (though I'll bet Sybase regretted it), if that makes sense.

  • Someone once told me that you don't ask for permission to install Linux, you ask for forgiveness.
  • I have plenty of MySQL databases connected via an access front end using ODBC. It's a good way of giving end users a way to update data without make new front end. If your Access front end needs more than the MySQL ODBC driver requires then don't do it. However, if it doesn't then go for it.

    Basically we've found the following when it comes to stability:

    Sun (SunOS Solaris)


    These are general stats collects from several groups at large midwestern telco over a period of three years. Generally speaking NT bases systems like the trouble Ticketing and some minor ordering front ends that are MS based all had tracked down time of at least 3-4 times as great as the Unix systems.

    That being said, MySQL can be used fine for production, but it's qualified. Although ROW LOCKING is now possible with MySQL we'd like a little shake down first. We've found MySQL works great when you have a lot of similar SQL queries. Storing stats for instance works very well on several gigs of data a day. On the other hand, I wouldn't count on it if you had a lot of interactive queries. Oracle and DB2 does this better. That being said a lot of performence issues can be taken care of with good programming. Creating temp tables, and good use of unions and joins. Recently I saw an perl programming write some statics programs that had queries that took 5 minutes to run per data set. After going through his code the same data could be ran in less than 20 seconds with better queries.

    I think it's importent to note that dispite Oracle, DB2, and MsSQL scaling better, we have never had a MySQL fail for software reasons. Only hardware. And even with those hardware fails, if the HD was still there, the data was too. That's more than can be said with Oracle, DB2, or MsSQL.
  • 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.

    And that's a rather idiotic blanket response. In fact, for many Web applications, MySQL will outperform any major commercial database engine. This is because MySQL simply cannot be beat for heavy-read database performance. So the question for the backend is really: What kind of transactions do you need to support and what is your budget? If you already have the licenses or if you have some decent transactional needs, then Microsoft SQL is a good answer.

    As far as the client end goes, why the hell is a database needed on the client? Access is NEVER an answer for anything, except maybe as a toy for learning about databases. As I heard someone say about C++ once, if Access is the answer, then it must be a very strange question.

  • Bloody hell, I hate being called a liar. But seeing as how you're too damn lazy to look yourself, or you never learned how to use a search engine, I'll fill in the blanks for you. Q169395 - PRB: Thread Safety for DAO/Jet 3.5
    Amusing excerpt:
    The Jet engine was originally written as a stand-alone desktop application, and originally conceived before thread-safety was as important as it is today. Jet 3.5 itself is not thread-safe.
    Q222135 - ACC97: Using Microsoft Jet with IIS
    Amusing excerpt:
    The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur.
    Look up the entires for further information. Now admittedly, using SQL Server on the backend helps, but at that rate, you might as well code a VB front end instead of an access one.
  • sounds like your motivation here is to not use Microsoft at all costs. As a hobbyist, and for my own systems, I agree. But when you're taking a paycheck you have an obligation to deliver the best possible solution. Not only the technically best, but the best overall, including political and business considerations.

    SQL Server is actually a very, very good product. Probably because it originated outside Microsoft :), but for whatever reason, it is light years (ok, just years) ahead of MySQL. SQL Server has better XML support, higher performance for most applications, distributed transactions, good (but not great) clustering/failover support, and vastly better administrative tools.

    Now, there are reasons not to use it. Like, Oracle is better overall. Or MySQL is much cheaper and good enough for smaller databases that don't need enterprise-level functionality, support, or reliability.

    Additionally, if your boss (and presumably company) is going in a Microsoft direction, are you really doing anyone a favor by going with something else? I mean, yeah, we all hate to give Microsoft more money. But what happens if you leave the company, and you've build this Linux/MySQL backend that's not in line with the company's strategic direction?

    I'm sure I'll get plenty of hate mail. And believe me, I do my best to promote alternative solutions to the Empire when it makes sense. But if an MS solution makes the most sense in a particular situation *for a paying client*, I go with it. And SQL Server is the *one* MS product that I've frequently seen as the best solution given budget/hardware/DBA salary constraints.

    So anyways, if you want to be a crusader, by all means go with the rebel solution regardless of how much sense it makes for the business. But if you want to deliver the best application, do the research and figure out what really makes sense for your particular environment and application needs. And don't be surprised if SQL Server actually looks pretty good.

  • So he wants you to design the GUI in access, yet user another system to store the data?
    Thats just sooo stupid for so many reasons
    If you can use a database of your choice on the back end , cool. But is you have to use a MS product for the front end you need to explain to your boss that it should be VB.
    Access is horrible to work with, and it is not very easy to get low level with, which is important if you like to tweak your system for max performance.
    I have done lots of access work, and VB work and I have seen every wrong thing that can possible happen, and using access as a front end is always the biggest mistake. Unless this is for less then 15 users, then it would work until you ned more useres. If you can choose your back end, try informix. I have yet to see a database that can come close to its speed with large database's.Plus, you can get a free copy of informix for linux. CIAO
  • It's been my experience its not whether its a web page or an app, but the design that matters.
    I can do anything with a web page that can be done with a VB GUI.
  • MS SQL Server has a truly nasty license and a steep price that goes up as you use it more.

    Plus it has tons of bugs: Views essentially dont work, The error reporter says thing like "General network error" for a syntax error, and its pretty easy to cause it to crash. If you try to use ansi outer joins, aggregate functions, and subqueries together it craps out. A buggy stored procedure can freeze the whole server up.

    Its stored procedures cannot return rowsets to other stored procs, and moving from 6.5 to 7.0 is very difficult because it wont transfer identity prorerties correctly.

    Working on budget software at a government contractor I developed for MS SQL SERVER versions 6.5 and 7. (7 is alot better but far from perfect). I think I ran into every major bug in them. MS support was useless- their knowledge base simply told us it was a "known issue".

    Now MySQL isnt nearly as feature-full as either, but I prefer posgresql myself. As soon as they get outer joins working I'll never look back.

  • Yes, a setup.exe is available.
  • I've been working with MSSQL 6.5/7.0 for about 4 years now, mostly on 7.0 (so my answers will mainly apply to 7.0, can't remember 7.0 that well). Let me comment on some of these statements.

    Views essentially dont work,
    I use views all the time, never seen one that didn't work properly.

    The error reporter says thing like "General network error" for a syntax error
    Wrong. I would say MSSQL's error messages are above average in the software world for descriptiveness. You only get the "General network error" messag when there's... guess what... an actual NETWORK ERROR. Not too confusing from where I sit but hey, YMMV.

    If you try to use ansi outer joins, aggregate functions, and subqueries together it craps out. A buggy stored procedure can freeze the whole server up
    I'll have to try and get this to happen. I've never seen that happen before... and I've written a lot of buggy stored procedures. :)

    moving from 6.5 to 7.0 is very difficult because it wont transfer identity prorerties correctly
    I only moved one database from 6.5-->7.0 and that was a while back. I don't remember identity field problems. Even if this does happen though, it's easy to force the identity value for each table. Pain in the butt, but shouldn't take more than about 10 seconds per table to force the right value. And it's not like you need to migrate every day- should be a one-time thing.

    Its stored procedures cannot return rowsets to other stored procs
    This true, and it's a pain... but you can get around this by storing the rowset in a cursor and/or a temp table or just using output parameters for the innermost SP's. (yes I know temp tables and cursors are expensive).

    After about three years of MSSQL 7.0 use I'd have to say it's an awesome product, honestly. Stable, fast, and incredibly feature-rich. I've been keeping an eye on PostgresSQL tho. I think that's going to be a match for MSSQL pretty soon, especially since MSSQL is overkill for most user's needs. []
  • There are ODBC drivers [] for MySQL that can be found over at Freshmeat [].

  • I coded frontends with the MySQL Win ODBC drivers (more than 1 1/2 year ago) and they did work as advertised. 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.

    In other words: yes, go for it. It works. And in the meantime, try to convince your boss NOT to use Access as a frontend, but Visual Basic. You'll get a much leaner and meaner application, and it's just as M$. Access as a front-end is a dumbed-down VB.

  • Then you could use it (Interdev) to give the database a web front end. Operate it via the browser. Password it, etc. This could be on the intranet, with another front end for web use as desired.

    Bonus points! - The boss could work from home.

    In Fact, you could sell the boss on this, saying that this is what you use instead of Access if you want to be "really serious". Acess is for the low end business, and you need the MS industrial products, etc.

  • I would like to point out the following, and have you consider it please.
    1. He already said he hasnt tried ODBC, but also pointed out that he wasnt willing to by asking us to first.
    2. Their workplace is happy on Win32/MacOS. They are unlikely to fire their staff because they dont know what their boss feels is an inferior OS. (Not neccesarily my opinion)
    3. Solid transaction support is a *must* have for anyone running any sort of financial application. It *has* to pass the ACID test, with no exceptions.

    Pertaining to your points:
    1. SQL Server comes free with Win2K
    2. FUD.
    3. True. But not neccessarily relevant once you may have to possibly take into account my point #3.
    4. The boss SPECIFIED the windows clients, this means the duct tape is connecting to the db. Remember, he has no choice about the client, so whatever back end he chooses *has* to work well.
    5. You can write stored procedures to back up *any* SQL database to file or to another SQL database. Its not *that* hard.
    6. The reverse is only seldom true because once they know linux you no longer classify them as a Win32 tech.
    7. Cant argue there, that's a very good point.
    8. Companies need accountability, and paid-for support that will be there at 3am in the morning "until its fixed". Not someone on IRC that says "I'm tired, I have to go to bed". It is foolish to ignore a fast growing OS, but it is much more foolish to ignore your bosses demands. If 90% of the office knows Windows, then it is by far better to use windows than to rehire (more expensive) unix people, or pay for (Expensive) training for the windows people (who may or may not "get it")

    Sometimes the "right thing" is not always clear. I'm not saying that you're wrong, or that the other guy is right, but please look at this from a non-defensive position. There are clearly more things at stake here than chooising a "free" database.


  • Thank you for proudly waving the GNUflag in our faces. Pity we're not talking about PostgreSQL.

    btw, if Oracle released a report stating that they had benchmarks proving that their DB was the best at everything, but you couldn't actually see those benchmarks, would you give the claim any credence? If not, why should we trust someone else's rumor? Just because it says good things about an OS application?

    That's hypocrisy, my friend.

  • 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.

    Not to defend the practice, but it was my understanding that this was pretty common. Don't Oracle and Sybase extend SQL as well?
  • One of our clients had to downgrade to Access 95, but it works. Probably something with differing MDAC drivers or some other MS data components not being right. :(

  • Yes it can be done. I work in an office where we heavily invested in Access95 and have seen little reason to "upgrade" since many of the new features can be recreated in earlier versions with a little VB and sweat.

    We bought one copy of 97 to test with it and installed in on out NT 4 server along with Apache and PHP. Once everything was running properly, I just fired up the ODBC driver and everything worked perfectly - a little slow since I've never tweaked it or gotten rid of IIS. Far-flung employees can dial in and check in via a browser. Access as a client over a phone line is a nightmare.

    I hope it works best for you. If we were starting over again, I'd seriously consider using front-to-back opensource. But Access clients allow you to edit several records in a single step (without refreshing the page twelve times) which makes users happy.

    And as we all know, happy users is what this is all about. []

  • I use Access as a front end to a mySQL database on our local linux server, using an ODBC connection just as if the database were on a remote server. This setup is actually faster for our database (main table has ~50K records in 30 columns) than it was when Access acted as the "server", that is, when there was one central Access database on the NT fileserver.

    The big advantage is that I don't have to train people to use a new program, and I don't have to write a new script every time somebody comes up with a new problem, since Access will generate SQL queries (albeit not good ones, but this a very small DB).

    What we are actually doing is having our users access a local copy of the database and doing a daily synch with the production DB. This prevents excess load on the web server, and prevents them from scrweing shit up.
  • True, MS-SQL can be accessed by Sybase dblibs but you only have access to SQL 6.5 features. (MS rewrote the codebase a couple of years ago (starting with MS-SQL 7) and introduced a shed load more features that need their (Win32 only....natch) odbc/oledb netlibs

    Sybase have recently released a new preview with an inbuilt xml-db - looks pretty cool if you want to avoid shredding inbound/outbound XML into relational tables, not sure if they support Quilt tho....
  • I love PostgreSQL. It's fantastic, and serves my needs reasonably well, except for one thing that really bugs the hell out of me...

    Support for outer (left) joins. I mean, I want to be able to do a

    SELECT table1.x, table2.z FROM table1, outer table2 WHERE table1.y = table2.y


    SELECT table1.x, table2.z FROM table1 LEFT JOIN table2 ON table1.y = table2.y

    rather than having to resort to something stupid like this:

    SELECT table1.x, table2.z FROM table1, table2 where table1.y = table2.y
    SELECT table1.x, NULL as z FROM table1 WHERE table1.z NOT IN (SELECT z FROM table2)

    I mean, come on. PostgreSQL is fantastic because it supports transactions and triggers and just about every other feature that I REALLY want, but it lacks something like outer joins, which makes it difficult for me to work with.

    At the present time, my company's using Informix for some stuff, MS-SQL for others. Porting my app to be able to work with PostgreSQL would require some of the above to work.
  • by IntlHarvester ( 11985 ) on Sunday March 04, 2001 @12:11PM (#385864) Journal
    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.
  • by Shoeboy ( 16224 ) on Sunday March 04, 2001 @03:34PM (#385865) Homepage
    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.
  • 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!
    Jim in Tokyo

  • by hey! ( 33014 ) on Monday March 05, 2001 @04:59AM (#385867) Homepage Journal
    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.

  • by 1010011010 ( 53039 ) on Sunday March 04, 2001 @05:39PM (#385868) Homepage
    No, no! He obviously wants to run the postgres setup.exe under wine! ;)

    - - - - -
  • by runswithd6s ( 65165 ) on Monday March 05, 2001 @12:18AM (#385869) Homepage

    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:

    1) Chose an RDBMS that allows VIEW's, STORED PROCEDURES, or other optimizations that allow the RDBMS to generate and store query plans

    Plans are essential tools to efficient queries. Your database management system must decide which indexes it needs to use to fulfill your requests. VIEW's and STORED PROCEDURES are excellent examples of pre-compiled query plans.

    A second advantage or use of VIEW's is that of security. VIEW's hide the details of a normalized database and can limit the records viewed from larger datasets. If your design includes ANY type of privaleged users, VIEWS will help you implemnt the security model immensely.

    2) Choose an RDBMS that supports TRANSACTIONS

    Again, that eliminates MySQL. (Sorry, but it's true.) Some people try to argue that transaction are unnecessary. This may be true with an unnormalized database schema, but when you start to separate the data into its atomic parts, a transaction is essential in tracking the addition, subtraction, or alteration of a set of data that spans multiple tables or records. It is ESSENTIAL for database integrity. I don't trust a program to faithfully rollback a transaction on its own. Computers have hardware problems, programmers blink their eyes as they're searching for bugs. Having an RDBMS that supports TRANSACTIONS isn't a convenience, it's a requirement.

    3)Design as many "canned reports" as possible.

    There's nothing more frustrating than trying to design a dynamic query builder. Not only do you loose the advantage of having pre-planned queries, but you have also worry about how to best deliver an interface flexible enough to build these dynamic monstrosities. If you don't believe me, take a look at the Bugzilla query page...

    4)Choose an RDBMS that supports CURSORS

    "Why," you ask? Simple answer. Ever wonder how you're going to limit the result set of a query, especially that one that likes to return 10,000 records even though you will most likely find 50 records to be overwhelming to display on your carefully designed UI? CURSORS provide you a way to page back and forth through a result set withough having to requery the database using MAXROWS options. You may have to hit the database more often to retrieve the results, but the server no longer has to compile a query plan and collect the resultsets. It simply holds the data for you until you need it. Who wants to pass around huge result sets, sucking up bandwidth and memory if you don't need it?

    Guess what... This counts out MySQL once again.

    5) Do NOT design the business-logic or rules-logic into the CLIENT!

    The principals of simple design logic here. If you design your software around the client, then when the rules change in the game of policy (you know, the thing that the "pointy haired bosses" change on a whim, you have to upgrade ALL the installed clients. Guess what? This counts Access out.

    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 [] or [] if Java+Appserver+RDBMS+web raises an eyebrow or two.


  • by xant ( 99438 ) on Sunday March 04, 2001 @01:55PM (#385870) Homepage
    As has been pointed out here numerous times, MySQL is missing important features, especially in the area of atomic transactions, that SQL Server has always had. SQL Server is not a bad database - because it was originally based on Sybase, which is a good database. The bad part about SQL Server is that you gotta run it on NT.

    Go with PostGreSQL instead.

  • by Edgester ( 105351 ) on Sunday March 04, 2001 @12:12PM (#385871) Homepage Journal
    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.
  • by pi_rules ( 123171 ) on Sunday March 04, 2001 @10:05AM (#385872)
    I'm part of a team who works with PostgreSQL & ODBC connected to ColdFusion on a dynamic website.

    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 :). 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.
  • 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.
  • 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 agree that MySQL isn't ready for primetime yet, but it isn't the only open-source free RDBMS.

  • by LaNMaN2000 ( 173615 ) on Sunday March 04, 2001 @10:25AM (#385875) Homepage
    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.

  • by mgkimsal2 ( 200677 ) on Sunday March 04, 2001 @10:03AM (#385876) Homepage
    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.

  • by mgkimsal2 ( 200677 ) on Sunday March 04, 2001 @10:30AM (#385877) Homepage
    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.

  • by AlphaOne ( 209575 ) on Sunday March 04, 2001 @10:07AM (#385878)
    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.
  • by Hiro Antagonist ( 310179 ) on Sunday March 04, 2001 @10:38AM (#385879) Journal doesn't support transactions []? Check your facts first!

  • by Darkstorm ( 6880 ) <> on Sunday March 04, 2001 @11:41AM (#385880)
    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.

  • by PD ( 9577 ) <> on Sunday March 04, 2001 @10:00AM (#385881) Homepage Journal
    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.
  • by Malcontent ( 40834 ) on Sunday March 04, 2001 @10:15PM (#385882)
    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.
  • by divec ( 48748 ) on Sunday March 04, 2001 @10:32AM (#385883) Homepage
    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.
    Hmmm. There are reports that PostgreSQL v7 matches Oracle at its own benchmarks [], but that these benchmarks can't be published cos of a clause in the license for Oracle. So yeah, a free license isn't nearly as good for marketing as one which allows you to ban publication of benchmarks.
  • by dirk ( 87083 ) <> on Sunday March 04, 2001 @10:31AM (#385884) Homepage
    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.

    Except that isn't what is being proposed. Access is just being used as the front end. IT will work fine for that (as I can attect as we have numerous dbs running with an Access front end and a MSSQL backend. Access is definitely not good for multiusers dbs, unless you have a backend to it.

  • by psocccer ( 105399 ) on Sunday March 04, 2001 @12:48PM (#385885) Homepage
    I didn't see this mentioned so I though I'd point it out. When getting started with SQL I was pretty familiar with MS Access, and I noticed they had an ODBC driver. I though 'wow, now I can create an application in an afternoon with an SQL server back end!' So I plunged in. Things worked well for the most part, but there were some irritating problems. The biggest problem I ran in to is the case of working with auto_increment fields, which if you use MySQL you realize is a lot.

    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.

    1. When you key in the order information, since the ID is auto_increment access may not find the proper record to link to the subform.
    2. For the subform, if you add say 5 items and need to edit the 3rd, you may or may not be able to depending on if Access can find the row.

    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.

  • by raju1kabir ( 251972 ) on Sunday March 04, 2001 @11:58AM (#385886) Homepage
    but be warned using Access as a front-end to another database type. I have a few users that have Access 97 setup as a front-end to our Oracle8 and SQL Server 7 databases, and Access will start giving ODBC errors whenever the tables get sufficiently large, and then the whole thing shuts down.

    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.

  • by 1010011010 ( 53039 ) on Sunday March 04, 2001 @10:40AM (#385887) Homepage
    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)

    - - - - -
  • by Zeus305 ( 104737 ) on Sunday March 04, 2001 @10:37AM (#385888)

    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.

  • by edmz ( 118519 ) on Sunday March 04, 2001 @10:07AM (#385889) Homepage []
    "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."

  • by TeamFXML ( 125269 ) on Sunday March 04, 2001 @10:04AM (#385890)
    If you have to use Access, you can still connect to it via PHP or Perl from Linux using ODBC Socket Server, located at []

    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.

  • by bellings ( 137948 ) on Sunday March 04, 2001 @10:55AM (#385891)
    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.
  • by danlyke ( 149938 ) on Sunday March 04, 2001 @10:23AM (#385892) Homepage
    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.
    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.
  • by Trevor Goodchild ( 187368 ) on Sunday March 04, 2001 @10:18AM (#385894)

    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.

  • Using the MySQL Win32 ODBC driver, it is possible to define a data source in Windows that MySQL can attach. The setup is a bit tedious and your capabilities are limited. You cannot (that I know of) 'say' connect me to the whole database, rather, you have to, in Access, define a virtual table for each table in the MySQL database you wish to connect. You can then use Access to create views, predefined queries, reports, and so on. Keep in mind that, other then manipulating the data directly, all your work resides in your Access DB front-end.

    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,

Today is a good day for information-gathering. Read someone else's mail file.