Re: ODBC connection to mdb for MacIntosh

I have been following this thread and I have a question myself. My situation
is a bit different. I have virtual pc installed with access2003 so
manipulating the data or structure isn't an issue. However I am using
macromedia studioMX 2004 and I need to be able to connect to the mdb by
setting up the dsn and I can't seem to fing the appropriate odbc driver to
accomplish this so studio can see/retrieve the table structure. I can hand
code it all but that is a pain. Any ideas?

mark


On 8/4/05 4:36 PM, in article #nGLxZWmFHA.1044@xxxxxxxxxxxxxxxxxxxx, "Jim
Gordon MVP" <goldkey74@xxxxxxxxxxxxxxxxxxxxxx> wrote:

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

.