Slashdot Mirror
Microsoft Access As A Client For Free Databases?
"Can this be done effectively? Is there any good documentation on connecting Access to Non-Microsoft DB servers in general, and MySQL in particular? No 'ODBC RTFM' flames, please; I'm looking for something a little deeper here."
I'm not very familiar with ODBC beyond it's basic use, so I really can't answer this question, but might this be possible if the MySQL server were somehow treated as an ODBC source? It would be tremendous coup if somehow the power of the Access front end could be used with MySQL (or PostgreSQL, or mSQL or any other open sourced RDBMS)? MySQL does have some ODBC functionality and as well as other ODBC related links on their site.
For those of you who want to immediately play around and see if this is possible, you can find the MySQL ODBC driver, here and a PostgreSQL ODBC driver here.
(My apologies. The story went live right as I was still editing it to include links in the last two paragraphs...)
mysql does act as an odbc source quite nicely. heck there are open source odbc drivers out there for it -- look on freshmeat/google.
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).
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...
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.
Yes, please do got to tpc.org 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.
#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.
Nah, it just corrupts the data/
The reason that Oracle prohibts publication is that database tuning is a black art and makes a huge difference in what the performace is.
Every database wants to claim to be "Faster than Oracle". That would mean that Oracle would need to send engineers out to tune for every stinking little benchmark that someone wants to run. Not worth their time, especially when it something like Postgres that is absolutely not a threat to them.
That's simply bullshit.
If the system is engineered in such a manner as to make proper performance tuning more difficult then that is as much a relevant technical issue as anything else. Such a situation is no excuse to "ban" benchmarks. Infact benchmarks would only force the disclosure of a situation with real customer impact.
Or put more simply: one man's benchmark is another's mission critical application. Systems should be judged based on how they can be expected to be deployed rather than some vendor controlled laboratory situation.
A Pirate and a Puritan look the same on a balance sheet.
Ok, seriously... Why? The backend to access is based of of Sequel. Its the good part. I have no idea why you would want to replace it with MySQL.
I'll second that. Besides, this can be answered with about two minutes of looking at the MySQL home page.
* And remember, it's spelled N-e-t-s-c-a-p-e, but it's pronounced "Mozilla."
i don't know why kdb and k don't get more press around here. kdb is easily the best db i have ever seen: it is incredibly fast, easy to setup, can run on your palm (the download is 150k that unpack in less that 300k), and has a killer extension language, k. actually, k was the language used to implement kdb (it is an apl derivative). it is incredibly well done. it is free (gratis) for personal research and a small fee for corporate use.
www.kx.com
"Before Kdb, we were waiting 60 to 90 seconds for responses to queries on our system," says an IT Manager for a large private hedge fund. "I decided to try to speed things up with Kdb, and was able to build a prototype trading system in Kdb in one week that ran 1,000 times faster than what we were running at the time. That convinced us to add a significant historical component to the real time system, and even with billions of rows of tick data our response rates are still out of this world."
"In this tick database system, Kx gets sub-second query response rates on 2 years of NYSE tick data for all publicly traded stocks - over 2.5 billion trades and quotes."
Using mysql and the windows mysql ODBC driver to use Access as a client is fine. But why don't you do both. You can do what your boss told you to do plus show him an alternative that you think is better. If you can convince him that web based is the way to go then that will be great. And if you fail at your attempt or he thinks it is not a viable altenative then ,oh well, you allready have what he wants. Either way you win.
Can you see Iron City here?
1 Reason?
Cheap company. Free database.
Check this page from the MySQL manual. It describes how to setup MySQL as an ODBC datasource. If you can manage to get that to work, you might be able to do what you want. Granted...I wouldn't recommend it because any way you slice it I can't imagine it not being a nightmare to implement, manintain or troubleshoot.
-- Are you an EFF member yet?
His job is also to make recommendations to the boss when he might be able to save the company money, time, or problems.
With Debian it sure does. apt-get install postgresql (although Debian installs it by default it would seem... at least debian 2.2 potato from CDs)
the real at&t mix
Lets see here:
"God fights on the side with the best artillery." - Napoleon, Marshal of France - speaking truth to power
Speaking of PostgreSQL, is there anyone one can get an already compiled, ready-to-run-setup.exe version of it?
./configure; make; make install.
It ships with Red Hat 6.x and there are RPMs available. If your machine has the right libraries, compiling it is a piece of cake: tar -zxvf postgresql*.tar.gz; cd postgresql-<version>;
Generally with JavaScript, although XUL can do a better job. Also, if you're working in Windows land, where every user is considered a moron, you would be doing data entry ``wizard'' style, with very few questions per page, so you could probably find an excuse to make a new page after the first combo box and before the second, thus bypassing the needs for client-side scripting.
Got time? Spend some of it coding or testing
That needed phasing out from the day it was invented. Even after buying Fox and integrating some of the speedups from FoxBase into the JET engine, it was still one of the worst-chosen ancronyms in history.
Sad that it took - what? - about eight years of customers screaming at them before M$ decided to go client/server, and you can bet there will be JET-oriented backwards-compatible barnacles in the code until doomsday, to say nothing of new ASP-ish remote-disablement code.
Access is one of the better practical arguments out there for Linus Torvalds' approach to backwards compatibility.
Got time? Spend some of it coding or testing
It seems to me, that (at runtime, anyway), we're pretty close to dealing with stone soup here. If you're only after a front end, why not use (now here's the brilliant bit) a front end? Why use the saw from a Swiss Army Fork instead? There are bazillions of nice, neat GUI frontends, from Python and Ruby RAD tools through PERL-based Web front-ends. Go visit FreshMeat and have a bit of a browse.
Got time? Spend some of it coding or testing
The tool he wants is here and the ODBC so widely feared above ceases to be an issue.
...or in other words, Access sucks over a network?
PS, Access will often lock up or lose the plot if you truly are working with large amounts of data. Better to do data reduction at the server end first, it increases your survival prospects.
Got time? Spend some of it coding or testing
You can actually put more than the database in one file, but it means that when the one file becomes corrupted, you're sunk. Also, the ``connect directly to file'' bit does actually need whole mess of other stuff out there in DLL hell - and so MS-ASP - and so IIS - and so Windows on the server. The file doesn't know anything, you're not really connecting directly to it, but to a server on the ISP, albeit possibly built into ASP.
Yes. And with PostgreSQL. Copy the whole shebang onto a virtual disk and deploy that. You can even compress it for transport (zeroing the unused chunks helps compression lots). You can include a full MySQL (or PostgreSQL) implementation on the VD and run either or both as a user, and connect from any webserver, not just InternetInformationShredder. The VD trick requires special software under Windows, and usually mount permission under (Li|U)n[iu]x so you can do:
Unix can outpackage Windows any day. With three utilities tied behind its back! (-:
Got time? Spend some of it coding or testing
The PostgreSQL people are currently discussing the most elegant way to back out the 500,000,000th transaction, so I wouldn't let sheer volume frighten you... except that I would be keeping good and regular backups of that Access DB handy, and would hurry to have another solution (any solution that works) to hand for the day Access spits the dummy for the final time.
Ten simultaneous transactions is no big whoopee, even through ODBC, unless those ten don't count a horde of unmentioned read-only internet users. If you're pulling stuff out of the Web end, you can avoid ODBC anyway, and use a PERL or PHP module instead (even under Windows).
Using PostgreSQL instead of Access as a back end will have you using something with less proprietary SQL extensions when the time comes to seriously scale up. That means that dropping in (say) an Oracle server shouldn't be as much of a big deal as replacing an Access or MS-SQL server.
Got time? Spend some of it coding or testing
Want to bet on that? (-:
MySQL does suck in some areas (row-level locking is a new feature, for example, and until MaxSQL, transactions will be only a dream) but unlike MS-SQL, it's relatively easy to improve. Likewise PostgreSQL, which already does a significant number of things that MS-SQL doesn't, and lots of them much faster as well - and is easy to improve.
But if your application is mostly reads, and structurally simple (many, even most, DB apps are), why on Earth would you bother with the expensive, proprietary and resource-thirsty MS-SQL, even now, even three years ago? So you have to rewrite two or three JOIN statements? Oooooh, whip me with chains, that is so difficult a task, I impress even myself with my ability to complete it! Big fat hairy deal.
Got time? Spend some of it coding or testing
ODBC was designed just for this situation. A place I worked for a while back was mainly an NT shop, but I had a MySQL database up for a project I had written in perl. One of the NT people needed to be able to grab some data (names and email addresses) to insert into the Exchange server's address book. I just had them load the MySQL ODBC driver for windoze and within Access they selected the MySQL datasource. From the front end, there won't be any noticable difference. (except that really annoying problem where the Linux DB tends to run faster)
A slip of the foot you may soon recover, but a slip of the tongue you may never get over. -Benjamin Franklin
That's because Access does page-level, not row-level locking. Yeah, it sucks.
--
Peter
I wouldn't consider "a Windows version" to be a solution to *anything*, but that's another matter...
--Tom
Tom Geller
Speaking of PostgreSQL, is there anyone one can get an already compiled, ready-to-run-setup.exe version of it? I don't see widespread adoption being possible if using it requires compilation, Cygwin, etc. (That stuff is fine for developers, of course.)
Interbase also has some advantages.
We run Access as a front end to our MS SQL7 backend and have a separate system (parts of which are synched using VBScripts), and here's what we noticed:
.MDE). Sourcesafe supposedly handles individual modules, but it's unmitigated crap on a DB of this size (20 mins to save a form, wierd errors, etc.), and so you're stuck doing versioning manually.
1. Don't sync anything with VBScript and ADO if you want to keep your sanity. The scripts' error handling is atrocious--Active Perl on Win32 is MUCH better if you have to glue these backends better.
2. I can live without many of the things I take for granted in MS SQL (transactions, subselects, etc). The only thing that's REALLY annoying to have missing is triggers. Without them, you basically have to write quite a bit of code in the front end that Access is pitifully bad at handling sometimes (especially with bound recordsets).
3. We have three programmers (well, two FTE) working on the Access front end at any given time and it has major issues once you pass the basic levels of usefulness, even if it's not responsible for storing any data. Things we've noticed:
A. No reasonable way to handle version control once the DB gets large (front end over 10MB when "compiled" to an
B. It doesn't handle versioning of ActiveX components well at all. Any variance, and your Forms break with cryptic errors. Yet using OCXes and DLLs are your only hope of modularizing things when you're using an Access frontend.
C. MDBs and MDEs corrupt easily in the hands of users. We have an "update" script that individual users can run on their copies of the frontend, which is a necessary evil. Good thing remote offices can use ICA to run the DB frontend. It would be hell on our bandwidth otherwise.
D. I could probably go on for five solid pages on this topic alone. The only reason that we're still working with Access at all is that there has been six years of development on it, and there are a LOT of vestigial forms that rely on bound recordsets that we don't have the time to rewrite yet. We're using OCXes a ton, and anything that's still in Access is being done unbound now to make it easier to at least port to VB (the things that are really UI-intensive) or the web using ASP (reports, especially).
The lesson here is that you shouldn't develop a DB front-end in Access if you're not going to store the data there. Basically, if you are then the app probably isn't going to be used by more than a few people and Access is proabably OK. If not, then you're serious enough that you should be using something else. VB's a good start if you must, PHP is better.
Depending on your UI requirements, I'd start with a PHP/MySQL or PHP/MS-SQL combo. Write some OCXes if you need to (and have your guys use IE or write a VB wrapper for them). If you're not that serious (and your boss's FileMaker background is holding you back--Access and FM are more similar than not), you could try Access/MySQL or Access/MS-SQL, but PLEASE save yourself some sanity and avoid binding the forms straight. You might not want the complexity of reading/writing/syncing the data right off, but you'll save yourself tons of grief when you boss decides that he wants column X updated if and only if it meets some wierd constraints, the user clicks yes on a confirmation form, and is cancelled if they click no (it happens more often than you think when you're starting out--business logic is never as simple as it seems).
"Time flies like an arrow; fruit flies like a banana." --Groucho Marx
Can't people please just stop preaching their own opinions sometimes and be helpful or shut up?
Not congruent with my experience, but we haven't tried it again with Access 95 or later. Of course, I'm limited in my experience to using Access with Oracle. YMMV with another DBMS (especially SQL Server).
1- The clients get up to their resource caps.
2- There's dozens or hundreds of them.
3- You try logging your boss off after 20 queries, like it or not :-).
If you let the user in with user-defined SQL (like in Access) you're unlikely to get away with limiting his view to small/simple tables, and you can't stop him from trying to join whatever he sees. There are GUI tools for that (Oracle Discoverer leaps to mind) but they cost money, probably as much as using Access on a "reporting" server, so it's a matter of preference. The real problem with them is that the boss is already tool-focussed, and is unlikely to accept learning a new tool.
Better to make offline Access or Excel presentations of the data. They can be quite interesting and sufficient in many cases, and permit the boss to (ab)use his own computer rather than mine. I find bosses usually understand spreadsheets better than databases, but there are some functional users that get into Access.
An earlier project had a $14,000 to $2,000 comparision and the implemented Linux solution is working amazingly well
how much do you expect the training costs to have been? less than $12,000? Unless your company's tiny, probably not...doesn't sound liek the best of decisions, in that case - you have to look at the cost of time as well as the cost of software & hardware
I am currently using MySQL server backend for many thing on the website at the school I am working at.. (guestbook, and such) along with tracking /maintaning user accounts on the NT systems..
I was using Access for a while as the front end.. but basically it's a pain.. I have been shifting everything to a php frontend on the web server.. PHP has just more power than Access (Visual Basic) in less time I have gotten far more done.. I am ussing the DB.php (PEAR) class that comes with PHP4. and using the class.QuickTemplate.php off of phpclasses.upperdesign.com which allows me to very easily create dynmic pages with a template (and not clutter my PHP code with HTML)
Dude, why even bother trying to explain? It should be obvious by now that TCO is a totally foreign concept to 99.9% of the people who come here. I think it's cool that you hope to educate them, but I'm pretty much coming to the conculsion that they're hopeless.
Cheers,
Why is it that every time we get a Republican in the Whitehouse I loose my fucking job?
Maybe by now you've noticed that Republicans generally favor earning your way rather than having things handed to you. The Dems might've told you otherwise, but your job isn't an entitlement. After reading your post, lemme guess...member of the teacher's union? :)
Cheers,
Anyway, just my two cents. I still wouldn't recommend it.
Life is but a mist upon the horizon.
We are running a few mySQL databases at work with Perl based front ends. But, we also use Oracle for quite a few clients. A lot of the machines used in Production use MS Access with ODBC as the front-end to Oracle.
I can't even begin to tell you how many bugs Access has! The ODBC drivers suck and the program crashes constantly! Besides crashing, Access also has some weird, unexplained problems. Try doing a "search and replace" with Access on a dB that has over 1000 records. You can't! Access will sit there and lock up. The only way to get out of it is to kill the program.
I would spend some time in the newsgroups and on Google searching and documenting all the trouble that people have with Access. Compile all this into a single file and show your boss. He is thinking "short term" since words like Perl, and PHP are unknown to him. Once you open his eyes to the trouble that Access will cause he may change his mind.
Oh yes... my Access really is crashing! Granted, I don't like Microsoft products, but if I didn't have this type of problem why would I waste my time making shit like this up for someone I don't even know? You say that your Access rarely crashes... what exactly do you use it for? Our people use it as a front-end for viewing Oracle tables with 6,000 - 10,000 records. ... I'm talking real, everyday, working conditions.
I suppose that Access can handle *tiny* databases with 100 - 500 records quite easily. But, I wasn't talking about "mini-dB's"
I know I shouldn't feed the trolls like this.
BTW - do you get paid to post here by Microsoft?
Checkout http://www.w3.org/MarkUp/Forms/
XForms look very impressive and coupled with DOM3, CSS3, XHTML 1.1, and SVG some very impressive apps could be made making the web far richer then it is now.
Too bad browsers won't adhere to standards. Well at least not most of them. There are so many things that could be done on web pages right now that would make them very rich. Unfortunately Mozilla would be about the only browser that could then render the page.
Computer modeling for biotech drug manufacturing is HARD!
Option Explicit
Dim oRS, index
Set oRS = Server.CreateObject( "ADODB.RecordSet" )
Call oRS.Open( "SELECT foo FROM blahblah...", _
"DSN=A_MySQL_DSN;UID=auser;PWD=pass", _
adOpenStatic, adLockReadOnly )
For index = 0 To oRS.RecordCount
'Process data
Next
Call oRS.Close()
%>
You may have to use the numeric values of the Open() constants, I forget if loading the library is suffecient to define them.
Not sure what you mean about FM Pro being Mac-dependant. There is a PC version of FM Pro that is virtually identical to the Mac version, and this made it possible for Mac and PC users at my last job to use the same DB views at all. Not free, but still... What I don't understand is why the original poster wants to switch their front end to Access when the back-end isn't MS SQL or Access. If you've already got working FM Pro views and clients, then keep them, keep the views, and offload the actual DB to your new MySQL back-end (whcih FM Pro talks to just fine on PC AND Mac, incidentally)
My current job involves writing and working with access frontends and SQL server databases. Currently there is no other product that offers the ease of database conectivitly like access. Period. End of story. Sure you could write up something with perl or php, but thouse options lack so called 'bound controls'. Access is the RAD of database development. If you want something done semi-quickly and with as little pain as possible you generly use access as the front end. Many things writen in access would take half again as long at best in any other language. So don't diss access because it's microsoft. As of this point in time there really isn't anything that acutaly makes using databases something less than a migrane... (it's just a headache)
To connect Access to MySQL use MyODBC, which is available from the MySQL web site. Just follow the included documentation--it is extremely simple.
Once the ODBC driver is installed, you simply create 'links' in Access, which look exactly like normal Access tables, but are actually portals to the remote database. You can use links everywhere that you'd usually use the data from a table--creating a query, a bound form, or writing VBA code.
However, you can't change the table structure or create new tables through the usual Access methods. To manipulate the structure, you can issue Pass-Through SQL commands through Access which are not parsed by ODBC, but are sent directly to MySQL, or you can use VBA code, or you can use the normal MySQL client.
I use MySQL + Access frequently, and it works perfectly. I run my Access front-end in Australia, and the DB server is in the US, and I work over a modem! It really works very well.
Having said all that, if you've got the money you might want to look into MS SQL Server, since it is nicely integrated into Access and has a great Admin front-end nowadays.
>preference toward Access, but you know from past
>experience that Access will not meet your long-
>term needs and you recommend something different.
But also, does mysql really meet your needs? Mysql doesn't have stable transaction support (they're on b7..and i would never use beta *anything* in a production machine). Mysql also sucks when doing a lot of heavy inserts because of the table locking.
If you want to use mysql (maybe it is better in this situation) you should have a list of reasons on why it's better than mssql/access, not just 'because i want to'. Maybe your boss has a reason why access will do the job better.
You're the DBA, make the intelligent decision, not just mysql because it looks cool and is free.
Don't forget that it's Sybase at heart...
--AP
It was faster to develop the front end interface for the end-users and from the end-users' perspective, it was a better ui than we could have provided without cooking together entirely too much framing, JavaScript and DHTML for such a small target audience (e.g. to present subforms within subforms).
I don't remember exactly all the issues I ran into, I seem to remember some minor glitches in Access and having to issue some kind of special instructions to the users when certain kinds of updates and deletes needed to be done (sorry that's not terribly specific). But the setup was a breeze: set up the permissions for the users in MySQL, install the MyODBC driver on the client workstations, set up the DSN on the client, link the Access frontend to the MySQL db, rinse, wash, repeat.
Other posters have mentioned SQL Server & other dbs--whatever, if you were running on FileMaker, I doubt your database needs were all that sophisticated. And unless your db needs have changed significantly to include replication, transactions etc., stepping to MySQL seems like a reasonable decision. Then again, you didn't exactly specify what your db needs were either.
That said, if your boss is itching for a MS solution, you're probably just sticking your neck out to be cut off introducing Linux and MySQL esp. if your boss thinks you're only doing so because it makes you feel warmer and fuzzier inside about the technologies you're using.
I know, it's a little bit late but...
In the last months, I develloped an small application with MySQL server and Access 97. It was my first experience with ODBC and I did some mistakes. (I must say at this point that my day job is in Access 97 and I know Access pretty well. I also work with MySQL server (for a database on a web server) and I know it well too.)
1. it's pretty easy to access your tables by ODBC with `link tables'.
2. your tables in MySQL server *must* have simple primary keys or it will be a mess in Access. (You can redefine the keys in Access, but I wouldn't recommand that.)
3. Don't use the SQL front end in Access. Use VBA code instead. You'll have more control over your recordsets.
That's it.
If you don't like MSSQL talk your boss into getting Oracle.
the ability to have 100GB databases means nothing. "crashme" also means nothing. TPC means quite a bit more. The major problem with crashme and 100 GB databases is they are not likely to simulate real world behaviour. Remember all that quacking over CPU benchmarks and how they were misleading? Same thing with databases. As for mysql's "tests", well let me tell _you_ something, there is a bug in mysql where a missing update occurs, a single threaded app inserts a row, then trys to read it back and its not there. It gets written to disk eventually, but just in that instant its not there. I'm not sure the 'crashme' tests would reveal these kinds of limitations.
Hey -
Use MySQL for web development and want a graphical management interface much like what Enterprise Manager is to MSSQL? Check out 'the tool' -- http://dajoba.com/projects/mysqltool
Disclaimer: my brother wrote MysqlTool, but it still kicks arse and we need more users/testers so we can get closer to version 1.0...
-Abe
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.
How about cost? He mentions that the company is small. If the database is relatively simple (I have no idea how complicated FileMaker based apps can be) and he can maintain the MySQL server, then he is doing the right thing - finding a compromise by basing the solution on a foundation, which can be extended as needs increase and change (and not when the next version of the database comes out), while keeping his boss relatively happy with the Access front end. Not to mention that this could be the a way out of Microsoft's "software as service" (a.k.a. pay-per-byte) future plans.
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.
Unless the shop heavily uses Microsoft servers already, I sincerely doubt that managing an MS SQL server will be easier than a Linux box. Being able to connect 10 desktop machines to a file and print server is not the same as managing an MS SQL server (remember, the post said small company).
In addition, many have pointed out that using Access as the front end may be a bigger source of problems, since Access doesn't seem to play nicely even with MS SQL. Given all this, it does seem that an HTML - PHP/Perl - MySQL/PostgreSQL/Oracle solution could be indeed superior, especially in the long run.
You are using Access on NT as the database server? You should be concerned. MS-SQL would seem like the most direct upgrade. (You already have the NT server, just need MSSQL server.) Access should be able to easily migrate to MSSQL backend. PostgreSQL might (should) be a good alternative as well. (depends on exactly what you are doing) You can actually use Access to move your data from the .mdb files to the ODBC postgresql server.
I would be cautious in recommending that you move a critical app (to your job at least) to any database that you don't know much about.
Why not just share the Access .mdb file on an NT server? Sure, once you have your 20th user on the system it will come to a grinding halt. But you did warn your boss. Maybe he'll listen to his dba the next time.
Yep... I've done it. There are a few quirks but it works quite well. From my experience it is more than acceptable for an internal production system.
Brian Macy
... and it worked a treat. Using Access with either mySQL or PostgreSQL works a treat if you use the ODBC drivers. Despite being a Linux fan and a double fan of PostgreSQL I have to grudingly admit that MS SQLServer 7.0 is not too bad (but more expensive that using PostgreSQL :) ).
Just as an aside: MS Access supports one of the best reporting facilities I know. Does anyone know why MS didn't use it in VB 6.0? I HATE that DataReport abomination supplied with VB6 and Crystal reports is not that much better either.
Can anyone recommend a Linux reporting facility???
I have been programming with Access for about 6 years and it is okay for most run-of-the-mill database tasks. It's just not really good enough for a full blown multiuser realtime system.
Arrgh.. I sound just like a MS junkie - I'm not, it's just that I have to use MS stuff at work ('cause the boss creates a stink if I don't).
I agree with most of your points about Access. However, it DOES come with good reporting facilities.
Choose solutions that are portable, standard, and will last.
If your company is already running on Macs, why not check out some of the other database available?
:-)
One that I've heard a lot of hype about it Valentina. I have no idea if it offers good performance -- but at least its creator thinks it does.
What was it you tried? If it was gaining access to MySQL databases from Access 2000, it's worked fine for me. I have Apache set up to log to MySQL. I can then use Access (among other methods) to browse the logs, see what pages are getting accessed, who's sending page hits my way, etc. Setting up MyODBC to access (no pun intended) particular databases and pulling them up in Access to run queries was trivially easy.
(Next time you post, remember: <blockquote> is your friend. :-) )
20 January 2017: the End of an Error.
You never say what the database is for, what load (simultaneous open connections) you expect, if and how it will need to scale, etc. etc. Which tool you choose won't matter if you haven't clearly defined your requirements. None of the tools you mention are inherently "better" than any other; it all depends on what you are trying to do.
I suggest you define your requirements and architecture first, without regard to specific tools or platforms unless those are requirements that can't be changed. Only when all requirements are agreed on should you be out shopping for tools and platforms.
perl -e 'fork||print for split//,"hahahaha"'
BTW, I wasn't saying that you should use Postgres over MySQL, I'm sure you can make that decision for yourself! I'm just saying that ODBC<->Unix worked fine for me, and you may well find it equally hassle-free.
perl -e 'fork||print for split//,"hahahaha"'
It is very possible to develop your db application in Access and use mySQL as a backend. Use myODBC (free software, stable). Once you install this driver, you can create a machine data source to point to each of your mysql server's db's. Next, create your tables in mySQL. Then, create an mdb (Access application). Instead of creating new tables in Access, use the Link Table feature, select your myODBC data source, and then develop your application as you would have if you had created your tables in Access.
So, you want to web enable this with PHP4? Can do! Access is capable of exporting an ASP application which manipulates your db via ODBC (MS assumes you are running IIS, Win2K). You may take this exported ASP and use the asp2php application to crank out php code. A simple find-and-replace operation should be able to correctly point PHP at mySQL instead of Access.
Whatever you do, you should let the boss know that it needs to be web-enabled. Browsers are free, Access is not. Unfortunately, Access-generated ASP files fail the XHTML 1.0 validator post-processing miserably, and I don't think they even use external CSS.
All this can be accomplished much more easily with a plain old mySQL client, emacs, Apache and PHP. Perhaps someone could start a benchmarking project to compare processing time for functionally equivalent scripts on the PHP4+Zend/mySQL/Apache/Linux and ASP/SQL-Server/IIS/Win2K platforms. That way you could have some tangible evidence to use to convince your boss to get off the MS soap box.
Good luck.
You obviously don't follow MySQL.. If you don't know what you are talking about then don't talk. Period.
Walk away man, just walk away.
-jcr
The only title of honor that a tyrant can grant is "Enemy of the State."
"Not using it just because you don't like Microsoft could get you fired, and IMO it would be justified. " Wow, that's pretty harsh. Nevermind that you can save money by using mysql (provided it'll do what you need and scale as approriately. And that in using MySQL it'll be accessible my other platforms as well. I aggree that one should use the right tool for the right job however, and that sometimes the proprietary solution is best. That's not very often though. You tend to get (IMHO) much better software when you go open source and / or free.
"If you love someone, set them free. If they come home, set them on fire." - George Carlin
Turloch
Be Free: Free Software Tuition
or why not use Sybase, since that's what MS-SQL is anyway.. please.. there is no significant reason not to use MySQL and MS-SQL has it's own problems as well.. don't listen to this guy. the only real problem is that ODBC is a bit of a slow way of connecting.
people use mysql because it is free. Even though we all hate MS, you've got to agree that SQLServer is a much better DB than mysql. Face it, people are paid to work on it; it implements more of the sql 92 standard and it works.
I myself, prefer oracle of DB2, but what can you do. If you already have sqlserver, why replace it with something inferior (note: I did not say bad, mysql is just a child).
-tony
Access has got to be one of the most inefficient & ugly ways to make a front end to an app. Our Time & Attendence app is written in Access and it's really a stinker. Please, use something else, even if it's VB for the front end. Access does not make a good front end for anything (even Access).
Later...
KangarooBox - We make IT simple!
Obviously you've never worked with SAP products. Over the past two or so years of dealing with it constantly I've come to the firm conclusion that SAP is the lousiest, most poorly written, ridiculously bad software I've ever seen. Microsoft likes SAP for the same reason Alabama like Mississippi: keeps them from being the worst at EVERYTHING.
The Sybase heart got torn out with version 7.0 of Microsoft SQL Server. Version 7 is 98% Microsoft code. Version 7.0 (and also SQL 2000) is actually quite impressive, but so it should be with the number of PhDs that Microsoft brought into the SQL development group...
Of all the M$ products, I think that SQL Server 7.0+ is their finest creation. Exchange Server, on the other hand, is worthless crud written by people who wouldn't know an RFC was if it slapped them in their faces with a wet fish...
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.
Actually you are right about MySQL. MySQL isn't great for everything and it doesn't have transaction support but SQL server is usually an overkill since SQL server is usually slow but has richer functionality.
I would say Postgres would be a better alternative.
Well, the original poster did mention they were migrating up from Filemaker, which suggests to me that MySQL would be a good next step, unless their growth patterns compare to those of, say, AmIHotOrNot.com...
--
come on though, wouldn't it be this guys job to recommend or at least point out the flaws where they are going to have potential problems?
I think all he is looking for is tips on using the access front end to another DB backend, just to create a proof of concept. Apparently his boss likes the Access tools, so he is trying to let his boss use them. If your just gonna bitch, find somewhere else to bitch, he is just looking for information to prove to his boss that this can be done.
theKompany.com will be releasing beta 1 of Rekall which is designed to be an MS Access style application in that it is a more user oriented, database centric development environment. The scripting language is Python and it has a full compliment of widgets and wizards as well as reporting ability. The web pages will be up in a week or so and the beta will be out around the end of the month (http://www.thekompany.com).
A precurser to this work was the KDE-DB data access layer for KDE. This allowed us to abstract the database further so that while Rekall comes with a default xBase data repository, you can swap it out without notice for anything that has a KDE-DB plug in, which is currently MySQL, PostgreSQL, Informix and soon for Oracle and DB2. You can see details on KDE-DB at http://www.thekompany.com/projects/kdb
P.S. - To be somewhat on topic with all of this. To the guy who has to implement an acess based system for his boss and use ODBC for it....
I'm sorry.
That solution is the last one I'd ever pick, I did so once in the past and its not a fun path to follow.
Oracle isn't that hard to administer, but it wouldn't be that hard to throw a large enough wrench in the works to hurt its performance.
Quit pulling your own pud, that's disgusting.
Don't go around trying to insult people by calling their tools a toy, that doesn't impress anyone. MySQL may not be a good solution for you, but it works great in some situations. It's very fast on cheap as shit hardware, free, and there are plenty of ways to back up your data with MySQL.
If you don't need the features of a more expensive database that requires beefier hardware, then it's not a bad choice. Go fire yourself for being a prick.
FYI, we use MySQL. It's worked flawlessly for us. I would really love to upgrade to a database with more features just to "play" with it, but that would be a waste of time for us.
Sticking feathers up your butt does not make you a chicken - Tyler Durden
Being used to deploy Windows software for the last 20 years, and being used to support that crap, I have always wanted to replace it with something more manageable. You never hear Linux-only people complain about Windows - it's mostly people who use Windows on a daily basis that complain.
Since it's quite hard to sell a product to a governmental department with a clear policy not to use that product, I see it as a big sales success.
Being self-employed, I see it as very important, that work is fun. According to Linus Thorvalds, "the major design goal of Linux is... it is meant to be fun", and it IS.
There are so many people out there, that still believe in the Microsoft religion, and if you want to earn money, you have to learn to speak their language. Microsoft Access is such a language.
Our company has introduced the first Linux server into a government organization (Vejle Amt in Denmark) with a policy of not using Linux. The CIO has decided to try Linux in this database project, because it was based on technologies, that could be transferred to Microsoft servers immediately, if anything should go wrong (Interbase, PHP). This was the only way we could sell them something Linux based.
Their current solution is an Interbase database with a PHP-based HTML interface for simple tasks. Complex tasks are done with Microsoft Access (via ODBC), and the Access programming is partly made by the governmental organization, partly by us.
The current experience is very, very good, and because the solution is based on Linux, further adoption of Linux based servers is very likely.
I can tell you, that it was a very special moment for me when the first Linux server was put into approved daily duty in an organization with a clear policy of not using Linux. And until now, Linux has proven to be a very wise choice.
I hope they will replace Access with something else, too, one day.
If you have MSSQL already, use it. Not that it will work any better for most apps, but, it will buy you job security. I don't observe db admin being done too well in most shops unless the app is very important, and, even then, I see a lot of screwups. If you aren't a seasoned dba, take the one that's "most supported." It'll save you grief and get you support for the app from your boss.
Linux rocks!!! www.dedserius.com
www.dedserius.com
VB != VisualBasic
Solution. If what you're doing is anything that requires transations, MySQL won't do the job.
Ultimately, I think you'll find that Oracle has pretty robust ODBC drivers and its a nice namebrand that your boss will recognize....
MSSQL Server does the job, but you'll be forever tied down to MS the second you start down that path.
You might point out to your boss that Microsoft is moving away from ODBC in favor of a new technology called OLE DB. What implications this may or may not have for your design I leave to you.... all the new Microsoft paradigms are based on OLE DB data sources (basically a COM based interface to data) you only have to see the big ADO push in VB land to see this is true....
Do not spread "09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0" over the internet, thank you.
Why not just use a client/server application written totally in Access if you are concerned with compatibility issues? This is quite simple to do and will provide you with the ability to use local temporary tables which can greatly increase speed when manipulating large amounts of data.
But the obvious question here is do you want a web front end or not? There is a huge difference in what you can do with PHP than Access....they are two totally different tools that both have great strenghts and weaknesses. I think you are confusing the front-end of the application with the back-end. The front-end could be Access, Filemaker, PHP, ASP, or custom programming. The back-end can be MS SQL, Access, MySQL, PostgreSQL, PervasiveSQL, Oracle, etc, etc. Any of these front-ends can be used with any of these back-end databases.
When starting an application like this, you don't fit the application to the tools, you fit the tools to the application. What does this DB app do? Who uses it? How do they use it? What kind of security is needed? What platforms does the front-end need to run on? Who will support the back-end? Figure out what the design specs are and then resubmit this to Ask Slashdot!!
ÕÕ
mysql isn't an RDBMS to being with. Maybe someday it will be but it certainly isn't in its current incarnation.
My blog: http://jkratz.dyndns.org/~jason/blog/
I don't think that this is "weaseling" at all. Lets face it. Access has some major table locking issues, and if he is more familiar with MySQL why not use it as a server?
bash-2.04$
bash-2.04$yes "Don't you hate dialup connections?"| write USERNAME
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.
Well, I read from the post that their boss wanted to use Access as the front-end, but may be flexible on the back-end. So this might be the ideal solution for the client. I know that I have often setup simple Access front-ends for users that need direct access to the data for our Oracle and SQL Server databases... it gives them a lot of flexibility and power for a novice user. Of course, it gives them a lot of opportunity to do a lot of damage...
-rt-
-rt-
** Evil Canadians are taking over the world. Learn about the conspiracy
... 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.
If I were use, test Access 2000 to see if they have improved on this at all, because otherwise it's just not worth it. And I'm not just some Access or ODBC flamer, I'm speaking from direct experience!
-rt-
-rt-
** Evil Canadians are taking over the world. Learn about the conspiracy
Every major database has extended and modified the SQL-92 standard to meet their needs. Transact-SQL (MS SQL Server / Sybase SQL Server) does it, PL-SQL (Oracle) does it. Access does it. (Haven't used Informix enough to know). Postgres does it. MySQL most definately does it. Most do come a bit closer to the SQL-92 than standard Access. The rough of it? If you're going to use a database system, you'll have to learn the finer points of the SQL dialect it uses, as the companies add additional features to differentiate themselves from the competition.
Why do you want to use one of these open-source database systems as your back-end? Call me out-of-place on this site, but databases are currently better in the closed-source world than in the open-source IMHO. You might ask why, well, if you're going to add some functionality to the database server (as an Open-Source-Developer) what are you going to choose? You'll choose the sexiest thing that's available -- something that'll boost speed on selects, or something that'll increase theefficiency of the storage. However, with this, a lot less time and effort gets spent on less noticeable features, like concurrency, transactions, rollbacks, etc. I'm not saying they don't get the attention, I'm saying they don't get AS MUCH attention as they do at a closed-source cathedral like Oracle (Remember, Oracle is the #2 software company in the world -- are you telling me as many people work on MySQL/msql, Ingres/Postgres etc. as work on the Linux Kernel?!?!?). When your ability to sleep at night rests on data being secure, I personally opt for something that has a little more development time and QA time under its belt than an open-source solution.
What am I saying? I'm asking why do you want to use MySQL as a back-end for anything? It's ONLY benefit over a true RDBMS is the speed at which it makes simple selects (SELECT * FROM tablename). That speed comes at the cost of Transactions, Concurrency, and Reliability. With that being said - there are certainly uses for it. Using it as the back-end for a website, and publishing data to it from a company-wide database would be a noteable example. HOWEVER, if you want reliability for your companies data, you *probably* want something more robust. The fact that you're coming from a Windows house makes it even more ludicrous. Sure, you can fiddle with any of these programs (take a look at the Sybase ASE 11.03 version released for Linux -- for FREE) and get them working and integrated with Windows, but why bother? MS SQL Server works quite seemlessly with the Windows world. Don't get me wrong -- I have issues with it, but it does a fairly good job for a small amount of money (*g* take a look at Oracle pricing).
Now, coming from a filemaker solution your boss has probably noticed that Access is the most similar product on the Market. When I say similar, I mean that it has the ability to create interfaces within the program, and yet also functions as a "database". I doubt you'll find that with any other product, instead you'll have to do coding (web -- PHP, ColdFusion, ASP, Perl, or machine -- VB, GTK+, etc.) to create the front-end. My two preferences there would be ColdFusion for the web (quick development time), and VB for the client (again -- windows environment, and VB works pretty damned well with Access / SQL Server).
The problem with Access is that it handles everything as a large file. If many people are accessing this information, then they each need a copy of this file -- and any time you make structural changes to the DB, you have to propogate new copies of the front-end to everyone (hence the preference to a web front-end, or VB where you can code in a check for the current frontend version, and force an update of the VB program if it is not the most recent).
If you are still set on using an open source database, please take a look at the Sybase ASE 11.0.3 for linux (Freely released, no support), as it takes a MS SQL Server ODBC driver, but operates smoothly within that realm. I believe you've seen postings before about how to link with MySQL databases, and you've seen the Postgres zealots trying to convert you (*grin*). However, if you're just looking to change the backend, you can link to these via ODBC in Filemaker5. Sure it's messy, but it won't be any cleaner with Access/msql,Mysql,Ingres,Postgres,Sybase -- about the only thing that'll work very smoothly is Access/MS SQL Server (the joy of a MS dominated world).
And please, if you're worried about the integrity of your data (I don't know how complex your FM dataset is), please, please, please consider using a Relations Database Management System like Sybase, Oracle, Informix, or MS SQL Server.
Friends don't let friends use FileMaker
I used MySQL on Solaris and front ended it through Access. You can use the MyODBC to connect and can write native MySQL queries in Access and send it as a pass-through query to hit the database. Then store all the queries in Access and you have your solution. On the other hand it wasn't really fast. I have to agree that if your boss wants to pay for SQL Server then I would say go for it. It's going to be faster and it is far more of a full featured database than MySQL (transactions anyone?). The only reason that I can see not to use it is hatred of m$ft but that is more of a religious issue than a technological one. It's not your money so let your boss do as he pleases. MS-SQL is a fine product.
Hey, you think your house is cool?
>>-- what the nature of your data is, or the
>>nature of the front end is.
Exactly.. you don't.
>>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.
Really? how do you figure?
Linux/MySQL are FREE/Stable and easy to program
(MySQL is also fast as a Rabit on crack..)
>>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.
"REAL"
database.. now come on.. Access/MS SQL are full
of GUI-boy toys.. that doesn't make them more
"real". 98%.. if 98% works, and it's free,
wouldn't he be an idiot to use a "pay to play"
database and then need to deal with all the
expensive hardware needed to keep it running?
>>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.
ok, lets see..
#1. (note, I already found one). MySQL is free, fast and easy to use.
#2. If you have a DBA that can't figure out MySQL but can understand MS-SQL, they are not a DBA.
#3. Many, MANY sites use MySQL for production day-to-day HEAVY traffic..
#4. The Windoze clients are where the duct-tape is, not the MySQL database.
#5. Backup and Restoration of a MySQL database does not require special "pay to play" software.
#6. Any junior level sys-admin knows enough unix to keep a linux box running and use MySQL on it. (if they are a "ONLY M$" admin, then fire them and hire a unix admin.. he will know both OS's anyway. Seldom is the reverse true)
#7. MySQL support is free (via EXCELLENT newsgroups, IRC, etc..) --> Ever tried M$ support? It's an oxymoron.
#8. A Crappy Idea, and maintenance disaster is a office with a single admin that only knows wintel, runs mission critical/security conscious apps. on it and is foolish enough to discredit the FASTEST growing OS on the market.
Have a nice day.. now "go do the right
thing". (Use the linux/MySQL solution).
-Celtic
=-=-=-=-=-=-=-= - The Celtic - =-=-=-=-=-=-=-=
i have a small project that is to somehow let a user run queries in Access on some data that is currently on a server in a DBM database (flat file bianires, right?).. as a quick fix i made a script to dump the data to a .CSV and the user downloads that and imports it into Access.. Although i assume there's no way to do an ODBC connection, is there any better way to do this short of changing to a different database?
jas
http://joshgray.myip.org
MS-SQL Server 7 is based on the Microsoft Data Engine (MSDE) which is basically SQL Server 7 with the client management tools (and a few enterprise features) stripped out. Although it depends on what type of work you're doing MSDE databases perform quite well, and the MSDE engine is free to redistribute (if you own Visual Studio). I realise that part of your "MS-SQL Server costs too much" argument is based on the cost of the hardware and operating system necessary to run it, but it may well be that they already have these also. I'd use SQL Server if they've bought a license for it, and MSDE if they don't have a license, unless their needs fall squarely into the areas of strength of MySQL.
MS warns against using Access databases for enterprise databases. There is a difference between Access and an Access database. Access can be used as a front end for other ODBC datasources, as long as you have a driver for them. Don't use mdb files as datasources, but using Access as a front end is not a bad idea. Your argument is kind of like saying "Don't use Netscape because Netscape Enterprise Server sucks."
Why is Gnome pronounced with a hard G?
Gah
yea, access is such an sql-compliant database. i've done this - works pretty good... there are some issues with the date format in access, though so be warned.
Once more, I'm pleasantly amazed by the collective knowledge of the /. groupmind.
The correlation between ignorance of statistics and using "correlation is not causation" as an argument is close to 1.
-
-
Listen. Strange women lying in ponds distributing swords is no basis for a system of government.
Maybe you should go work somewhere where you have some level of confidence in your manager instead? I'm sure most companies can find something more productive to do with the money than to waste it on tech they'll never need or use. Money is most often a scarce resource you know. Was this ever about an end user product to start with?
-
-
Listen. Strange women lying in ponds distributing swords is no basis for a system of government.
There is a way to talk directly to the MySQL database using quoted SQL within Access that I didn't see mentioned. Use only queries as your data sources. Change the properties of the queries to quoted SQL. Which means don't let Access interpret the text I'm going to type. (It's text mode queries only.) The returned query results are static only so you have to do all your updates row by row using quoted SQL update and delete statements with parameter passing.
This translates into a lot of extra work because you have to actually code all of your interaction with the database. But the real benefit is that you will undermine your bosses lame effort at making you use VB so he can read and maintain the code. And you will save yourself from having to do this at the end when you discover that your real enemy is the way Access lulls you into a sense of RAD when you always end up having to hand code the interactions/representations with/of data to produce those simple reports/summaries that are always just beyond the reach of a straight forward query in MS Access. You will also have the luxury of being able to switch away from the demon AKA Access to any language/environment/database because you will have forced yourself to have intimate knowledge of all the interactions in your application from program flow in the GUI to dataflow in the database. And you'll never have to rely on the limitations of cursors and stuff because you can always hand optimize the amount of data passed back and control what gets cached on the client side. Rather than relying on the undocumented dynamics of someones hacked app turned bundled money making monstrosity. i.e. MS Access
Just my 2 cents.
MyAccess Seems to have some interesting tools, found out about it from MySQL's contributions section. Works as a front end in Access. You might want to evaluate this one...
AF-Design, web development.
Don't get me wrong... I use MySQL on my personal website, and I like it. But there are reasons I use Oracle at work. One is that we have skilled, experienced DBAs who wouldn't touch a freeware database that doesn't really support transactions, SQL sub-selects, et cetera, et cetera.
And hey, if this is just a half-veiled ploy to introduce GNU/Linux at the office, remember that Oracle runs on Linux too.
Yes, Oracle is expensive as hell. But when you need that kind of power, it's the only choice. If you don't need that kind of database, then maybe you can get off with using MySQL. Explain that it's used by lots of Linux websites, explain that it's free, and, since he's a Mac guy, explain that a port of MySQL to MacOS X is inevitable. ;-)
If he really does have hardcore Microsoft-fever, mentioning Oracle will only make things work, so maybe start off with DB2. It's fast, a lot less expensive than Oracle, and has a comparable feature set.
--
--
I like to watch.
Why couldn't you let this guy dig his own hole and try to use MySQL with Access as a frontend and find out for himself what you so plainly and clearly stated for him?
Access forms are actually more difficult to set-up & maintain, and offer no more than much more cleaner, useful HTML forms Ok, just one example: How would you filter a combo box after an item is chosen from another combo box, especially when you are working with very large tables?
Database goes kaput on first day, boss starts wondering what the hell he's paying you for, dumps you for contractor who does it right the first time and you go back to being head fry boy at McDonald's. Lucky you.
"Do not be swept up in the momentum of mediocrity." - anon
I dunno that article sounds like the problem is pretty simple and that thread safety can be acheived either through proper client calls or specific compile options.
http://support.microsoft.com/support/kb/articles/This article assumes that you are having clients hit a webserver which is in turn calling the Jet ODBC driver to get at the database. From what I have read this is not going to be the case. The post states that Access will be used as the front end on the client machine it will make calls to the Jet ODBC on the client which will in turn call the database on another server (linux). Two completely different situations
I hate to bash you on this one but neither of the articles you provided really shed any light on the issue at hand and make the appearance that you are simply attempting to find ways in which to bash Microsoft. Microsoft fully acknowledges the fault in both articles, provides a reasonable answer as to why the problem occurred, and gives a reasonable solution. If you need unlimited users/high concurrency/24-7 support there are simply much better options to look at.
Note also that at the end of the article there are links to newer drivers. Some of the information in the followed documentation give the appearance that the issue is really problematic in Jet 3.5 but possibly resolved in 3.51/3.52
Being a long time (l)user of Access (since 2.0) I can say that it definitely has it's strong points. Want to create a quick front end point it at a database and go? Want to build a system to spit out some simple reports that others can use with excel/word/whatever? Want to create a small database to keep track of employee's, performance, general information? Access is a great way to do all of that without spending an ass load on development costs or high end server liscenses and hardware. Need something to hookup to a webpage, more than 50 people to use, handle more than a few hundred thousand records, like headaches? Access will not be your answer for those issues.
Use what works, stop trying to find excuses to further obfuscate the problems
"Do not be swept up in the momentum of mediocrity." - anon
yes it can be done, in the company i used to work they constantly used access & VB as a frontend
and postgres as a backend odbc source the main drawback is speed, since ODBC is slower than natively talking to a DB
42
I don't think this is true - IIRC, FileMaker 3.0 Server was relational. Keys, etc. Blah blah blah.
[|]
Well, as many have already pointed out MySql has ODBC drivers which can be used to hook Access to MySql via Access's linked tables.
The question is "Do you really want to do this?". I only deploy MySql on web-backends where read speed is important. A carefully setup MySql/Apache/PHP combo rocks if you need a database driven website,
However, MySql is only used for the web-side of things. In the office we use MS Sql Server and a VB front-end. I'd recommend Oracle over SQL Server if you have a lot of data (millions of rows), a lot of users, or a lot of heavy duty querys, but all of these are fairly moderate where I work. We then regulary push the data from MS-SQL to MySql as a one-way op.
Access is great for RAD development & situations where long-term support isn't an issue. The Access forms lib make this stuff a breeze - VB and ADO takes longer.
I don't have much experience using Access with MySql, but I can offer some tips from my experience with Access/SQL Server which hopefully will give some kind of an idea what you can expect.
Firstly for god's sake don't use Access 2000 with linked tables! To get Access 2000 on Windows 2000 with SQL Server 7 requires that you have every available service pack for all three pieces of software client and server end, otherwise you'll have all sorts of problems with the linked tables (sometimes they will refuse to link, sometimes it will link but all your rows show up as #deleted, bit columns in MS-SQL that can be NULL give Access headaches, GUID's sometimes get treated as guid{x} in code instead of {x}, etc. etc). All of these problems can be fixed with the latest service packs but in my experience Access97 and Sql Server 7 make for a more reliable combination.
Don't attempt to use Access if you have a large amount of data. Access barfs once the tables get to large. Access's psuedo SQL implementation (Jet I believe?) is stoopid enough to try and do a lot of the processing client-side (like joins and the like - eek!). Remember Access was originally designed to work a local database.
Working with Access also gets nasty if you need to fill forms with data from more than one table (updatable joins? Have fun...). VB & ADO is much nicer for this sort of stuff.
Access2000 with SQL server can utilitize Access Data Projects. A custom mode designed to link the two together properly - you get access to all the SQL Server properties like views and stored proceedures, and decent query optimising (Jet is rubbish). In return you lose some of Access's features (you have no ability to create local Access tables etc.) I haven't played with this that much so I can't offer that much advice here.
Unless this RAD stuff, if you are using Windows clients I'd really recommend VB and ADO - it will connect nicely to MySql via MyODBC, and you have none of the Access headaches. VB and ADO are more widely used for real application than Access so you'll find more books and examples, and it seems to be more stable and better tested.
You really need to consider the choice of MySQL. SQLServer is great for middle-sized deployments and is very easy to maintain. Alternatively if you are on an OpenSource bender take a look at PostgreSQL. It seems to have a good rep but I haven't played with it that much.
If you choose SQLServer some advice:
*it needs a fair bit of diskspace - particulary for it's transaction logs, which due to the way they are allocaed and used can ballon waaay waay up (allocated space at the beginning and end and NOTHING used in the middle!). There's example stored proceedures on the web on how to get it shrink. Do your research!
*SQLServer backups are huge! We've ditched them now, and do a nightly backup to an new Access database everytime (SQLServer generates the Access db, Access the application is not involved), but ours is only a small operation.
Remember you pick the tools to suit the application - not the other way around. I could be wrong here but it sounds like you want to use MySql simply because it looks like the 133t OpenSource database of the hour. RESEARCH!
Anyway, enough rambling, hope this is of some use. It's very late here at the moment, and I'm tired so it could all be bollocks.
Just tell HIS boss that you can have a product for free that has the same performance that the product he wants to pay for.
--
Je t'aime Stéphanie
He's a Mac Guy he won't know any better :-)
No Seriously show him the cost differences and the performance/$ spent and you'll likely have him convinced quite quickly.
Network Security: It always comes down to a big guy with a gun.
Locking issues shouldn't be an issue with either.
tetrad
he is not the boss, and his job is to do what the boss says.
Only the State obtains its revenue by coercion. - Murray Rothbard
you quit because your boss likes MS? thats a really great reason. Have you considered that MS might be the right tool for the job? When considering a solution I look at what will work best, not at who makes the product.
Only the State obtains its revenue by coercion. - Murray Rothbard
Maybe we tend to believe it because the comment itself is credible. We know that part of the Oracle license is that benchmarks can't be printed without permission, so the claim that Oracle won't let them publish the benchmarks without that permission isn't obviously problematic. In fact, it makes sense that if Oracle had turned out better that they would have let the benchmarks be published. It basically boils down to your assesment of the credibility of the people making the claim under these circumstances; if you think that they're credible and wouldn't say a thing like that without evidence, you can believe it. It you think they're bullshitters, you don't believe it. It's really very simple.
Of course, we all know that there are three kinds of lies: lies, damn lies, and benchmarks. Just because Postgres won at Oracle's benchmarks doesn't mean that the test was particularly meaningful to Oracle. Oracle's main selling point is that it's a scalable, enterprise class system. Benchmarks performed on typical small-scale systems like what you'd use for a small company web-site aren't necessarily translateable to the big iron where Oracle really lives. There's nothing particularly hard to believe about the idea that Oracle has been so optimized for high end applications that it can be beaten at the low end by a RDBMS that's been developed for low end systems.
There's no point in questioning authority if you aren't going to listen to the answers.
You connect to a View as an Access 'Linked Table', or in code by appending to the Tabledefs Collection, and when using a DAO OpenRecordset against it, make sure to set the options db_openDynaset and DB_seeChanges if you want to make any changes to the data. And if you don't, use an SP anyway.
For an SP, if it returns rows without input parameters, create a 'Pass-through' query containing "exec " & the SP name. If it requires parameters, use a DAO queryDef object or an ADO Command object (a lot less painful) in code.
I mean, come on, this is VB we're talking about here (in Access drag). It was never likely to be hard, was it?
TomV
You will be working with a subset of SQL that will not be very satisfactory.
(In case you're wondering, yes I've been there w/ Access/Oracle... We had to abandon it).
Just don't do it. Tell your boss Access is only good as a front-end to small-time Jet databases... The ODBC access is provisional for odd tasks only.
Hi! I am one of MySQL developers. There is truly no big deal. Just download MyODBC binaries for Win32 from www.mysql.com site and install them on all Windows machines that are to access MySQL server. Configure MyODBC on each machine, according to privileges granted on the server. Then you will be able to use Access from any Windoze on your network. Two noticies, however : 1. Access 2000 has a bug in it's ODBC code and you should get patches from Microsoft 2. If you want to update tables with Access, each table should have one auto_increment key (counter in MS Access) and one timestamp column. There is truly no fuss !!
MySQL is ok, but it lacks many important features that a corporate database should have. You don't sound like someone who has had experience with real enterprise databases, so you should research what your needs are before you start knocking SQL Server.
I usually hate Microsoft products, so please don't hold this against me. But I would take SQL Server 7/2000 or Oracle 8i/9i , over Access and MySQL any day. The Open Source DB's just aren't there yet.
That's reality... for the moment....
Fuckin-A. Fuck microsoft ! Go work for a company that uses FreeBSD, Linux, or Solaris. Refuse to have anything to do with microsoft products. When brain dead management can't find anyone except very expensive consultants or children to work with microsoft products then they might reconsider their decision to buy microsoft.
I see. So companies which dont use Linux are not worthy of a solution?
That right, shit for brains. Those using microsoft products are making their own problems. Let em' deal with their own mastakes. Then they will see the real cost of microsoft products.
Another day closer to redwood heaven
I see a lot of technical comments comparing Microsoft and other vendors' products. They miss the point.
95% of all technical problems are people problems.
If you decide to stay at the company you owe your boss loyalty. That means do everything you can to make the project successful with Microsoft. Also make sure he sees you are doing it.
If the project fails anyway you may get another opportunity to do it right.
I believe mysql actually comes with myodbc drivers. pop them in and away you go.
"Only amateurs attack machines; professionals target people."
I've experimented with MySQL 3.21 using the windows ODBC driver with Access 97 and had some issues gettting the two to play nice together so eventually gave up the idea using Access as a frontend for the application and went back to a strait PERL web based front end.
There are newer version of both MySQL and the ODBC driver now so it may work much better, but it just did not work well for me about a year ago.
- subsolar
MySQL has a MyODBC that you can download from their download section. It allowed a ODBC connection to MySQL, and Access will be able to open MySQL DBs. IMO, you have SQL server, then use it, but if you must use MySQL, this is the way to go. It's usually meant for a linux server with a whole bunch of Win comptuers in the office.
"Time is long and life is short, so begin to live while you still can." -EV
I've got Postgres, which comes with a fine ODBC driver, talking to Access just great. Access is good on an MS platform for admin stuff. I have scraps of data here and there that cut and paste easily into ODBC-linked Postgres tables through Access. But I would never use Access as a client front-end--unless your user base knows how to use QBE or SQL and absolutely needs to cook up their own queries on a daily basis. Otherwise, if you have a set group of canned queries, why lug Access along? Better to use Java, VB, VC++, or a Web for the (much much thinner) client. Access is a good enough client development tool, but why drag along the whole Access executable (and shell out for all the Access licenses) unless your users absolutely need all the other stuff Access has? The vast majority of VB out there is employed for just this reason: you don't need Access unless your users need both canned queries and the ability to write their own.
--- WWSD? What Would Strider Do?
It was easier to use Access as a front end, because it can read from a bunch of weird, disparate apps (our invoicing system was entirely in VBS on Excel 95)and give you an easy way to centralize that data and report on it.
Part of the appeal is that it is as simple as it looks. When some middle management goon wants some statistic in five minutes you can get to it quickly and easily, and if you're lucky enough to work in an environment where your co-workers are intelligent and inquisitive, it's easier to teach them how to build queries in Access (and keep them off your back) than it is to explain how SQL works and where all the individual tables live on the network.
come for the naked robots, stay for the zombies
- Remember to put a timestamp column in each table that you want to update (this is in the MySQL docs). If you don't, you'll only be able to view the data.
- While the Access/JET solution works fine, VB/ADO doesn't play nice with the MySQL ODBC driver. That's a shame, b/c you have to spring for Access on each desktop (or build some web forms).
And would everybody stop crying about the lack of transactions (etc.) in MySQL - they're using FileMaker for this database at the moment!!Hi, I just actually started do this for my company, and found that if you take a look at the windows ODBC drivers that are available for MySQL, you'll find that at least that part is easy. The tough part (which I'm still working on) is getting the right connection string for the ODBC datasource. It's probably documented somewhere in those driver packages though, but I haven't had enough time to fully take a look at it all.
Cheers, and a hope this helps.
Chris
-- Humans, because the hardware IS the software.
Do you have some examples of the ODBC datasource connection strings you used to connect to the windows ODBC Mysql driver? I've been stuck on that fer a bit (mind you, I haven't really been looking too hard... :)
Cheers,
Chris
-- Humans, because the hardware IS the software.
Right, yes, I think I remember now, getting to that point. I guess you've never needed to connect to that dsn via IIS? (yes, even though that DOES sound a little idiotic, we have a couple of ASP programmers, and I'm to busy to just recompile Apache to handle ASP... easier to set up their own server with IIS and DB support and let them administer it).
Cheers,
Chris
-- Humans, because the hardware IS the software.
And if perl's not your thing, then you could always use jConnect and TomCat, and run JSPs.
But if the boss want's access, then he may well have a reason, so your Sybase ODBC interface is just sitting there ready to go.
I honestly can't imagine why you'd choose mySQL instead, unless you care about OpenSource so much that you let it become major factor in your decision making process at work.
And while OpenSource has practical benefits, what I read of your situation, doesn't suggest they're of much use.
If you let personal prefence and political ideals cloud your view, then you're not doing your job well.
--
Read more of this story at Slashdot.Read more of this story at Slashdot.Read more of this story at Slashdot.
Here's what I consider another choice:
- Leave the company
- Take some Access courses... Learn it. Learn it well.
- Come back to the company 6 months, or maybe a year later and offer to consult for them as DBA for twice your original wage. With any luck, the person they put into your old position can't hack it [it certainly sounds your boss is reccomending some serious changes] and you'll steal the show (and get some decent $$$).
It's a big bet, and it has a good chance of not working out, but in that case find a job elsewhere with your new upgraded skills.
The nice thing is, as a consultant, you won't worry (as much) about keeping the company going, and therefore keeping your job. You can always move on.
There's many bad things about being a consultant too. I suggest you go and research your decision heavily before you do anything.
Of course, I take no responsibility for your following of this advice. If it fails miserably, tough cookies.
If you could be told what you can see or read, then it follows that you could be told what to say or think - BoC
>and I would fire anyone that recommended it as a 'serious' solution.
That's why some companies always seem to have a brain drain. A hard-working, good team member type person has a brain fart/temporary problem and gets put on the chopping block for it.
It costs a LOT to get/train/hire new workers. It costs a lot more for them to get used to the environment. It costs _even more_ for them to get used to the politics involved, and _even more_ for you, as a boss, to figure out what makes that person tick. It then costs more to help assure other workers they are safe from being fired (since they noticed the DBA got fired for a simple disagreement, they are very uneasy). If you don't assure the other workers, they are already looking for other work... they simply don't feel secure where they are.
You sell your company short most of the time when you fire a worker. It is much cheaper to help that worker through the problem than to fire them (or let them leave... did you know that many employees that come back to work for a company again, after being fired, end up getting paid much more?)
If you manage with an iron-fist you'll find it ends up going through your company's profits.
>we all get fired if the wrong decisions are made
Ever heard that you can manage yourself out of a job? It's easy: To be a manager you need employees to manage. Your attitude sounds like you want to fire them all for maximum eifficiency. Without employees you can't be a manager.
I've found that in the places where workers are happiest (and remember, a happy worker is a hard worker) work gets done better. No one is happy when people get fired all the time.
Just a few cents from the other side of the fence.
If you could be told what you can see or read, then it follows that you could be told what to say or think - BoC
If the link doesn't work, look for article Q222135 .
Never play leapfrog with a unicorn. Or a juggernaut.
> FileMaker-based database solution wasn't working
Why is it not working? Can't you fix it?
> As DBA, I recommended a MySQL server with a
> Perl or PHP-based front end
Did you really 'recommend' it? Did you show
him examples of a front end? Did you list out
the pros and cons? Did you list them out in
his terms or your terms? Why DID you recommend it?
> insisting that I develop everything with Access
> as a front end
Despite what every single person seems to be
saying in this thread, Access is NOT a front
end. It is a relational database. Most of what
the user sees is visual basic forms. Have
you explained to your boss that the server can
be distinct from the client?
> good documentation on connecting Access to Non-
> Microsoft DB servers in general, and MySQL in
> particular?
You are thinking in terms of products instead of
solutions. Try this instead:
Think of what has to be done.
Think of the best solution to implement what
has to be done. Think of a couple of other
solutions. Flesh a couple of them out before
you decide which one is 'best'.
After you have done this, you should be able
to convince your boss that Access is not the
solution (or yourself that it is).
Anonymous posts are filtered.
I completely agree with this post. If your boss has decided on Access, it seems silly to use MySQL on the back end just for the sake of not using Microsoft SQL. I'm often faced with the choice of opensource v. commercial software. I try to make individual decisions based on the TCO and on what exactly I need. For example, I run an MS shop almost exclusivley, yet I use Analog for web stats. Why? It's free AND its a superior product to almost everything else that's out there. Even the commercial stuff. So, in short ... if MS is what your shop uses, use MSSQL on back end.
$20 Domain Name Registration - click here!
Before I get flamed to a crisp, I'll state right now that I would always recommend a non MS solution. However, I have run an Apache/php/mysql solution on an NT box for months now as our Intranet server at work. Granted, there are one or two difficulties (such as user authorisation) but in the most part they are easy to work around. There is the added bonus that our intellectually-challenged sysadmin (and MS thrall) is able to cope with it.
If MySQL/PHP is capable of what you want it to do, then My advice is to sell the same package but running on NT. The major advantages for the Company are that it is cheap to install, very fast to setup, and very fast in performance. For me the absolute killer blow was that it took so little time to deliver really_useful_results. I have also been able to gradually demonstrate the shortcomings of MS products for what has been asked for by management, thus making a FreeBSD box a question of when, not if.
Has anyone attempted to use Interbase with a project like this? http://sourceforge.net/projects/interbase
I tried this about a week ago using access 2000 and it froze the program. I think I remember reading somewhere that Access '97 will work though.
I'm an analyst, not a professional programmer, and I found the ease of VbA, coupled with the fact that my company already owns it and therefore doesn't need to pay for another ERP frontend, more than makes up for the bugs and wierd behaviour in Access. I used to as a front end to an Oracle database so that managers could easily access reports. It works well after 3 years and cost nothing. If you only have a handful of users (20)it's miles ahead of anything else. And VBA is fun and makes you feel like a real programmer !!
Remember, there is no "we" in megalomania !!
Go back to mass-emailing or flooding the newsgroups for business.
- Toby
I'm shocked about how many people declare combining Access and Mysql to be a dumb idea. I've been doing this stuff for quite some time now and surprisingly I had far less problems than with Oracle and their own JDBC driver. After all, a CLI like ODBC allows for the separation of server and client implementation. Once you get your MyODBC driver going, the only caveat is that you should stick to pure SQL for SELECTs, INSERTs and UPDATEs. Don't use the DAOs themselves to do that, in this setting they're just good for navigation in result sets. Along these lines, Mysql does a terrific job as a speedy backend for your Access client.
It is not the developer's job to worry about the price. If the boss/manager is willing to pay for SQL Server, then as far as the developer is concerned, the price is not an issue anymore.
He should of course make the boss/manager aware of the total cost of ownership. But choosing a lower performing solution when management is willing to pay for a higher performing one is idiocy.
How funny.. why is SQL Server missing from that statement? How is Oracle any cheaper or more open sourced that SQL Server? They are just as bad as Microsoft, if not worse, when it comes to the database market.
What hate and jealousy will do to people's minds...
He means windows binaries. BTW for a server envirorment cygwin is usually unacceptabel because of performance. It basically puts the whole unix API in a DLL that all cygwin ports use.
--- Justin Dearing http://www.justaprogrammer.net/ We're just programmers.
This is especially usefull when dealing with $19.95/mo ISPs who want to charge extra for adding database capabilities. With Access you just FTP the .MDB file to your ISP and, using ASP or other ODBC-capable scripting languages, you can connect directly to the Access file and SQL against it like any other RDBMS.
Can the same be accomplished with MySQL?
I had a very similar use for the mysql odbc drivers for Access. I had everything running smoothly except it was absurdly slow. Has anyone else experienced the same situation. I basically gave up, and used some php scripts as a management solution.
That's plain old FUD. Microsoft hasnt dropped support for databases in the past, and nearly always provide an upgrade path. Win95 is old outdated software, they have provided a direct upgrade path to win2k professional or WinME, or Win98 if you like. All are compatible. There will be an upgrade wizard in VB.NET, and it will cover 90% of the upgrade, and point out the 10% it cant (IF YOU HAVE A BAD CODING STYLE ... ie you are lazy and dont specifically declare your variables or point to a property of an object)
Please stop distributing above said FUD, and return to the real world.
---
Video meliora proboque deteriora sequor - Ovidius
Don't do it!!!
M$ Access is okay for doing crappy little jobs, but it's in no way a real database front end, it is seriously and honestly not the thing to use. Your boss must not have a good handle at all on IT and has fallen for the 'Well Access comes with office and I can make myself a contacts list easy and so therefore we must use this' - that is very common in organisations and very ill-informed.
You could write it using VBA and M$ Excel as well for example. That would be stupid too.
If you do have to go down this route, then just stick to NT is my advice. I know loads would disagree with my, but it'd probably better not to complicate your life with the two platforms. Get MySQL for NT (or better still AbriaSoft's distribution) and perhaps consider using Allaire's ColdFusion, it's pretty good.
I'm completely against using NT for this solution, *NIX would be far more better.
But if you're going down this road and your misguided boss must go M$, then at least make him pay for a decent web-db database development tool to do it with, and ColdFusion should be just the job.
Access is a waste of space for anything serious.
BTW. IF you're going the other way, Easysoft's ODBC-ODBC Bridge is pretty good.
http://www.easysoft.com/products/oob/main.phtml
Access was an RDBMS - it's not it's a GUI tool. It can use the JET DBMS, SQL Server, various flat file formats and other ODBC sources.
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.
Maybe - but as I said Access is not about 'data storage' it's about GUI applications. Most people assume using Access forces you use a Microsoft DBMS but it doesn't.
I've been a DBA for a long time (>10 years) but I never got to dictate which GUI tool the developers used. Come to that I never got to dictate what RDBMS we used either. I had a say but not the only say.
Microsoft Visual InterDev Home Page Microsoft VBScript runtime error '800a01b6'. Object doesn't support this property or method: 'platform'. /vstudio/includes/header.inc, line 76.
Roses are #FF0000, violets are #0000FF, all my base are belong to you
Sheesh.. I was called to do the same thing about a year ago .. and instead of writing to ask slashdot I was sending out my resume.
Word of advice: your boss has his head too far up his ass .. QUIT. I left my company without notice over a very similar situation. Treat what your boss is asking as in insult; drop everything and don't look back.
I can tell you that I feel a lot cleaner now.
--
One future, two choices. Oppose them or let them destroy us.
Since most modern versions of anything office supports HTML, just write what you need in HTML, import that into an Access database and poof. INstant Access front end. :)
Non impediti ratione cogitationus.
To do it, you have to use an ODBC driver appropriate to your database back end. In the Oracle case, you can install Oracle's Windows client distro on every host that will be running your Access application. Then you create an Oracle ODBC data source with the same name on every client system, say "mySource." Point your Access application at mySource, link in the Oracle tables and, voila, you have a very nice ad hoc two-tier client/server application.
You share the application by putting the Access mdb file on a share.
Don't expect it to scale, though, and you will find it a major nuisance to install the Oracle client on so many Windows machines. I don't know that a MySql, PostgresSQL, or Interbase client distribution even exists to provide the necessary Windows ODBC driver to make this work. If drivers exist, I see no harm in using one of these databases for the back end. (Incidentally, MS SQL Server ODBC drivers are standard in the Windows OSes, so if you go with SQL Server, there is no need to install third party drivers. Imagine that. ;-))
If you need something Enterprise strength, go with Java 2 EE and something like JBoss/Tomcat. In my view, J2EE is unbeatable. It's both the end of Microsoft's enterprise aspirations and a great tool for saving Linux from itself.
It would be tremendous coup if somehow the power of the Access front end could be used with MySQL
Are you crazy? Access may be powerful for simple stuff, but as soon as you try to do something remotely complex, you can't.. Unless you don't base your forms on tables/queries, which is what is supposed to make the power of Access.
Don't get fooled. Access may look great and powerful at first sight, but as soon as you start doing something complex, you'll discover its limitations and bugs (randomly corrupted forms that have to be imported (not because of filesystem corruption) from an older version because you can't use them at all, references to subforms that sometimes can be access and sometimes can't...)
Kids, trust me, Access is bad for you. Get a competent RAD tool instead
The MyODBC program has some windows-based setup program. You should be able to just put in the IP address, port, username and password, and you'll have a DSN created. Access should just be able to 'open' than DSN itself and connect you - at least, that's how we do it.
creation science book
If you're being overridden based on a higher up's emotional decisions, you may be best to move on, reframe the issue.
*YOU* are the one who will need to support it - if there's a crash at 2am, will your boss be driving in to fix it? Or will you (or someone else in your department?) Remote maintenance of an Access installation will probably require PCAnywhere or something similar - creating DSNs for applications remotely can't, I think, be done solely via Access over a network. (ok, use a raw connection string in the app I guess)
As much as I love MySQL, other open source options may be a better option, depending on concurrency and complexity of the system. But to be fair, if it's something that actually *could* fit in Access, you can most likely do it in MySQL anyway - I think you'll still get better concurrency performance in MySQL than Access.
creation science book
Yep I have actually -
) ;
// Hostname of our mssql server.
var $Database = "datbase"; // Logical database name on that server.
var $User = "username"; // User und Password for login.
var $Password = "pass";
$this->Link_ID=odbc_pconnect($this->DSN, $this->User,$this->Password,SQL_CUR_USE_IF_NEEDED
is what we use, with var $DSN = "DSN_NAME";
as part of a DB class we use. This is PHP, and the ODBC driver apparently will build the connection string for you. I used to have connection string stuff, but I don't have it with me right now - email me privately if you can't find any and I'll see what I can do.
creation science book
Might I add "if they HAVE it...and the resources to maintain it..."
DBMS's don't just "install and go". For the most part, there is a LOT of energy that goes into supporting an "Enterprise" system.
Why is it that Bosses think that Oracle and MS work better? They cost more to purchase AND they cost more to run AND they cost more to get back up if (when?) they go down.
Last week I was discussing this very issue with folks at the Human-Computer Interaction research lab at my university. (We have a bunch of interactional data related to wireless network usage that we want to analyze, visualize, cross-reference, etc.) Ultimately, for maintainability down the line, we decided on SQL Server on Win2k server.
... then decided I wanted to use one of my home computers to log in and add some new tables. First I look for some Mac client -- any Mac client -- for SQL Server. Of course there is none, unless you want to cobble together some obscure ODBC driver with a half-assed shareware GUI. That's OK, I thought, I'll just connect with one of my Linux boxes. Or OS X on my Powerbook. ... After two days of trying to make any of the ODBC solutions compile on either of those OSes, I set up ActiveState Perl with DBI + DBD::Proxy on the SQL Server box and got in that way, despite inadequate and sometimes incorrect documentation. This is such a hack and a speed liability that I don't consider it a real solution.
Now, I've never set up a SQL Server install before, but I got it running, put some tables in it
What did we finally do? Wipe Windows off the machine and install MySQL on Linux. We don't need any of the features SQL Server has that MySQL doesn't, and now cross-platform access is easy. We can use Perl and GUI interfaces on Macs and Linux boxes, and MyODBC to get at the tables with Access, which some of the non-technical people in the lab like to use.
If you're considering a SQL Server solution, check out the cross-platform issues first. We didn't lose any money on this because we have some sort of site license for MS products, but still. I assumed such connectivity wouldn't be a problem. The database world is less platform-agnostic than I would've thought.
Andrew
Recently I wanted to move an access database to a more robust platform. Some tables had a few hundred thousand records.
The MySQL ODBC connection was not the problem, Access was the problem, because of its non-standard SQL and some problematic data types. PostgreSql was almost seamless in converting the data, but there were problems there too.
In another project, what I did was to use the corp's existing MS-SQL database and my Linux web server with PHP. Installing the Sybase drivers (or FreeTDS), I was able to get VERY SPEEDY access to the MS-SQL server running on NT. Everything rocked. So we were able to have the best of both worlds... my bosses insane desire to have Access Applications, and our desire to have a web based Linux/PHP/Perl front end to the same database. You should look into this solution.
MySQL is great and all, but don't forget the ability to mix and match solutions.
Newsfollow.com
Six months ago, my business partner/investor asked me to come up with a database to handle customer info, purchase history, estimated reorder dates, plus inventory, payroll, assemblies, etc... He wanted me to write something that would be accessible through a dial up server so he could check sales etc on a daily basis from wherever he was.
Today, he is able to access daily reports, print purchase orders and checks, etc from his laptop through a secure connection to the dsl connection at my office. All I did was run down to Office Depot and bought the multi-user version of Peachtree!
The point being, bosses are much less apt to care about how you do it as long as they get the desired results.
Are YOU listed?
I've been using MS Access with an Access Accounts database (not MS product) with good results. MS Access has the ability to do 'pass through' queries that can be different from normal Access queries, so you can use the features that MySQL has from Access. You can link tables directly with ODBC and see them in Access like they were actual Access tables. Buy a book like Access 97 Developers handbook, Litwin, Getz, Gilbert or similar. Jari
I don't necessarily agree on all parts with this post. Here's why:
- Interbase is, like MySQL, a good database for developers, because it is rock solid and requires little or no maintenance, but you have to have front-end tools to administer the thing. These tools have always been there, but they have always been far from complete and always required the DBA to know something about the system tables or the exact query syntax to get a few things done. User management is such an example.
- Interbase is more targeted at the 'embedded database' market and always has been. And I don't mean the 'embedded device' market but the situations where (semi-) shrink wrapped applications come shipped with it and hide most details from it for the user. On this count, it's even cheaper than MySQL.
So maybe Interbase is also not the right thing for this job. My two cents? Stick with Access and develop it for the MDE which is 100% SQL Server 7 compliant. You can always move on later.
Umm, SQL Server doesn't use the Jet engine. Jet is the Access database engine. SQL Server uses *surprise* the SQL Server engine. Furthermore, a MySQL backend is NOT faster than a SQL Server backend. I don't know where you got your information. No offense.
"Saddam Hussein cavorts with terrorists."
If you really wanna run a full fledged database on Linux, you might want to take a look at IBM DB2 Universal Database. Its not open source and I have no idea whether it can be used from Access.
Access is adequate if rather pathetic on it's own. But it doesn't work well as a front-end, EVEN FOR M$ SQL Server. It can't handle tables! On any big view or stored procedure, the connection times out. So you have to pass-through the SQL statements to SQLServer and retrieve the results JUST LIKE YOU'D HAVE TO DO WITH HTML. I'm not sure if Access has this problem with MySQL, as I use PostGreSQL. I don't know what that fucking AC has up it's ass, but you can be sure it's not pretty.
Now, MS Access & SQL Server will easily replace this guy's FileMaker setup, but at the cost of at least TWO GRAND, and not offer anything more than MySQL. FileMaker is so far worse than MySQL--Where is Trevor Goodchild's fucking idiotic perspective coming from?
I know of a lot of mission critical operations out there using MySQL. I prefer PostGreSQL or even some commercial rdbms's (DB2, Oh..WTF, ORacle), but I will at the very least stand up for MySQL.
This guy is moving from FileMaker! Comma-delimited text files & Perl would work better. Day to day, MySQL & Access will be a big improvement.
Yeah, you can connect Access to MySQL/PostGreSQL. This will work, and down the road you can show that a PHP, PERL, or Java interface offers way more than Access can ever offer. Then you can learn some cool-ass shit, too. XML, PHP, etc. This is the way to go. Keep a copy of the tables in an Access DB on the side, just to cover your ass.
Check FreshMeat. I use a nice CommonLisp interface for PostGreSQL. There are many others out there, but few offer the real benefits of plain-old SQL. The hardest part is the intracies of Joins, but that is like saying COBOL or VB is hard when there are really hard things like C & Lisp out there.
Then, if I smoked all that crack at once, I wouldn't mind using Access & SQL Server. Of course, then I'm out FOUR GRAND. The trick is to sneak the "CRACK clause" into the fine print of the project's proposal, then when the customer calls & I'm out of my mind & I've got every field in every table linked to each other & I've written their App in VBA, I just say, "Yeah, it's in the contract. I think you want to talk to Trevor Goodchild."
I run Postgres on the Mac, but customers always shun it. A lot of companies I've worked for or with need a good DB, but also something they can learn on. Access & Filemaker fit this, but they're crap. MySQL & Postgres are great to use once you know exactly how to implement what you want. But WTF, half of all Slashdotters seem to be Oracle DBAs! Must be nice, but it only reinforces the view of DBAs as cult members.
2000 is worse than '97, too. That whole fucking Outlook-style bar on the left side? Groups for tables, views, etc? If you have to organize your your db with shortcuts to even have a coherent front-end, then your DB is royally fucked up. Access forms are actually more difficult to set-up & maintain, and offer no more than much more cleaner, useful HTML forms. The "Query Builder" is also a fucking PITA. That's why SQL Server offers has a different, better version. VB is M$'s attempt to undercut SQL.
And VB! *%^&#*$%^*&%$@# VB! Most of what VB does has a better alternative in SQL92, which is even implemented in Access. So you have parallel ways to do the same thing! You have to decide--VB or SQL?--for each different application.
That's it, I'm all verklempt. Talk amongst yourselves. Here, I'll give you a topic: Visual Basic is neither visual nor BASIC.
HA!
"I am a warrior, and information is my weapon..."
...little baby row limits....
"I am a warrior, and information is my weapon..."
But he DID look!! It's just that the search engine came up blank when he typed in the string: "Don't use Access! It's unstable!"
:-)
Man, you guys have got to learn to account for the difference in capitalization.. Remember, the articles are written by someone highly trained familiar with all of Microsoft's tools. Remember, usage of the shift key is not in the typical training course.. Gotta search for "dont use access its unstable"
He'll go with Linux right away!
"Ancillary does not mean you get to rule the world." --U.S. Circuit Judge Harry Edwards, speaking to the FCC's lawyer
Yes, MS Access will work as a front end to other databases provided you have an appropriate OLEDB or ODBC driver for it. However, in my experience, it is a bit of a pig. My best experences have been to use it where the end user is a bit of a tinkerer and needs to create reports and queries and customize the database in ad-hoc fashion without professional intervention.
... or since he is so into MS, ASP (Active Server Pages)? In either case, you can use any backend database you want.
If your users will not need to evolve the system without programmer help, or you plan to have many users on at the same time you may want to talk your boss out of MS Access altogether. Can you talk him into browser based solution using JSP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~ the real world is much simpler ~~
--- -- - -
Give me LIBERTY, or give me a check.
Using mod_perl, DBI, and HTML::Embperl, you can put together a really nice database interface much more quickly and easily than with access. It will be much easier to maintain as well.
Access is ok to learn how a database works, and to do some small databases used by one or two people..but anything beyond that will be a PITA to administer. A web frontend that you can fully control while giving as many people who need it access is a much better way to do it. That way you are guaranteed that anybody with a web browser is already capable of using the database. No client side installation/maintenance headaches and mac/linux/os_2/whatever can all use the database without needing a crappy M$ app on the client side.
This would be a much better point, and smell less of FUD, if you could provide a link...
"I've seen plays that were more exciting than this.
Honest to god... Plays!" Homer Simpson
Too bad MySQL brought down my W2K Server...
It's easy. Just use MyODBC, and link an Access DB to MYSQL via ODBC. It's a great, easy-to-use front end.
No problem at all (apart from the obvious Access/win95 crashes
I think it is still a good way to quickly set up a nice interface to a simple database... but frankly in my last works I've found Web Applications written in PHP a far better solution for both flexibility and stability.
You can get the ODBC drivers here
If Godzilla did not exist, man would have had to create him.
Tell your boss he will need an extra stick of RAM in every Mac he wants to use Access after March 24th. MySQl & PHP will build on OSX. Perl ships with it. Tell your mac-loving boss that. With XonX you can have MySQLGUI so why Access at all? Do you really want to boot Classic to load Access?
At work, I have written several apps to connect to a central database (in Access on an NT server). My concern is with the scalabilty of Access. I am thinking about converting the database to PostgreSQL on Linux. My company does not "give a rats ass" on how I get anything done as long as it is done fast. My office is mostly a Windows shop though. How should I approach converting the database I built to PostgreSQL? Do people think it is a good idea or should I just think about converting to SQL Server instead?
My issue is that some _tables alone_ will have 500,000+ rows (with posibly ten people accessing the database at the same time) by the end of the year (some tables are growing by 20,000 rows a month). I know it is not remarkable to have several tables of that size but can Access handle it and is PostgreSQL a good alternative solution?
I miss the Karma Whores.
be careful with access. i have argued many a day away that access is not a viable solution for 'business critical' data, usually to no avail. also make sure that you don't have users with different versions of access...we have had many problems with users running access 2000 updating a access '97 database
The only problems is that I have are:
1. Some people at the office are still living in 1995, and see Access as a solid solution for building a whole system.
2. Access is not free. You will have to pay for every user (if I write the front end in ASP/Perl/JSP/etc then the user only needs a browser).
Pedro
Pedro
----
The Insomniac Coder
There's an article (not necessarily good, but useful) over at DevShed that explains how to get MyODBC to work with MySQL and then link a Microsoft system running MS Access to it.
Here it is.
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.
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? Are today's sql security models that inflexible?
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.
Once again, can't you just offer up views to limit what access sees (and do the joins to boot)? I'd be very surprised if you couldn't offer up a solution other than putting extracts on the users desktop, but then again, your the dba, not me :-) I _do_ feel access is too good a tool for me to go tell folks they have to do reporting from an excell spreadsheet hehe
My only real experience here is with Oracle type security and views, and then not as a dba, I don't know what kind of sql the open source servers have available, do they not support views and some sort of 'permit' type security or whatever?
I'm not arguing, I'm asking, as I'm not a DBA :-)
While I have no experience with the opensource db's, in the Oracle world, I'd even try to put the datamart on the back end, maybe sync it with triggers or something. (Thats not saying that snapshots are a bad thing or useless, but by definition, they're not the live data.)
Informative post tho!
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.
Ok, I'll bite, wheres your point'n'click query solution?
If your boss still insists, it's time to move on. You work for a micro-manager and that's the pits. Trust me.
Depends. There are micro-managers who respond to logical / cost-benefit type discussions, and there are micro-managers who don't. But your argument says your the inflexible type here, so I'd hazard a guess you'd be cranky in any job. (Still only a guess tho hehe)
Sometimes having a micro-manager means they're interested in what you actually do for a living, it has happened to me, both good and bad. *Shrug*
Don't touch ASE (Adaptive Server Enterprise). If you spend your life workign with ASE it can prove to be quite a nice database I'm sure, but unless you're willing to devote your life to it, it can really be a pig. ASA isn't so bad though. Jedidiah
Craft Beer Programming T-shirts
How? I've been trying to do this for some time now. Please email me!
-B...
...quit. or better yet, put up both versions of the thing (one with a perl frontend and one with access) and show him/her the diff. at the same time bet 4 or 5 employees to "beta test" it in fron of him making sure they point out the advantages of web-based. ya never kno.
ZERO
You shouldn't have to link the tables in Access, as other posts have suggested. You can do what is called a pass-through query in Access, that will allow you to send a query directly to ODBC without any pre-processing. The good news is that this allows you to do your processing server-side, and you don't use Access' peculiar dialect of SQL. The bad news is that you have to use ODBC's version of SQL, which is its own special kind of fun. Go to MSDN and look on their Universal Data Access site for ODBC, or look in the MSDN Library.
That having been said, I have to agree with the posters who have questioned the wisdom of this move. Access should work fine as a desktop report generator (as long as you don't have to support the messes that management will make of it), but MySql doesn't sound like as good a choice for a back-end database as MS SQL Server. Sometimes you have to look past ideologies and Do the Right Thing for your customer.
Ever try to "fix" a corrupt access database? Never could find the tool that would let me walk the data file and fix the pointers. Oh yes, it broke because of the poor built-in multi-user access. More then one client and watch your performance go out the window. DB2/Oracle/Postgres or the only way to go. (And if you must use a windows only, pc only product then www.rbase.com is for you).
What is pirate software? Software for inventory of stolen treasure?
So, I would say mySQL's tests are valid -- they simulate a production environment much better then TPC does (continuous operation for weeks on end is much better then a quick benchmark). Is mySQL up to the level of a database like Oracle or DB2? Nope. But neither is SQLServer, IMNSHO.
I'm not even going to get started on the "who has more bugs" -- we both know that SQLServer+NT will win in that department. I'm not just another Linux zealot, either; I administrate both Unix and NT systems (Solaris and Linux), and needless to say, we use Linux and Solaris pretty effectively (keeps costs down, too).
--
--
I Hit the Karma Cap, and All I Got Was This Lousy
Urm, they do test releases with production databases in the 100G range, plus "crashme", and thousands upon thousands of hours of user debugging time. Pray tell me why that qualifies as more of a toy than something like SQLServer or PostgreSQL?
--
--
I Hit the Karma Cap, and All I Got Was This Lousy
Small correction: Access + SQL Server doesn't use Jet. Jet is the mechanism for using native Access file format (.MDB) - which Microsoft is actually phasing out.
.MDB files (Jet engine) natively and everything else through ODBC.
Access is actually going through a transition with Access 2000 to a proper client-server system:
The old "Access database" format uses the DAO object model to access
The newer "Access project" format uses the ADO object model (which is much nicer than DAO) to access many different data formats (including SQL Server) natively, as well as ODBC. Access 2000 comes with a mini version of SQL Server called the Microsoft Data Engine (MSDE) that can be distributed for free. Access Projects are meant to be written for the MSDE, and can be scaled up to SQL Server without any new code at all.
As actually using it: It depends totally on what's meant to be accomplished. If it's just a a couple of simple forms and reports, they can be whipped out in an afternoon with Access. If it gets more complicated, all the stuff Access does for you automatically (updates, etc.) start getting in the way.
Convictions are more dangerous enemies of truth than lies.
- Nietzsche
Seems the number #1 problem you got here is Access on the Macintosh is a dead end which hasn't been updated for years. So it's basically not an option if yours is a mixed shop.
Otherwise, using Access with myODBC works fairly well for what Access is good at: a bunch of fairly simple forms and reports. Access' wizards and such make simple data input forms a snap, and it's reporting engine is very easy to use... certainly better than any of the web one out there if you need special formatting and the like.
The problem with Access is you lose a lot of control; Access tends to do lots of things for you, even if you don't especially want it to. I work in a Microsoft shop, and I tend to use VB instead of Access when a client app is desired... it's data access components are nearly as good as Access, but give you a lot more control.
But Web apps are definitely a better way to go. No worries about client-side drivers and everything... no worries about configuring the myODBC drivers and the ODBC settings and all... and no cross-platform concerns.
If this app is supposed to work on both Macs and PCs, that's your killer argument: cross-platform development is a bitch, and Access on the Mac isn't supported anymore.
If it's only supposed to work on PCs, point out the configuration and compatibility issues involved in installing client-side apps to multiple PCs. Unless it's a fairly simple app with heavy reporting needs, in which case it might be worth looking into Access.
If it's only supposed to work on one PC, take a look a Access' capabilities. It might save you days of work.
Convictions are more dangerous enemies of truth than lies.
- Nietzsche
Alot of the SQL incompatibilities are corrected in Access 2000.
Oracle is a damned pig to administer. 8i (8.1.6) installs in 'noarchive' mode which means you can do hot back-ups. To enable archive mode you have un-comment some lines in the init.ora file, which turn out to be coded wrong.
It took us a week and the help of an 'offical' Oracle DBA to get this fixed. Don't tell me how easy it is to administer.
-----------------
www.lucernesys.comHorizon: Calendar-based personal finance
For a web application, Access is about the worst database you can use. I would pick something like MySQL or PostgreSQL LONG before picking access. Access databases have locking problems and lots of other issues. Access really wasn't designed to be a multi-user database.
Thanks for the wonderful info - I really, really appreciate it. I had no idea that it was already being worked on, and that was where my frustration came from (and I couldn't find anything on the PostgreSQL site, so I guess I assumed the worst).
:)
Once it finishes beta testing, then I can probably consider Postgres for my projects again! Since the outer join issue was the only one that I personally encountered (I had no complaints about performance, and I hadn't encountered the bugs mentioned), I'm sure that I'll be quite thrilled with 7.1
Heck - I love 7.0 even. It was just a bit of a disappointment to discover that I couldn't do a straight port of my application at work because of this
Once again - thanks!
You can accomplish anything you set your mind to. The impossible just takes a little longer.
Really, using access as a front end isn't so bad and probably makes some sense. Access is probably microsoft's best application since it's so goddamned easy to make reports. In a lot of ways I think it beats crystal reports, and not only that but its got better GUI stuff. So... while it maybe be true that it would be cooler to use linux & php, from a business standpoint, access isn't really a bad choice. It might be more sellable. And access can hit anything w/ odbc as a backend so it doesn't really limit you database wise (if you don't let it, jet sucks, but it sounds like you're gonna be using a real db anyway). I've been using it as a front end for DB2/400 for years. -z
-Z
If 'I' were microsoft, considering the way that microsoft has proved they do business in the past, I'd consider slashdot one of the frontlines in the war on Linux. I'd have paid monkeys that did nothing but watch the forums here all day, and make at least one post for every anti-microsoft post to give the illusion of an enraged, clueful, competent userbase. Sometimes I see posts and I KNOW that's what must be going on because of the way they are written, and the total lack of actual technical information supporting the wild claims made by the microsoft sponsored posters. I then see these posts shot down and exposed for what they are by the mostly clueful and highly technical posters that assemble here. I wonder how much the job pays? I'm not sure if they have, but I doubt microsoft will ever mention slashdot anywhere, anytime, ever. They'd be too afraid that the people that bought into their religion would come here to investigate, and adopt the religion here. Anybody that would buy into the microsoft religion would be the type of person that would jump ship if they read enough of what's on slashdot. Lets hope they mess up and mention slashdot a few times. I wonder how many of the microsoft sponsored posters got curious and started running some free unix at home. Food for thought.
The most important thing any republican needs to know.
I suggest a life. getting one that is.
The most important thing any republican needs to know.
If 'I' were microsoft, considering the way that microsoft has proved they do business in the past, I'd consider slashdot one of the frontlines in the war on Linux. I'd have paid monkeys that did nothing but watch the forums here all day, and make at least one post for every anti-microsoft post to give the illusion of an enraged, clueful, competent userbase. Sometimes I see posts and I KNOW that's what must be going on because of the way they are written, and the total lack of actual technical information supporting the wild claims made by the microsoft sponsored posters. I then see these posts shot down and exposed for what they are by the mostly clueful and highly technical posters that assemble here. I wonder how much the job pays? I'm not sure if they have, but I doubt microsoft will ever mention slashdot anywhere, anytime, ever. They'd be too afraid that the people that bought into their religion would come here to investigate, and adopt the religion here. Anybody that would buy into the microsoft religion would be the type of person that would jump ship if they read enough of what's on slashdot. Lets hope they mess up and mention slashdot a few times. I wonder how many of the microsoft sponsored posters got curious and started running some free unix at home. Food for thought.
The most important thing any republican needs to know.
Most microsoft oriented people would stay away from a site like slashdot. The sheer number of 'linux sucks use microsoft' replies back my theory. Especially when you look at how the posts are done. All very hard to defend, all inflamatory, all lacking any technical information, all FUD.
The most important thing any republican needs to know.
If 'I' were microsoft, considering the way that microsoft has proven they do business in the past, I'd consider slashdot one of the frontlines in the war on Linux. I'd have paid monkeys that did nothing but watch the forums here all day, and make at least one post for every anti-microsoft post to give the illusion of an enraged, clueful, competent userbase. Sometimes I see posts and I KNOW that's what must be going on because of the way they are written, and the total lack of actual technical information supporting the wild claims made by the microsoft sponsored posters. I then see these posts shot down and exposed for what they are by the mostly clueful and highly technical posters that assemble here. I wonder how much the job pays? I'm not sure if they have, but I doubt microsoft will ever mention slashdot anywhere, anytime, ever. They'd be too afraid that the people that bought into their religion would come here to investigate, and adopt the religion here. Anybody that would buy into the microsoft religion would be the type of person that would jump ship if they read enough of what's on slashdot. Lets hope they mess up and mention slashdot a few times. I wonder how many of the microsoft sponsored posters got curious and started running some free unix at home? Food for thought.
The most important thing any republican needs to know.
The paperclip isn't really talking to you. It's a hallucination. I don't think computers are for you. Try WebTV.
The most important thing any republican needs to know.
Even though learning how to drive a car requires some learning and understanding of some advanced concepts, most people learn how. If they didn't, I'd be dodging a whole lot of people on bicycles while enjoying the speed advantage of my car. I'm sick of the excuses for being lazy.
The most important thing any republican needs to know.
So I guess the internet is 'real' now that it's been invaded by microsoft. Personally, I liked the internet a lot more before windows 3.0. It's become a corporate cesspool instead of the collection of intelligent, caring people it was. It would have been fine without the addition of big business.
The most important thing any republican needs to know.
As a database administrator having used Microsoft SQL Server, Sybase (ASE), and Oracle extensively, I think I have something to add to this discussion:
Don't use MySQL or Microsoft SQL Server. Use Sybase ASE 11.0.3.3. It's 100% license-free for use on Linux, both in development and production databases. It's solid, it's fast, and it's good.
Use SybPerl from Michael Peppler, use SQSH from Scott Gray for your DBA tasks.
Use whatever client you want, Access or otherwise. Sybase is a fairly significant industry standard RDBMS, and most everything connects to it.
To see an entire product built on open source components, try http://www.activespace.com. The backend is Sybase, the frontend Mason.
--
fifth sigma, inc.
You're absolutely right that only old versions are free for deployment and commercial use.
And that old version has triggers, stored procedures, guaranteed consistency, online database backups, support for terabyte-sized databases, and more.
What the fuck do you want? If you need more than that (ie: row level locking) shell out the cash for 11.9.2.
--
fifth sigma, inc.
MySQL is good at certain things.
Just don't make a choice to use it for political reasons. Make sure it suits the needs of your business. Otherwise, when the system doesn't work like managment needed it to and they hire consultants to come in and find out what is wrong and how to fix it, you will be screwed.
My inclination would be to avoid Access as well, since that can be an absolute nightmare to work with. However, if the users are Access literate and want to features that it provides, you do your company a disservice by trying to force a political decision on them.
Actually.. one of the cool things about doing this is that you can encrypt the connection by tunneling MySQL data through SSH. I had no problems with the MySQL ODBC driver... except for the fact that if you have office 97 or lower, you need to make sure you have all of the updates for it (I can't remember the problem, but you had to install the latest SR release). I think for this reason it offers a few more features, and given the quick access speed of MySQL and the features PHP offer it I think its a great alternative for a small-medium size database. Though do I *hate* a few things about MySQL.
2) find a better job where you can use Linux
I see. So companies which dont use Linux are not worthy of a solution? That makes sense since it's companies that do use Linux which have all the problems.
An idiotic statement if I ever heard one. Companies with programmers that breathe oxygen have all the problems. Now I'll suppose you'll suggest that oxygen is a bad thing.
There is no lower bound to the idiots here.
My feeling exactly.
A vote from me. We use it to post announcements
in our web site (Access -> MySQl -> WWW via Zope).
I don't even think it's bad, after all, the infrastucture is what counts.
As for how to do it, you'll be surpised when you
find out how simple it is whit ODBC. A hint:
it a wise idea to tunnel your connection using
SSH, so you can be pretty sure noone's sniffing
your mysql passwords.
--
Dimitris
Dimitris
Great job getting people excited. Funny I haven't heard anyone mention DB2 or Teradata. It would be really surreal to see someone claiming that MySQL was of the same calibre as DB2, SQL Server, Teradata, or even Oracle. But it is quite possible that MySQL is "good enough" for what this fellow wants, and therefore not a terrible business decision. This guy clearly wasn't the sharpest or most experienced DBA on the block, so if he is "good enough" for what that company needs, maybe MySQL is, too. We already have a huge shortage of resources in the IT industry; if everyone demands to have only the best (and ignore other satisfactory alternatives) we'll just make the shortage worse.
-- Respect for the word - to employ it with scrupulous care and an incorruptible heartfelt love of truth - is essential
I setup a backend database using MySQL at my old office and used MS Access through ODBC as a client, as well as using the console client and Perl. I used "linked tables" to reference the MySQL tables from Access. This worked well, although some datatypes were incompatable. Must say I preferred the MySQL datatypes to Access'. Unfortunately Access 2000 seemed to have more incompatabilities. For databases involving complex queries I'm not sure I would go with MySQL based upon my experiences. However, the ability to use text based clients and Perl was wonderful because it meant I didn't have to waste time with umpteen mouse clicks. I also tried PostgreSQL but it was unable to transfer large quantities of data via ODBC. Now that I've gone I think the company is looking to purchase SQL Server. At least my new employer is Linux friendly!
I've been doing this for about 6 months now. Here is some advice: The ODBC drivers are buggy, but they are stable enough for production use (honestly). It's imperative that you match the correct driver with the correct version of windows. We've found that some versions work on W95C and but not on B. Really weird, but that's M$ for you. We've only used Access for simple databases, but we have a huge database written in Paradox running through the Boreland Data Engine and then through the MySQL ODBC driver connected to a FreeBSD box running MySQL. It works great. Personally I'm really happy with the interface. One last note. DO NOT use Access for databases that are accessed by more than a few people at a time unless you have _some_ sort of SQL server on the back end. The native concurrency stuff in Access doesn't work worth a hoot when you've got a lot of users. You end up with corrupted .mdb files every other day.
Personally, I recommend the Access->ODBC->MySQL setup. You can't beat the price on the server end!
Why not use Sybase 11.xx or 12.x it's a fullup database server and supports a full SQL language. IT makes a lovely Access backend. RJ
Postgresql has an ODBC driver specifically designed for MS Access front end interface. Postgresql has stored procedures, triggers, etc. and is actively developed and supported. Your boss may love it for just those reasons. That, and it's much cheaper (free for a start) than MS SQL Server. Until someone comes up with an MS Access, friendly interface for Linux environments (want to give me the money to quit my day job and part-time evening/weekend job so I can do it?), then we're stuck with what's there, AND with what the users can use who use the data. (Say that a hundred times.) http://postgresql.readysetnet.com/
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.
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.
m an ual_ODBC.html
Here's a section of the MySQL manual you should read beforehand:
http://mysql.com/documentation/mysql/bychapter/
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...
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.
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).
If tits were wings it'd be flying around.
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.
If tits were wings it'd be flying around.
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.
Finding God in a Dog
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,
--Tom
Tom Geller
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.
D
----
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.
-josh
From this page on Sybase.com:
"
Adaptive Server Enterprise for Linux version 11.0.3.3 download
Adaptive Server Enterprise for Linux version 11.0.3.3 continues to be offered as a free, unsupported release for development as well as deployment. Adaptive Server Enterprise for Linux version 11.0.3.3 is available from the Sybase Adaptive Server Enterprise for Linux version 11.0.3.3 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.
Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde. - Voltaire
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
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.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
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".
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
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.
War is necrophilia.
A great Idea. As a bonus it will have good compatibily with access.
War is necrophilia.
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.
perl -e 'fork||print for split//,"hahahaha"'
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.
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.
--Lawrence Lessig for Congress!
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.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
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.
You will forget this sig before you next see it
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.
INTERBASE SPECIFCATIONS
Integrity
- 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
Availability- Online backups
- Immediate recovery after failure
Distributed DatabaseRohan
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.
- 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.
- 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.
- 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.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.
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.
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.
Friends don't help friends install M$ junk.
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.
--
Free Software: Like love, it grows best when given away.
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.
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.
I used up all my sick days, so I'm calling in dead.
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:
OS:
BSD
Sun (SunOS Solaris)
Linux
NT
DB:
Oracle
DB2
MySQL
MsSQL
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.
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.
Amusing excerpt:
Q222135 - ACC97: Using Microsoft Jet with IIS
Amusing excerpt:
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.
Vintage computer games and RPG books available. Email me if you're interested.
...it 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.
:), 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.
SQL Server is actually a very, very good product. Probably because it originated outside Microsoft
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.
Cheers
-b
If I wanted a sig I would have filled in that stupid box.
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
The Kruger Dunning explains most post on
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.
http://208.160.255.143/pgsql/pgsql.exe
Daniel
Friends don't let friends use multiple inheritance.
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.
"It is a greater offense to steal men's labor, than their clothes"
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.
---
Video meliora proboque deteriora sequor - Ovidius
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?
-- dR.fuZZo
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. :(
creation science book
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....
**Vanuatu or bust**
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
or,
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
UNION
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.
You can accomplish anything you set your mind to. The impossible just takes a little longer.
Let me preface this by saying that I have done a lot of work over the last few years using Access. For some things, it's a great tool. For others, it's not.
It shouldn't mean leaving your job to do what you believe in.
It's fairly trivial to convert a set of access tables and queries to SQL create statements that will re-create your design in MySQL, so...
Design your database in Access. Design it well - Get the tables right now and you will save yourself a lot of trouble.
A bad design in Access won't be any better in MySQL, trust me.
Be patient and do what you're told - do it in Access. When you design it, keep in mind that you might be upgrading to MySQL. Find out where it might break and avoid those areas.
Find out the advantages to using MySQL and promote them when the time comes. One example would be distribution of the data over the company intranet. Explain that web-based reports can save you installing Access on everybody's machine. In my case, that was the decision point - The company had not bought licenses for everyone to have Access on their desktop. Explain that a switch to MS SQL Server will also require client access licenses. Explain that MySQL can be had for free. (Actually, don't say 'free' - say 'MySQL doesn't require CAL's for the clients and will do what we need.')
Remember that MySQL can be had for NT, too. That may sit better with the PHB's.
Write some VBA to create MySQL create statements from your Access tables. Attach the new tables using MyODBC. Move the data from Access to MySQL. The other developers and your boss will be more comfortable seeing their data as familiar Access ODBC-linked tables than they will seeing it as text output on a terminal screen.
Write documentation that Windows people will understand. Do a newbie howto that tells exactly how to create the database and build the tables, step-by-step. A lot of the fear of using open source tools is that they can't find people who know it. Make your docs such that any MCSE can install and administer it. Burn a CD that has all of the required binaries and a script that will install everything.
You'll probably have to downplay the Open Source aspect of all of this. Show them that to use MySQL, they don't have to abandon Windows. Just let them know that they are choosing a robust, stable place to put their data that will work on several different server platforms.
Also, keep in mind that MySQL may not be the right tool for the job. Be prepared to use whatever is best to get the job done well, be it Access, MS SQL, MySQL or whatever.
Good luck!
Cheers,
Jim in Tokyo
-- My Weblog.
Well, I agree, but there is definitely some boss management that has to be done here. You don't want to put yourself in a situation where you are responsible to implement a strategy that is going to fail.
The problem is that Access (W/o sql-server) is actually less scalable than FileMaker Server. I know this because I run into people who are living in the crashed ruins of both Access and FileMaker projects -- it's a good part of my business.
In the application development domain, most people will end up taking FileMaker further than Access. This is because FileMaker is not a relational database -- it's an easier model for non-expert database designers, more forgiving. It's usually possible to recover failed homegrown FileMaker solutions with some general business application expertise. Similar Access solutions are usually a total loss.
Access is really a gateway drug for MS-SQL and VB. It's very well tuned to give people in the early stages of development positive experiences, but then it breaks down as performance requirements increase and the application increases in complexity. The solution to this is MS-SQL (or another client server database) and to hire an experienced business application developer (and possibly a platform change).
It's not like you can't create Access applications (using the SQL Server back end) that are scalable in complexity and performance. It's just that in practice the successful Access "applications" I've seen are personal or small departmental data entry systems of very limited scale and purpose.
Personally, I use Access for lots of "quick-and-dirty" kinds of things, such as doing data conversions. I think it really shines there (aside from its tendency to pessimize queries to external data sources). I would probably not use it for serious business application development. If you truly have outgrown FileMaker, then you should turn your attention to tools that are designed for professional application development, such as Delphi, PowerBuilder and VB. Actually, these days given my druthers (which as a developer you seldom have) I'd use Java, or for some applications Zope and Python.
I sympathise with Daniel's boss. Microsoft looms large in the imaginations of Mac enthusiasts -- implacable, evil and undefeatable. Implacable, maybe, but evil is questionable and undefeatable is certainly not true. It is simply not the case that there is no other way to survive but to go completely into Microsoft's embrace. It's just that a lot of the creativity that made the Mac platform an exciting place to be in the late eighties has moved elsewhere. Remember getting the huge stack of floppies of incredible freeware applications from your user group?
Where has all that engergy gone? You should take your boss on a tour of sourceforge. Daniel should take his boss on a tour of SourceForge. The difference now is that the free software/open software movements are not a single corporate entity that whose air supply can be squeezed off. They're more like a highly opportunistic fungus that spreads around attempts to control it and leaves spores (source code) where it has been extirpated.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
No, no! He obviously wants to run the postgres setup.exe under wine! ;)
- - - - -
Napster-to-go says "Fill and refill your compatible MP3 player", which is a lie. It's not MP3. It's WMA with DRM.
How many times do we have to tell the "pointy haired boss" that politics does not make good software?! You need to impress upon your boss the fact that all marketing and promises aside, Microsoft Access is not a scalable or stable solution!
When will people learn to delegate responsibility fully to the people who know how to do their jobs best? By not trusting you, the knowledgable staff member, on your design decisions, your management is suffering themselves no small number of headaches for the future of their company. By locking you into an environment that you are 1) not comfortable with, 2) do not trust, 3) doubt will fit the bill, and 4) dislike, they are seeding the crop of their own distruction.
Take a stand. Review your software design goals, and research the proposed tools and environments to do the job. Include in your research their proposals, and work to disprove them based on your own knowledges and instincts. If they can't trust you, their developer, and would rather go with what a marketing drone would recommend, tell them to hire the marketing drone to write the software.
On a less general note, my advise about writing RDBMS-based software:
What can I say. Access + MySQL sounds like a loosing combination. Access and MSSQL sounds like a better combination, but I would opt for something along the lines of PostgreSQL and Java (though the client-side SWING sucks ass). If you design the application with at least a three-tiered approach, client-server-database, you will be allowed some flexibility in which direction you can migrate with DB selection and client selection.
Anyway, I have to go to sleep so I can get up tomorrow and program... Good luck! Oh, go check out some of the projects like Enhydra.org or jboss.org if Java+Appserver+RDBMS+web raises an eyebrow or two.
--
assert(expired(knowledge));
Go with PostGreSQL instead.
--
It's rare that you're presented with a knob whose only two positions are Make History and Flee Your Glorious Destiny.
I have done something similar to the above with Postgresql and MS Access.
I have a stock Redhat 7.0 box with Postgresql running with an MS Access front-end. It works quite well. Use the postgres odbc driver and create link tables in the access database.
The biggest quirk that I have seen it that booleans don't work well, use integers. Access likes to use (0,-1) as false,true. The odbc driver translates booleans as a single char. use a "yes/no" data type in Access and an "int4" in postgres and the rest should be fine.
This system has been up and running for over a year. although, it's not under very heavy use. The secretaries update personel info in the database. They only have to update things once. The web server pulls data from postgres (ala php), so the secretaries update my web pages for me! makes my life easier.
I'm part of a team who works with PostgreSQL & ODBC connected to ColdFusion on a dynamic website.
:). I can at least tell you that it's been done, this is what the tools were DESIGNED to do, and that I've personally seen it all put together before. I can also tell you that it wasn't any major feat to get it working (it stumbled into our lap basically). The guy hated working with the command line psql client (read: he couldn't)... so he slapped Access up against it.
One of the guys, who has zero education when it comes to coding, databases, and computeres in general (don't ask me why or how he's a programmer.. I have no idea) managed to whack Access up against the PostgreSQL database via the ODBC portal I had done to it (can't remember how... it wasn't much of a thing).
It certainly isn't a revolutionary idea, people do it all the time... it's a two step process:
Setup ODBC to the database
Setup Access to hit an ODBC source (doesn't matter WHAT it is)
Yes, you don't want an "RTFM" post but really.. that's what you'll get
If you do a search in the MS knowledge base, look for access, thread, multi, and a few other things, you'll dig up the MS KB articles that say "Don't use Access! It's unstable!" It amuses me how the same people/companies that wouldn't use Word for creating a thousand page catalog, and wouldn't use Excel to write a corporate accounting system would, in fact, use Access to create an 'enterprise' database.
Vintage computer games and RPG books available. Email me if you're interested.
I agree that MySQL isn't ready for primetime yet, but it isn't the only open-source free RDBMS.
You are being equally rediculous by insisting on using MySQL even when it is a non-optimal solution, as your boss was to require you to use Access! While SQL Server is usually less efficient for non-transaction based databases (since MySQL doesn't support transactions), it works well with Access. Instead of jury-rigging a MySQL Access ODBC driver, you should use MS Jet for SQL Server. That interface will likely allow for faster DB interaction than a jury rigged driver.
If you really have a problem using MS products, you should find a job where you are able to develop on an OS platform. Even though I tend to use more MS software than I have to, I understand where you are coming from. It is much easier and more efficient to develop a DB front end using Perl/DBI than Access/VBA.
Lenny
ByteMyCode.com: A Web 2.0 code sharing community.
We've got some clients who are comfortable with Access, so they use Access with the MyODBC driver, and hit the MySQL on the back end. They simply update their data in Access, and it's "live" on the site instantly.
:)
That's the only major downside to this - people who work with Access aren't always used to thinking that thousands of people will be affected by their data changes in real time, so we don't give this functionality to all client.
It's also nice for them to be able to create their own reports based on live data from the website, rather than having us build web-based reporting tools. Yes, it's nice extra work, but when you don't have time and/or they have a budget, letting clients develop things how they want with their own tools is a nice option.
creation science book
The poster didn't say whether they already HAVE SQL on NT, or whether it might be an option later. Agreed, if they HAVE it already, just use it. If they DON'T have it, spec out the requirements. No doubt SQL Server has many good points, and for most jobs will be stronger than MySQL.
But... if they don't need it, why spend the money? We just completed work for people who bought SQL2000 and 2 W2k boxes, THEN brought in someone to build the website, before it was specced. We built on it, and yes, it worked fine, but for their needs, ONE (maybe two) Linux boxes with MySQL would STILL have been overkill. I have 1 box handling 2-3 times their load, and it's a 300mhz with 128 megs of RAM. They've got 512 meg systems, dual processor 750 mhz, RAID, etc.
Again, nothing wrong with SQL Server, but it's overkill in many situations. We prefer to inform clients of their needs first, and make purchase recommendations after needs analysis, not before.
creation science book
It comes down to this:
If you're the DBA and you've been given the responsibility of implementing the data storage at your company, you should be the one who decides what system is used.
Gently explain this to your boss. Tell him you understand that he may have a preference toward Access, but you know from past experience that Access will not meet your long-term needs and you recommend something different.
If your boss still insists, it's time to move on. You work for a micro-manager and that's the pits. Trust me.
--
All opinions presented here aren't mine.
Interbase has been open sourced and is multiplatform. So even if your boss is determined to use a NT/2000 server you can always move to Linux later and keep the exsisting database. MySQL lacked any stored procedure support the last time I was using it. Now that I've worked with oracle, ms sql, and interbase I don't think I would want to make a database application/site that didn't support a stored proc. I'm sure there are many ways around them but from a programming aspect I find stored procs have their uses to make the overall programming easier to maintain and understand.
Also if (which I doubt you would get any from M$ for free) free support isn't a concern then the downloadable version of interbase works quite well. I'm currently redesigning a project from paradox to interbase. Access like paradox is a non intellegent database which requires all work to be done on the client side. The SQL server has intellegence on the server side. The other aspect of a SQL server is that the users don't have to have any access to the directory where the database actually is. I have had users accidentally delete tables before since they have to have read/write access to the tables.
Depending upon your project MySQL could be just fine, it can be run on NT also. I think if you look at the requirements of what your company needs, and which database would better fill those needs, that would make a better argument for the M$ for everything mentallity. Business oriented people will be more impressed by the business side of the argument than the technical aspect.
If ignorance is bliss, the world is full of blissful people
It might be better to pick one of the following:
1) implement with Access like the boss wants or
2) find a better job where you can use Linux
This is not a troll, or a flame, or offtopic. I'm serious.
If tits were wings it'd be flying around.
I don't have too much experience with Mysql but here are some problems I have run into with postgres.
1) Make sure the ODBC driver uses cursors. If not Access will attempt to pull all data from your tables for a simple select queries. This is especially true in forms bound to tables (a bad idea in the first place).
2) the default setup of access runs queries in a case insensitive matter. Make sure you turn on the case sensitive option. Do this even if you go with ms-sql it will make migration easier when you ditch it later.
3) Make sure every table has a primary key and the index name of that primary key sorts alpheitcally first. Name your primary keys AAAAAPkey_tablename or somthething.
4) put in a timestamp field in every table it helps access out.
5) Keep your table names relatively short. Access and SQL server let you have very long table names while Oracle, postgres, db/2 will limit it. If you want to upgrade your sql server later you will be glad you kept your table names short.
6) On a similar note don't put stange character in you field or table names. Don't name objects fax# or discount% also don't use a number as the first character of an object name. Access will let you but your dabase might not. Basically it's best to limit yourself to alphanumerics and maybe the underscore.
7) Make all your table names uppercase. This is not strict but it's a good idea. If not at least try to keep a good method for capitilization and stick with it.
8) Try not to depend too heavily on stored procedures. Although they can buy performance they make switching databases very hard.
War is necrophilia.
perl -e 'fork||print for split//,"hahahaha"'
Imagine you have an order entry form, with the top half devoted to order related fields and a sub form that has the line items for the order. Now both the main form table and subform table have pkeys that are auto_increment. In plain old access this is no big deal, but with access/(odbc)SQL you have a big problem.
According to the MySQL mailing list and the MSKB, this is because Access will re-select the data it just inserted to find the row it THINKS you are working on. That's how it get's the database default fields to take effect and the auto_inc fields. Since, for example, you might have many orders with the exact same order line information, it may or may not find what you want in that subform. Same thing for the order header, if it's a stock order from week to week, then it may not find it.
Their solution is to make every form that adds records have a field that inserts 'now' so Access can find your row better.
An ugly kludge if you ask me, but I worked with it. And when I was done I started installing ODBC drivers on every machine and making sure the DSN was right and making sure the proper version of Access was on the machine. This was the other big problem, the amount of software required to make the system work on the client side, it's un-believable. So I've abandoned Access/anySQL and now work with just web-based applications. Only software required is a browser and thanks to MS and the popularity of the internet, that means everyone will have it in one form or another. It also means people can work from home, the office, vacation, whatever.
The only drawback was rapid app development, sql forms and even forms in general require so much bitch-work to get working it's just frustrating. So I've built some perl modules that handle SQL forms generation, binding to fields, verification, and insert/update/delete functionality from data descriptions. The bonus of this is I can make the updated forms definition a module and re-use it. Now, in access, if they say "we want 1 more decimal of precision here..." you've gotta find the damn field on every form and change it there, no re-use. The only thing missing (and actively being developed) is a reporting module to generate text/html/pdf/etc reports.
Free Online Woodworking Resources Directory
I have actually looked into the feasibility of doing something like this before, and I think you may run into some problems. The main problem is that what Microsoft calls SQL in Access is very misleading . . . while it is structured, and is a query language, it isn't SQL. The Access version of SQL really just gives the ability to have text based representation of what can be done using the GUI queries which are needed to compete with Paradox and other proprietary databases.
In addition, Microsoft has extended the MS-SQL language to use its own proprietary extensions. Microsoft has taken the attitude with Access SQL that they take on everything else - they program what they want and the standards be damned.
The major differences between Microsoft Access SQL and ANSI SQL-92 are listed in a table on page 190 of "Access Developer's Handbook" by Litwin, Getz & Gilbert from Sybex.
Here's a quote from the above book, "Access SQL is a hybrid SQL. It differs considerably from each of the SQL standards and doesn't completely support any of the ANSI SQL standards. It lacks large chunks of the standards, particularly in the areas of security and cursors. Sometimes it supports the same functionality found in one of the standards, but with a different syntax... In other cases, similar functionality is provided elsewhere in Access... Finally, Access SQL has some useful extensions that are not present in any of the standards..."
Overall, you may have some difficulty using Access with MySQL. If your boss is making you use MS on the frontend, it really will save you some trouble to use MSSQL on the back. If you do decide to go ahead with MySQL, my only advice is to make sure it is a well-researched decision. If you don't know exactly what you are doing and it doesn't work, it will just bolster your boss's attitude that all linux / open source solutions are problematic.
Black holes are where god divided by zero
http://www.devshed.com/Server_Side/MySQL/ODBC/
"Learn it from start to finish. Installing MyODBC, creating a new data source through the ODBC Data Source Administrator, linking a MySQL database into a new MS Access database, and finally updating the MySQL database through an MS Access GUI."
Enjoy
This paid my last vacation, it mi
ODBC Socket Server is an open source database access toolkit that exposes Windows ODBC data sources with an XML-based TCP/IP interface.
It has clients for PHP, Perl, C (in Windows, Mac, and Linux), Java.
Let's see if I have this right. You don't want to use Microsoft SQL Server on a Win2K box, and you'd rather use MySQL on a Linux box. You make no mention at all why you want to do this.
I have no clue what kind of application you have -- what the nature of your data is, or the nature of the front end is. I have absolutely no idea why you think a web browser front end, PHP middleware, and MySQL back end is the superior solution. (Or, perhaps by PHP front end, you were refering to that GTK/PHP toolkit mentioned on Slashdot a few days ago, and you want all the users to have a Linux box on their desk. I don't know.) I have absolutely no idea why you believe Microsoft SQL Server and a MS Access front end to be an inferior solution.
What I do know are these things. MySQL and Microsoft SQL Server are two vastly different applications. As a DBA, you are obviously aware that on a technical level, Microsoft SQL Server is the vastly superiour database. In a shop that is almost entirely Microsoft, with only a few Mac's, the MS SQL Server is going to vastly easier to keep running, unless you plan on hiring consultants or tech's dedicated to keeping the only linux box alive.
Face it... they're on completely different planes -- the only place that MySQL beats MS SQL is on speed, on only a subset of the queries that MySQL is able to perform. You won't find any other comparisons of the two databases, becuase MySQL simply will never, ever be able to do any of the things that MS SQL does, and does well. A fair, unbiased consumer reports comparison of the two databases would be nothing but hundreds or thousands of checkboxes that give MS SQL two or three or four stars, and give a little "N/A -- not available" mark to MySQL. It would be a joke.
I'll freely admit that there are many places why MySQL is useful. The standard Linux or BSD box, running Apache/mod_perl/PHP, with a MySQL database is a tottally rocking deal. If you're a decent Unix hacker, it's easy to keep running, it's extrodinarily flexible, it's easy to learn to use, and it's fairly well documented. For 98% of the websites out there, it's plenty good enough. You can even set one up in your office on a spare pentium 75, just to try stuff out before going live with it. I love the combo, and it's what we use on a lot of the sites we develop at work. But for the people who need it or want to pay for it, we use a real database. MySQL is a great database for developers who are aware that it is not a great database, and can explain why.
But for running a dedicated database machine, hooked up exclusively to a group of MS Windows clients running a MS Access front end, in an office where the DBA isn't smart enough to make ODBC work, and the DBA's boss only uses Mac's, I can't think of a single reason you'd want to use MySQL. Not one. Having a wierd box in the corner running a wierd database on a wierd operating system that only one guy in the office understands, duct-taped up to a bunch of Windows clients, seem like the perfect recipe for a maintenance disaster a few years down the road. What a crappy idea.
Slashdot is jumping the shark. I'm just driving the boat.
Here's a step by step of what I did to get PostgreSQL 7.1beta running with ODBC that might be useful.
You can hate Microsoft as much as you want, but MS-SQL is a whole hell of a lot better than MySQL. There's places where all these wonderful OS applications don't work nearly as well as a proprietary solution, and databases is definitely one of those places.
MySQL is a great DB for fast read access where things like row locking aren't very important. Great for the web when you don't want to spend a lot of money. But if you've already got SQL then using MySQL is just plain dumb. SQL on NT will out-perform MySQL in just about every way. Not using it just because you don't like Microsoft could get you fired, and IMO it would be justified.
As an extension of the above, and table creation must take place in MySQL and then a link in Access created. Table mods must also be performed in MySQL afaik.
I haven't done this in ~2 years, but that's how it worked then, so take it (above comments) with a few grains of salt.
--
Never meddle in the affairs of dragons,
Never meddle in the affairs of dragons,
for you are crunchy and good with catsup.