Avatar of marc_rabil
marc_rabil
 asked on

How to Remotely Connect to Microsoft Access via JDBC on a Mac

I am developing a Java web app on a Mac and need to connect to a remote Windows machine that has an MS Access DB file on it.  This file is configured as an ODBC data source on that windows machine. Can someone suggest the best way to do this?

Marc
JavaMicrosoft Access

Avatar of undefined
Last Comment
marc_rabil

8/22/2022 - Mon
Sharon Seth

What is the Mac  Java Web App supposed to do with the windows file ?
Also  , on what framework and web server is the Web app running ? Are you using WebObjects ?
ASKER CERTIFIED SOLUTION
CEHJ

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

Access databases don't support remote connections. Unless you can browse to the Windows machine using standard "browser" techniques (i.e. like Windows Explorer on a Windows machine) then you can't do this. You'd have to move the Access file to the local machine, or move your program to the machine hosting the Access file (or move the data in the Access db to a server-type database, which could be configured to connect remotely).
SOLUTION
CEHJ

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marc_rabil

ASKER
I am familiar with OpenLink and EasySoft solutions, but I am looking for a non-commercial approach, if possible.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Scott McDaniel (EE MVE )

I'm not sure how those would work, but I do know that you cannot connect to a remote Access database using standard ODBC-type connections. For example, if I have a website that is backed by an Access database, I can't use my local Access installation to open that remote database. I'd have to make a copy of the web db and move it to a location where I could get to it.

The reverse is true also - my website cannot get to the local Access database I have on my workstation (unless, of course, my workstation is on the same network segment as the webserver).

Note that very early on, MSFT provided the Remote Data Objects (RDO) library, which allowed web-hosted Access databases to be accessed by other web sites/programs. I don't know if RDO is even available anymore, but MSFT quickly began to recommend that you NOT try to connect to an Access db in that fashion (corruption was rampant, and it just wasn't worth the effort).
CEHJ

Certainly i think Access should be avoided wherever possible, local or remote. The only thing it's got going for it probably is its ui.
I would imagine OpenLink have put together some kind of shim to provide a network layer to Access. I would guess others have done the same - Access is rather 'sticky' unfortunately
Scott McDaniel (EE MVE )

Still, there'd have to be something running on the machine hosting the Access db in order to provide that product (OpenLink, Easy Soft, etc) with access to the data. If that were the case then I'm sure you could get to the data through those products. If they're running on the users workstation, however, I don't see how they'd be able to manage that. Outside of RDO, as I mentioned earlier, I've never known a product that could remotely connect to an Access db unless the two machines were networked in some way (LAN, VPN, etc).

If these products can connect remotely I'd be very, very interested (and so would a host of other Access developers).
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CEHJ

Still, there'd have to be something running on the machine hosting the Access db in order to provide that product (OpenLink, Easy Soft, etc) with access to the data.

Definitely. I expect that's exactly what they do
marc_rabil

ASKER
OpenLink does work in this case
CEHJ

:)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scott McDaniel (EE MVE )

Cool ... did you have to install anything on the remote system, or was everything installed on the machine hosting your Java application? Just curious really ...
marc_rabil

ASKER
The OpenLink solution has 2 parts - one on the machine with the Java app to provide a JDBC driver and the other on the server with the MS Access file to provide a remote API to it.