Connecting Access Databases remotely?

[sanchit dua]

Currently:
Currently I have a Java application which performs all I wanted and the limitation
in it is "It only resides on the Local machine for which it needs to work". I have the Access Databases located on a Remote Server and I install the Java app on the remote server to connect the databases using a connectionString

 "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:\\DATA\\DPPI\\DATA\\DPPI\\DPPIPR01.mdb";

It connects to Access Databases and do all the bussiness logics with them.

Enhancements (to be done):
1. Making the application online - not a big issue.
2. Connecting the databases remotely - its the only concern.
So, if I make the Application Online the Access Databases need to be connecting through network (somewhat like ip or some web address).

Problem:
But after going through this http://stackoverflow.com/questions/11388277/connect-to-access-database-remotely It seems my requirement can not be completed.
Is there any way if I'm able to upload the database to the online web hosting server and access the database from there only? If yes, then how should I do the connectivity since Access Database connects by giving the fully qualified name containing drive letter and all.
But how should I proceed further so that I may be able to connect the mdb Databases remotely?

ADO Solution:
But after going through the link: http://webcheatsheet.com/ASP/access_connection_strings.php, it seems we can perform remote Access Database connectivity.


The solution for the ADO is something like:

connectionString="Provider=MS Remote; Remote Server=http://your_remote_server_ip;" &_ 
	"Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\your_database_name.mdb" 

I'm a tad confused to how Access Database we can connect.

EDIT
There is one more question in my head.
I'm making use of Type-1 Driver. Should the Type-4 Driver support the remote connectivity?

[Wei Dai]

>I'm making use of Type-1 Driver.
You cann't use sun jdbc-odbc bridge driver to connect MS Access database remotely.

>Should the Type-4 Driver support the remote connectivity?
HXTT Access(http://www.hxtt.com/access.html), a commercial type 4 driver for MS Access, supports it.

[sanchit dua]

Do we have some free or open source Type-4 Driver for this case? As it seems to be not fitting for my pocket.

[sanchit dua]

What if I'm able to call another java application residing on the Server housing the Microsoft Access Databases? Can you please let me know if it can be the solution such that I will have the division of my application into two:
1. The Online java Application performing 2 things: Accepting the requests coming in and waiting for the response to come from another application running on the Server housing the Microsoft Access Databases.
2. The Java application installed on the Server housing the Microsoft Access Databases only responsible for connecting and returning the DTO (Data Transfer Objects) -or some kind of objects in Collections.

So, I would expect a serverside program to handle the communication with the DB and the client application would communicate with the server app.

Please let me know if I should proceed in this manner.

[Martin Vashko]

What you propose is doable, but far too much work for questionable utility. My advice would be to find and use a database better suited to your needs. There is a lot of free databases to use, eg. MySQL, PostreSQL, and even commercial databases usually have a free edition (Oracle and SQL Server does, for example). All of these databases I've mentioned will scale much better and operate more reliable than Access. But these are definitely not the only options, you might want to investigate other databases as well.

Why do you want to use Access at all? Given that you're accessing a remote database, I don't see any reason to stick with Access. Since the DB is remote, the users won't be able to grab the MDB file and backup it or bring it to an offline computer for work (or, heaven forbid, open it in MS Access and destroy modify it).

[sanchit dua]

The main purpose of using Microsoft Access Databases is:
1. Another .Net application installed on several systems are already making use of MS Access database and we have to refer to those databases every time a change is reflected from them from the Java Application.
2. We can't do the Database migration here since we can have the data integrity issues like several relationships of tables and all.

I would also like to have some another from the solution I proposed earlier. As I have realized it would not provide the cost benefits by developing the given scenario.
For this I think I need to perform the following:
1. I have to first install some dedicated Application/Web Server over there on the Remote Server.
2. Then, based on the online accessibility (getting the remote accessibility) of this server I will perform the callouts from outside.

Its gonna cost me the overhead of maintaining the Web Server as well as so many web services callouts (Not much thinking but as I was asking about writing of web service, it is already consuming 3 Soap based web services).

So, not going into much details can you please let me know what should be more possibilities of dealing with this situation considering the Microsoft Access Databases solely?

[Martin Vashko]

sanchit dua wrote:
The main purpose of using Microsoft Access Databases is:
1. Another .Net application installed on several systems are already making use of MS Access database and we have to refer to those databases every time a change is reflected from them from the Java Application.

My experience with MS Access used simultaneously by several users (especially with a file on a network share, but that won't be your scenario) isn't very good. Some sources say that MS Access has improved over the years. Even so, the architecture you describe sounds enterprise-y to me, I'd suggest ditching Access.

2. We can't do the Database migration here since we can have the data integrity issues like several relationships of tables and all.

Of course you can! Databases up to terabytes in size are migrated (and even larger, I'd say, though I haven't witnessed a larger database migration myself). You need to plan for it carefully and use the right tools (and a dry run or two is good as well), but it is not an impossible, not even unusual task. If you choose MS SQL Server (perhaps the free Express Edition) as your target, you'll have all the tools right there from Microsoft. There's a lot of information on the web on migrating Access to SQL Server. But I'd say that SQL Server certainly isn't a necessity, migrating to something else might be nearly as easy.

So, not going into much details can you please let me know what should be more possibilities of dealing with this situation considering the Microsoft Access Databases solely?

Have you considered porting your Java application to .NET? Since there are already .NET applications, you'd make your environment less diverse, and you wouldn't have problems connecting to MS Access.