Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Microsoft Access As A Client For Free Databases?

Cliff posted more than 13 years ago | from the it-would-work-nice-as-a-front-end dept.

Linux Business 353

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

"Can this be done effectively? Is there any good documentation on connecting Access to Non-Microsoft DB servers in general, and MySQL in particular? No 'ODBC RTFM' flames, please; I'm looking for something a little deeper here."

I'm not very familiar with ODBC beyond it's basic use, so I really can't answer this question, but might this be possible if the MySQL server were somehow treated as an ODBC source? It would be tremendous coup if somehow the power of the Access front end could be used with MySQL (or PostgreSQL, or mSQL or any other open sourced RDBMS)? MySQL does have some ODBC functionality and as well as other ODBC related links on their site.

For those of you who want to immediately play around and see if this is possible, you can find the MySQL ODBC driver, here and a PostgreSQL ODBC driver here.

(My apologies. The story went live right as I was still editing it to include links in the last two paragraphs...)

cancel ×

353 comments

Sorry! There are no comments related to the filter you selected.

odbc. (1)

Anonymous Coward | more than 13 years ago | (#385543)

mysql does act as an odbc source quite nicely. heck there are open source odbc drivers out there for it -- look on freshmeat/google.

ODBC with MySQL (1)

Anonymous Coward | more than 13 years ago | (#385545)

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

Re:If you're the DBA... (1)

Anonymous Coward | more than 13 years ago | (#385546)

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.

Re:That's a rather idiotic idea (1)

Anonymous Coward | more than 13 years ago | (#385548)

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

Re:hahahaha (1)

Anonymous Coward | more than 13 years ago | (#385549)

Nah, it just corrupts the data/

Re:That's a rather idiotic idea (1)

Anonymous Coward | more than 13 years ago | (#385550)

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.

Why? (1)

Hawke (1719) | more than 13 years ago | (#385552)

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.

Re:Why? (1)

Hawke (1719) | more than 13 years ago | (#385553)

Er, brain fade. "The backend to access is based off of Sybase." Sorry bout that...

Re: Bagh...Humbug (1)

KnightStalker (1929) | more than 13 years ago | (#385554)

I'll second that. Besides, this can be answered with about two minutes of looking at the MySQL home page.

MySQL as an ODBC source (1)

pberry (2549) | more than 13 years ago | (#385558)

Check this page [mysql.com] 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.

MySQLACCESS (1)

N8F8 (4562) | more than 13 years ago | (#385561)

Lets see here:

Re:Why? (1)

IntlHarvester (11985) | more than 13 years ago | (#385577)

MSDE is actually only a "dumbed-down" version of SQL Server in that it doesn't ship with the normal admin tools and is tweaked to run in low memory situations. Other than that it's the real thing.

As for the "Free Beer" nature of it - I'd double check that. When we looked into it for SQL 7.0, you needed a full MS-SQL Client Licence to use it, which runs about $200/seat. Which is great if you've already bought MS-SQL, but if you are using another vendor, you might want to check on their desktop solutions.
--

Re:That's a rather idiotic idea (1)

Zico (14255) | more than 13 years ago | (#385583)

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,

This Article ... (1)

trexl (16434) | more than 13 years ago | (#385586)

http://www.devshed.com/Server_Side/MySQL/ODBC/ On Devshed explains the usage of Access as a front end to MySQL. Just like everybody has said, but with specifics on setup.

Your boss is going to REALLY regret this decision (1)

GuNgA-DiN (17556) | more than 13 years ago | (#385587)

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.

Re:We do this in some cases (1)

flink (18449) | more than 13 years ago | (#385590)

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

Re:Why? (1)

synx (29979) | more than 13 years ago | (#385600)

not this bullshit again, _sigh_.

Well, since mysql is using db3 with XA support, I guess I can throw out my legacy Oracle database and bolt in mysql in my transaction monitor system.

Anyways, I won't say anything about mysql, except, I've lost my faith in it.

I've tried this before.... (1)

pxpt (40550) | more than 13 years ago | (#385607)

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

Re:Use Access? Erm... no.... (1)

pxpt (40550) | more than 13 years ago | (#385608)

I agree with most of your points about Access. However, it DOES come with good reporting facilities.

Re:ODBC driver available for PostgreSQL (1)

divec (48748) | more than 13 years ago | (#385614)

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.

Re:That's a rather idiotic idea (1)

BenLutgens (56508) | more than 13 years ago | (#385619)

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

Access and GPL database is the way to go (1)

dybdahl (80720) | more than 13 years ago | (#385635)

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.

Re:Don't weasel around the boss (1)

GrEp (89884) | more than 13 years ago | (#385640)

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?

Re:Why? (1)

god_of_the_machine (90151) | more than 13 years ago | (#385642)

To clear it up: MS Access ships with the MSJET engine, which they developed themselves, while SQL Server is based on the Sybase-Microsoft joint project which developed a D.O.A. database for OS/2.

Access 2000 ships with the MSJet engine by default, but you can easily configure it to use a dumbed-down version of SQL Server called Microsft Database Engine (MSDE) which only allows 5 concurrent users and doesn't allow use of the SQL Server Data Transformation Services (DTS) but is available for free (beer) license and distribution. Access supports being front-end to MSJet or MSDE as well as SQL Server.

-rt-

half right... (1)

god_of_the_machine (90151) | more than 13 years ago | (#385643)

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-

not a "'ODBC RTFM' flame"... (1)

god_of_the_machine (90151) | more than 13 years ago | (#385644)

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

MyAccess - MySQL (1)

giberti (110903) | more than 13 years ago | (#385657)

MyAccess [accessmysql.com] 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...

yes it can be done (1)

kuiken (115647) | more than 13 years ago | (#385663)

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

Re:Why? (1)

pi_rules (123171) | more than 13 years ago | (#385666)

Major brain fade there man... the word 'Sybase' wasn't even mentioned in the post. It's a FileMaker implementation right now.

Simple (1)

SpanishInquisition (127269) | more than 13 years ago | (#385667)

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

Re:Simple (1)

SpanishInquisition (127269) | more than 13 years ago | (#385668)

I never said that no lying is invoved, but a the higher level of managment, facts are not that important.
--

Use Linux anyway (1)

hansonc (127888) | more than 13 years ago | (#385669)

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.

Ripping off Sybase... (1)

ccoakley (128878) | more than 13 years ago | (#385671)

I'll comfirm it for you. It is a well known fact that Sybase and Microsoft developed SQL Server together. Pick up a copy of O'Reilly's Transact-SQL Programming for a bit of the history of the two products. However, to call it a rip-off of Sybase might be a bit extreme. In 1993, the two companies ended their partnership, and have diverged quite a bit.

Re:Don't weasel around the boss (1)

tetrad (131849) | more than 13 years ago | (#385672)

Actually he says that Access is merely the front end, and that the choice is really SQL Server or MySQL for the back end.

Locking issues shouldn't be an issue with either.

tetrad

because (1)

ArchieBunker (132337) | more than 13 years ago | (#385673)

he is not the boss, and his job is to do what the boss says.

um (1)

ArchieBunker (132337) | more than 13 years ago | (#385674)

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.

Sure, myodbc (1)

lamp77 (147098) | more than 13 years ago | (#385682)

I believe mysql actually comes with myodbc drivers. pop them in and away you go.

"Only amateurs attack machines; professionals target people."

Re:That's a rather idiotic idea (1)

subsolar2 (147428) | more than 13 years ago | (#385683)

Well I would take small exception to this. Since they are not currently (judging from the question) MS-SQL ... so it might be a bit pricey to go with an all microsoft solution.

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

MyODBC (1)

akiaki007 (148804) | more than 13 years ago | (#385684)

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.

Re:We do this in some cases (1)

spezz (150943) | more than 13 years ago | (#385686)

We've done it too. I used to work at a law firm that was concurrently running an old Foxpro database, and a fairly recent version of Mas 90 (financial software) and my boss wanted to compare information in the two.

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.

Yes, it works. (1)

MikeApp (151816) | more than 13 years ago | (#385687)

Once you've got the MySQL ODBC driver set up, just do File->Get External Data->Link Tables in Access. Caveats:
  • 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!!

Windows ODBC Driver for MySQL Server (1)

cmat (152027) | more than 13 years ago | (#385688)

Hi, I just actually started do this for my company, and found that if you take a look at the windows ODBC drivers [mysql.com] 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

Re:We do this in some cases (1)

cmat (152027) | more than 13 years ago | (#385689)

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

Re:Windows ODBC Driver for MySQL Server (1)

cmat (152027) | more than 13 years ago | (#385690)

Thanks... It is amazing what a google search will turn up. But then again, you have to understand what the results mean. You just found the exact same thing (although developed by a third party) that I had linked to, whereas I was actually looking for connection strings, a bit different from the Run install sort of documentation. ;)

Cheers,
Chris

Re:We do this in some cases (1)

cmat (152027) | more than 13 years ago | (#385691)

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

Re:That's a rather idiotic idea (1)

jshindl (157371) | more than 13 years ago | (#385697)

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! [silverscape.net]

Access is nice for low-cost ISP situations (1)

shodson (179450) | more than 13 years ago | (#385711)

One of the benefits of Access it that the entire database can be deployed (on a windows box at least) with just one file! There is no need to install an RDBMS.

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?

Re:um (1)

flikx (191915) | more than 13 years ago | (#385717)

Yes, I did. I found that a stupid solution was being called for. I would not do it willingly, nor would I wish to accept responsibility for when it goes belly up. The best thing to do was to walk out, and let them find someone else willing to do it that way.


--

I say DON'T! (1)

flikx (191915) | more than 13 years ago | (#385718)

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.


--

the power of Access??!? (1)

Idolatre (197068) | more than 13 years ago | (#385721)

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

Re:Why? (1)

mgkimsal2 (200677) | more than 13 years ago | (#385722)

I don't think it's Sybase-based. SQL Server was based off of early Sybase code (SQL 6.5 and Sybase 10(?) were *very* similar), but Access is based around the "Jet" engine, which is pretty dissimilar to Sybase. Just look at the query syntax for an Access query and a SQL server query even. Been very few moderately complex Access queries that I could ever straight drop into a SQL server and have work - there's always porting going on.

Perhaps the very latest version of Access is merged with SQL2000, but I don't think so.

Re:We do this in some cases (1)

mgkimsal2 (200677) | more than 13 years ago | (#385723)

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.

AGREED (1)

mgkimsal2 (200677) | more than 13 years ago | (#385724)

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.

Re:We do this in some cases (1)

mgkimsal2 (200677) | more than 13 years ago | (#385725)

Yep I have actually -

$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"; // 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";

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.

Re:Why? (1)

mgkimsal2 (200677) | more than 13 years ago | (#385726)

...are you saying this doesn't work?

Wasn't saying it wouldn't work - the original "Why?" responder was saying that Access and SQLServer were based around the same code - I was pointing out that they weren't.

My Experience (1)

Zecho (206792) | more than 13 years ago | (#385730)

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.

Re: An even better suggestion. (1)

AFCArchvile (221494) | more than 13 years ago | (#385747)

Covertly sabotage the Access implementation so it goes kaput on the first day, and then tell that loser, "I told you so!"

He'll go with Linux right away!

Re:MS themselves say DON'T! (1)

B14ckH013Sur4 (234255) | more than 13 years ago | (#385750)

This would be a much better point, and smell less of FUD, if you could provide a link...

It works great! (1)

NineNine (235196) | more than 13 years ago | (#385752)

It's easy. Just use MyODBC, and link an Access DB to MYSQL via ODBC. It's a great, easy-to-use front end.



ODBC for mysql (1)

sonofepson (239138) | more than 13 years ago | (#385754)

I use access on my win98 machine to access (no pun intended) my mysql linux box. Works just fine.

You can get the ODBC drivers here [mysql.com]

Thinking of a similiar project. (1)

oconnorcjo (242077) | more than 13 years ago | (#385756)

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?

Re:Why? (1)

Anonymous Slackard (254578) | more than 13 years ago | (#385765)

I think we're talking about Access as a front end for an sql database, are you saying this doesn't work?

Access looked very nice hitting RDB on VMS, I thought it was just regular ordinary ODBC, although the connection trivia was pretty obscure.

Also, from 97's help file:

  • DAO 3.5 introduces a new client/server connection mode, called "ODBCDirect." ODBCDirect establishes a connection directly to an ODBC data source, without loading the Microsoft Jet database engine into memory, and is useful in situations where specific features of ODBC are required.

Additionally, even using Jets engine, you can 'paint' sql queries using the query-builder type thing, granted, I'm not an sql pro, so feel free to correct me here, also I don't know squat about office 2000 stuff either.

This is a nice topic, hope some folks do post their experiences, I really like Access for surfing data, and the open source databases are looking great nowadays.

Re:If you're the DBA... (1)

Anonymous Slackard (254578) | more than 13 years ago | (#385766)

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.

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*

well you could always............ (1)

gik (256327) | more than 13 years ago | (#385769)

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

Re:If you're the DBA... (1)

Hiro Antagonist (310179) | more than 13 years ago | (#385773)

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?

--

Re:Pros and Cons of MySQL vs. SQL Server backend (1)

mech9t8 (310197) | more than 13 years ago | (#385774)

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.

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 .MDB files (Jet engine) natively and everything else through ODBC.

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.

Mac Compatibility? (1)

mech9t8 (310197) | more than 13 years ago | (#385775)

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.

It's not so bad (1)

mugurpe (317706) | more than 13 years ago | (#385781)

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

Make Sure MySQL suits your needs (1)

TargetBoy (322020) | more than 13 years ago | (#385792)

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.

ODBC Linked Tables (2)

deicide (195) | more than 13 years ago | (#385802)

  1. Download, install MySQL ODBC Drivers for Windows from http://www.mysql.com/downloads/api-myodbc.html [mysql.com]
  2. Create an ODBC source in Control Panel, entering all necessary information for your Unix/MySQL Server.
  3. Create all necessary tables in MySQL.
  4. In Access, New, Link Table, Files of Type: ODBC, choose your just-created ODBC source, choose your tables.
  5. You now have all your MySQL tables accessable just if they were Access tables. You can create forms, reports, whatever, using data stored in MySQL. Only thing you can't do is modify table schema from Access - and if you do that from within MySQL, you need to refresh your table links (Tools, Database Utilities, Linked Table Manager).

Using Access with MySQL - I've done it! (2)

planet_hoth (3049) | more than 13 years ago | (#385805)

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

Here's a section of the MySQL manual you should read beforehand:

http://mysql.com/documentation/mysql/bychapter/m an ual_ODBC.html

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

That being said, I'd take a PHP or Perl frontend over and Access frontend, hands down...

What about the front end? (Somewhat offtopic) (2)

tgeller (10260) | more than 13 years ago | (#385812)

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

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

--Tom

Re:That's a rather idiotic idea (2)

kinkie (15482) | more than 13 years ago | (#385817)

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 [sybase.com] .

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

Pros and Cons of MySQL vs. SQL Server backend (2)

Louis (21388) | more than 13 years ago | (#385819)

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

ODBC driver available for PostgreSQL (2)

divec (48748) | more than 13 years ago | (#385826)

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 [postgresql.org] . 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.

Re:If you're the DBA... (2)

alvi (95437) | more than 13 years ago | (#385836)

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

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

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

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

Re:That's a rather idiotic idea (2)

DeepDarkSky (111382) | more than 13 years ago | (#385842)

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

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

This is actually very easy. (2)

electricmonk (169355) | more than 13 years ago | (#385853)

There are ODBC drivers [freshmeat.net] for MySQL that can be found over at Freshmeat [freshmeat.net] .

Re:That's a rather idiotic idea (2)

Trevor Goodchild (187368) | more than 13 years ago | (#385857)


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.

I use MS Access as a front end... (2)

the Nach (260468) | more than 13 years ago | (#385861)

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.

Re:That's a rather idiotic idea (2)

CargoCult (313610) | more than 13 years ago | (#385862)

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

Been there done that, didn't think twice. (3)

pi_rules (123171) | more than 13 years ago | (#385872)

I'm part of a team who works with PostgreSQL & ODBC connected to ColdFusion on a dynamic website.

One of the guys, who has zero education when it comes to coding, databases, and computeres in general (don't ask me why or how he's a programmer.. I have no idea) managed to whack Access up against the PostgreSQL database via the ODBC portal I had done to it (can't remember how... it wasn't much of a thing).

It certainly isn't a revolutionary idea, people do it all the time... it's a two step process:

Setup ODBC to the database
Setup Access to hit an ODBC source (doesn't matter WHAT it is)

Yes, you don't want an "RTFM" post but really.. that's what you'll get :). I can at least tell you that it's been done, this is what the tools were DESIGNED to do, and that I've personally seen it all put together before. I can also tell you that it wasn't any major feat to get it working (it stumbled into our lap basically). The guy hated working with the command line psql client (read: he couldn't)... so he slapped Access up against it.

MS themselves say DON'T! (3)

SuiteSisterMary (123932) | more than 13 years ago | (#385873)

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.

Why? (3)

LaNMaN2000 (173615) | more than 13 years ago | (#385875)

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

We do this in some cases (3)

mgkimsal2 (200677) | more than 13 years ago | (#385876)

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.

Re:That's a rather idiotic idea (3)

mgkimsal2 (200677) | more than 13 years ago | (#385877)

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.

If you're the DBA... (3)

AlphaOne (209575) | more than 13 years ago | (#385878)

It comes down to this:

If you're the DBA and you've been given the responsibility of implementing the data storage at your company, you should be the one who decides what system is used.

Gently explain this to your boss. Tell him you understand that he may have a preference toward Access, but you know from past experience that Access will not meet your long-term needs and you recommend something different.

If your boss still insists, it's time to move on. You work for a micro-manager and that's the pits. Trust me.
--

Re:Why? (3)

Hiro Antagonist (310179) | more than 13 years ago | (#385879)

Urm...it doesn't support transactions [mysql.com] ? Check your facts first!

--

Don't weasel around the boss (4)

PD (9577) | more than 13 years ago | (#385881)

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.

Re:That's a rather idiotic idea (4)

divec (48748) | more than 13 years ago | (#385883)

There's places where all these wonderful OS applications don't work nearly as well as a proprietary solution and databases is definitely one of those places.
Hmmm. There are reports that PostgreSQL v7 matches Oracle at its own benchmarks [shout.net] , but that these benchmarks can't be published cos of a clause in the license for Oracle. So yeah, a free license isn't nearly as good for marketing as one which allows you to ban publication of benchmarks.

Re:MS themselves say DON'T! (4)

dirk (87083) | more than 13 years ago | (#385884)

If you do a search in the MS knowledge base, look for access, thread, multi, and a few other things, you'll dig up the MS KB articles that say "Don't use Access! It's unstable!"

It amuses me how the same people/companies that wouldn't use Word for creating a thousand page catalog, and wouldn't use Excel to write a corporate accounting system would, in fact, use Access to create an 'enterprise' database.


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

Re:Don't weasel around the boss (5)

1010011010 (53039) | more than 13 years ago | (#385887)

Plus, MS SQL Server is a better database than MySQL. You're just asking for problems trying to run the DB backend on MySQL on Linux (or NT, even), with an Access frontend.

MySQL doesn't support all of SQL, or all of ODBC, and Access will try to do things that it will not allow.

Stick with SQL Server

(says the Linux guy)

- - - - -

My Experience with Access and MySQL (5)

Zeus305 (104737) | more than 13 years ago | (#385888)

I have actually looked into the feasibility of doing something like this before, and I think you may run into some problems. The main problem is that what Microsoft calls SQL in Access is very misleading . . . while it is structured, and is a query language, it isn't SQL. The Access version of SQL really just gives the ability to have text based representation of what can be done using the GUI queries which are needed to compete with Paradox and other proprietary databases.

In addition, Microsoft has extended the MS-SQL language to use its own proprietary extensions. Microsoft has taken the attitude with Access SQL that they take on everything else - they program what they want and the standards be damned.

The major differences between Microsoft Access SQL and ANSI SQL-92 are listed in a table on page 190 of "Access Developer's Handbook" by Litwin, Getz & Gilbert from Sybex.

Here's a quote from the above book, "Access SQL is a hybrid SQL. It differs considerably from each of the SQL standards and doesn't completely support any of the ANSI SQL standards. It lacks large chunks of the standards, particularly in the areas of security and cursors. Sometimes it supports the same functionality found in one of the standards, but with a different syntax... In other cases, similar functionality is provided elsewhere in Access... Finally, Access SQL has some useful extensions that are not present in any of the standards..."

Overall, you may have some difficulty using Access with MySQL. If your boss is making you use MS on the frontend, it really will save you some trouble to use MSSQL on the back. If you do decide to go ahead with MySQL, my only advice is to make sure it is a well-researched decision. If you don't know exactly what you are doing and it doesn't work, it will just bolster your boss's attitude that all linux / open source solutions are problematic.

Devshed just did an article on this... (5)

edmz (118519) | more than 13 years ago | (#385889)

http://www.devshed.com/Server_Side/MySQL/ODBC/ [devshed.com]
"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

If you have to use Access... (5)

TeamFXML (125269) | more than 13 years ago | (#385890)

If you have to use Access, you can still connect to it via PHP or Perl from Linux using ODBC Socket Server, located at http://odbc.sourceforge.net [sourceforge.net]

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.

Why? Oh God, Why? (5)

bellings (137948) | more than 13 years ago | (#385891)

Let's see if I have this right. You don't want to use Microsoft SQL Server on a Win2K box, and you'd rather use MySQL on a Linux box. You make no mention at all why you want to do this.

I have no clue what kind of application you have -- what the nature of your data is, or the nature of the front end is. I have absolutely no idea why you think a web browser front end, PHP middleware, and MySQL back end is the superior solution. (Or, perhaps by PHP front end, you were refering to that GTK/PHP toolkit mentioned on Slashdot a few days ago, and you want all the users to have a Linux box on their desk. I don't know.) I have absolutely no idea why you believe Microsoft SQL Server and a MS Access front end to be an inferior solution.

What I do know are these things. MySQL and Microsoft SQL Server are two vastly different applications. As a DBA, you are obviously aware that on a technical level, Microsoft SQL Server is the vastly superiour database. In a shop that is almost entirely Microsoft, with only a few Mac's, the MS SQL Server is going to vastly easier to keep running, unless you plan on hiring consultants or tech's dedicated to keeping the only linux box alive.

Face it... they're on completely different planes -- the only place that MySQL beats MS SQL is on speed, on only a subset of the queries that MySQL is able to perform. You won't find any other comparisons of the two databases, becuase MySQL simply will never, ever be able to do any of the things that MS SQL does, and does well. A fair, unbiased consumer reports comparison of the two databases would be nothing but hundreds or thousands of checkboxes that give MS SQL two or three or four stars, and give a little "N/A -- not available" mark to MySQL. It would be a joke.

I'll freely admit that there are many places why MySQL is useful. The standard Linux or BSD box, running Apache/mod_perl/PHP, with a MySQL database is a tottally rocking deal. If you're a decent Unix hacker, it's easy to keep running, it's extrodinarily flexible, it's easy to learn to use, and it's fairly well documented. For 98% of the websites out there, it's plenty good enough. You can even set one up in your office on a spare pentium 75, just to try stuff out before going live with it. I love the combo, and it's what we use on a lot of the sites we develop at work. But for the people who need it or want to pay for it, we use a real database. MySQL is a great database for developers who are aware that it is not a great database, and can explain why.

But for running a dedicated database machine, hooked up exclusively to a group of MS Windows clients running a MS Access front end, in an office where the DBA isn't smart enough to make ODBC work, and the DBA's boss only uses Mac's, I can't think of a single reason you'd want to use MySQL. Not one. Having a wierd box in the corner running a wierd database on a wierd operating system that only one guy in the office understands, duct-taped up to a bunch of Windows clients, seem like the perfect recipe for a maintenance disaster a few years down the road. What a crappy idea.

Re:Windows ODBC Driver for MySQL Server (5)

SquadBoy (167263) | more than 13 years ago | (#385893)

You might also take a look at this page.
http://www.iserver.com/support/virtual/mysql/odbc/
Amazing what a Google search can turn up is it not. Just a hint Most of us will *never* think of anything so unusual that it is not already on the web. Do a search first and then ask questions.

That's a rather idiotic idea (5)

Trevor Goodchild (187368) | more than 13 years ago | (#385894)


You can hate Microsoft as much as you want, but MS-SQL is a whole hell of a lot better than MySQL. There's places where all these wonderful OS applications don't work nearly as well as a proprietary solution, and databases is definitely one of those places.

MySQL is a great DB for fast read access where things like row locking aren't very important. Great for the web when you don't want to spend a lot of money. But if you've already got SQL then using MySQL is just plain dumb. SQL on NT will out-perform MySQL in just about every way. Not using it just because you don't like Microsoft could get you fired, and IMO it would be justified.

Access can connect to/front-end for a MySQL DB (5)

LordWoody (187919) | more than 13 years ago | (#385895)

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

As an extension of the above, and table creation must take place in MySQL and then a link in Access created. Table mods must also be performed in MySQL afaik.

I haven't done this in ~2 years, but that's how it worked then, so take it (above comments) with a few grains of salt.

--
Never meddle in the affairs of dragons,

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?