Re: ODBC connection to mdb for MacIntosh

Hi Amy,

My apologies for writing when I was more than half asleep.


Amy Blankenship wrote:

Microsoft Access does not exist for the Mac. In order to use ODBC Microsoft Access has to be able to manipulate the data tables. Therefore the data tables (the .mdb file) has to reside on a Windows computer that has MS Access on it.


This is not true. As long as the computer has ODBC drivers for an Access database, it does NOT have to have Access installed.

Yes, indeed you are right. Thinking back to when I used ODBC a lot in Office 98 and 2001 it was possible to control several different types of databases directly on a single Mac without having the application on the computer. I was using FoxPro database files at the time.

Consequently it is impossible to have the mdb file hosted on a Mac and expect to use ODBC or anything else to get it to work.


Why? I'm not sure that I follow. Maybe the problem is not that it's impossible, but that the information is not readily available. Please don't take this the wrong way, but given the fact that you seem to be under the impression that you actually have to have Access installed on a machine for you to talk to an Access database, you may not be the most definitive source on this issue. If you had to have Access installed for ODBC to work, then you could not host Access databases on UNIX servers and access them via php, cgi, etc.

Again, you are right. A properly written ODBC driver would not require that Access be able to run. For some reason I was confusing ODBC process with a different protocol (DDE) which does require a host application to be able to run.

Is there maybe another forum, perhaps on the Apple site, where I might encounter people with more specific expertise relevant to my question?


It has to be hosted on a Windows computer. If you want a Mac only solution you might wish to consider a different brand of database that has a Macintosh version (FileMaker pro may fill this requirement, but there may be some SQL limitations - not sure).


Filemaker pro is crap. Trust me on this one.

I haven't had to use it very much. It seemed OK to me, but I've not given it a thorough work out. FoxPro is one I've used and like a lot.

The SQL commands are sent to Access via ODBC. The commands are generated on a Mac running MS Query in Microsoft Office (or other ODBC compliant software). You can use the MS Query interface to create any SQL command and execute the command.

No, you can not put the mdb file on a mac and connect to MS Access via ODBC because MS Access only runs in windows, not on a Mac. MS Access has to be there and able to run.


See above. People host mdb databases on UNIX boxes all the time. So it seems it should just be a matter of finding the right driver.

Yes, you are right. Duh! Please be gentle when you do my punishment for saying such dumb stuff.

Setting network and file permissions is the same for a Mac client as it is for Windows clients. Since this topic is about how to use and configure Microsoft Windows, it it is beyond the scope of Excel for the Macintosh so I'm not going to try to answer it except to say that ODBC needs to be able establish a connection to Access and have permssion to read and write to the mdb file in order for it to work.


File permissions have nothing to do with it. Is this an Excel forum? I thought it was an "other products" forum.

Nope, this is not an Excel forum. You're right again. I'm wrong. Sheesh! You'd think I would learn to not type when sleepy.

Any VBA SQL command that required the Microsoft ODBC add-in will work in Office 98 and 2001 but the add-in has not been brought forward to work in Excel v.X or 2004. But I am pretty sure a direct SQL command to update a row in a table works in all Mac versions.


But unfortunately no one has any drivers to make the connection!

Your expectation is not unreasonable. What you want is the same *type* of driver that was available for Office 98. Unfortunately there was no Access driver for Office 98 or 2001 or any version of Mac office. You want a driver so that you can have an mdb file on your mac or on a shared volume (anything that your mac can mount) and control it via ODBC.

If you search Google on this topic about 2 years ago there was a complete thread in the Excel for Mac newsgroup about ODBC drivers. But as you've discovered we are still waiting for someone to bring out a single-tier (or whatever terminology you want to call it) ODBC driver that will let you control an mdb file that is not on a network drive.

Oh, well, I'll keep looking. If you can suggest any further forums, I'd appreciate it.

One trouble with ODBC drivers is money. Microsoft gives ODBC drivers free as part of Windows. Apple does not include ODBC drivers with MacOS (a mistake in my opinion). Microsoft used to license ODBC drivers for Mac Office and distributed them for free with Office 98, but decided the cost was not being covered by additional sales of Mac Office, so they dropped them. 3rd party developers have not jumped in the fill the void.

It's been a while since I thoroughly investigated the availability of the type of ODBC driver you are seeking. If you do come across one please be sure to post the information here. It would be great if there an open source driver lurking in the shadows someplace that I don't know about.

Also, I want to elaborate a little bit about the ODBC add-in. It would be great if Microsoft were to updated that gem to work with Office 2004. It allowed for the use of certain SQL commands to be used by VBA. I think the SQLEXEC commands and some others were supported by that add-in.

The key to the whole problem remains the lack of a single-tier ODBC driver for MS Access.

I got all excited when I heard the next version of Office will change to XML as the file format, but then I found out that MS Access will not make the change and will continue to use the MDB format for the next several years at least.

I'm not sure, but it might turn out that you can use XML to treat Excel worksheets as a relational database. Since Excel already supports XML I wonder if anyone has tried this. This is an area that might be worth checking into, since if you can convert MDB databases into XML then the whole problem of ODBC drivers might go away.

The whole XML/SQL issue is intriguing to me. Here's a paper to get you started on this:
http://www.research.ibm.com/journal/sj/414/reinwald.pdf

In the article, there's a section concerning SQL extensions for XML. Another place to investigate XML/SQL is this site:
http://www.sqlx.org/

I found this article just now that I want to read:
http://www.datadirect.com/developer/xquery/topics/sqlxmlwp-new/index.ssp

IBM has lots to say about SQL and XML
http://www.google.com/search?as_q=xml+sql+java&num=10&hl=en&btnG=Google+Search&as_epq=&as_oq=&as_eq=&lr=&as_ft=i&as_filetype=&as_qdr=all&as_occt=any&as_dt=i&as_sitesearch=ibm.com&safe=images

I guess what I am getting at is that ODBC is great, but using XML may be a good work-around. It's entirely possible that XML solutions that we have not yet discovered might make the need for ODBC solutions not necessary. There is a huge amount of information to wade through.

Oops! Now I'm rambling again. Time for sleep.


Thanks;

Amy 



--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>
.