MS Access ODBC to Remote Database

[maxwanadoo]

Can anybody advise on the way to set up an ODBC connector for a desktop MS Access MDB Front End to connect to an MS Access MDB Backend on a remote (internet) location?

Thanks

Max

[GolferGuy]

Max, the only way I know of to handle this situation would be to use the cloud. I am assuming that you want more than just one Access frontend to be using this remote Access backend. From everything I have heard/read, the cloud is the only way to do it. Unless, you want to rewrite the Access frontend as a webpage, then you could use the Access backend with "little" trouble.

[maxwanadoo]

Max, the only way I know of to handle this situation would be to use the cloud. I am assuming that you want more than just one Access frontend to be using this remote Access backend. From everything I have heard/read, the cloud is the only way to do it. Unless, you want to rewrite the Access frontend as a webpage, then you could use the Access backend with "little" trouble.

Thank you for taking the time to reply. I will look into the Cloud and see if that is possible for me.

Max

[rgudgeon]

Can anybody advise on the way to set up an ODBC connector for a desktop MS Access MDB Front End to connect to an MS Access MDB Backend on a remote (internet) location?

Thanks

Max

Hi Max,

I assume that you are trying to run a desktop application conntecting to this backend data.

Over quite some years I have been supplying MS access desktop applications that connect to backends located on network servers. For small numbers of users this works fine.

When it comes to connecting to 'real' remote backends the performance has never been usable in a customer situation. There is so much data running around that user response is abysmal. Never have the tests done met the needs of customers.

The way I have solved this in situations where the remote connection was really needed has been to set up Citrix or Terminal Server solutions. with this the custeomer is really running the front end on the remote server (thus getting respectable performance on the 'front end', 'backend' conection). All that goes over the slow Internet/other remote link are the screen changes. while this is not perfect is is quite usable.

I have come to the conclusion that direct connections between MS Access front ends to back ends over wide area networks is not worth pursuing on performance grounds without even considerating the technical difficulty.

If Citrix etc is not feasible and you really have to do this you should consider a web app.

Hope this saves you a lot of wasted time

Regards
Ray

[GolferGuy]

Ray, excellent response. It has been too long for me that I had forgotten how slow the connection is on a wide area network.
Vic

[maxwanadoo]

Thanks Guys, food for thought there.
www.thinstuff.com seem to provide a good solution. Any thoughts on that?

Max

[chuckw]

Hi Max,

What version of Access? Access 2007 makes it easy to connect to Web or ftp.

Chuck

[maxwanadoo]

Hi Chuck,

Currently written in 2003 and looking to port to 2010 (Office versions). Lots of connectivity with Word and Excel build into my App.

Max

[chuckw]

Max,

How soon do plan to port it over? 2010 also has an external data link in it.

Chuck

[maxwanadoo]

Hi Chuck,
No set date. Sometime in the next year perhaps.
It might not even happen. I say that because it currently runs over a LAN and that works just fine. There is a danger that if the internet connection goes down that work will stop, whereas on-site solutions that won't matter.
So it is all up for debate at the moment and all i am doing is preparing for a jump either way.
I know there are many backends available (ie, MySQL, Libre and so on) but I want to stick with MDB because of the flexibility the DAO connection gives me with regard to table properties etc, which is just not available elsewhere (not even in ADO).
Thanks
Max

[chuckw]

Hi Max,

I've linked to access from 2003 to an access db once a long time ago. I just don't have 2003 setup anywhere to try to walk you thru it. I have mapped a network drive connection to a site before and made that work.

Chuck

[maxwanadoo]

I think it would be pretty easy to connect to a remote computer running the app whereby the front-end AND the back-end were on the remote computer for the simple reason that the processing is done remotely and that is what happens with a thin client (aka Citrix and similar) but to have the processing done on a PC client and the data sitting on a remote computer on the internet is different.
Max

[chuckw]

Performance will be an issue. As you said earlier, if the Internet goes down so does your app.

[chuckw]

The cloud is just about the same as remote desktop as everything sits on the server.

[maxwanadoo]

yes, you are correct.
Thanks for the input Chuck - appreciated.
Max

[chuckw]

Good luck Max, See ya.

Chuck

[maxwanadoo]

Thanks Chuck
Max

[RexBrown]

Can anybody advise on the way to set up an ODBC connector for a desktop MS Access MDB Front End to connect to an MS Access MDB Backend on a remote (internet) location?

Thanks

Max

Max,
Not sure exactly what you are trying to do, however, here is one possibility:
Select File/Get External Data/Import
when the Import window comes up select Files of type: ODBC Databases which will give you a Select Data Source window. Select New and then from the available list select the type of data base connection you desire. Select Advanced to setup driver specific keywords and values, then select next and browse to the database you want.

Rex

[maxwanadoo]

Rex, thanks for the suggestion but you cannot do that over the internet. That system you describe AFAIK is only for LAN and not the Internet.
Max

[RexBrown]

Max,
OK. I am not sure what you mean by internet but I assume you mean a global connection (i.e. WAN). In general, if you can browse to the site (may require login/password) you should be able to connect to a database at the site. If you need to connect to a different database (i.e. Oracle or Microsoft Server, etc) you will have to select the ODBC driver for it. As an example, I have a connection to a server an an offsite company which I can access as "\\server\directory\filename". It requires me to login but once I do so I can open a file at that location as though it were on my computer.
If this does not make sense could you give me an example of what you are trying to do?
Rex