9 Replies Latest reply on Jun 3, 2013 8:50 PM by jschellSomeoneStoleMyAlias

    Connecting Access Databases remotely?

    SanchitJavaDev

      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 Microsoft 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 .Net Solution:

      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. By making use of Type-4 Driver like HXTT Access(http://www.hxtt.com/access.html), a commercial type 4 driver for MS Access, supports it. But is there any open source Type-4 Driver like this?

      Edited by: Sanchit Java Dev on May 29, 2013 9:54 PM

        • 1. Re: Connecting Access Databases remotely?
          Kayaman
          Not really related to your problem, but having an application connect directly to a remote database is quite a security risk. I would expect a serverside program to handle the communication with the DB and the client application would communicate with the server app.

          Also, why use Access and not a real database?
          • 2. Re: Connecting Access Databases remotely?
            gimbal2
            Kayaman wrote:
            Also, why use Access and not a real database?
            That. Or at the very least use an embedded Java database such as H2.
            • 3. Re: Connecting Access Databases remotely?
              SanchitJavaDev
              The Microsoft Access databases are not countable for the client we have. We are making use to only fetch the records and transfer them to an online application (Salesforce). So, its kind of something exporting of data based on the logic written in Java Application.

              So, how can it be possible to switch to another database like H2 or any other databases?

              If my application becomes server side application (like a web application) and my server's OS is Windows then theoretically it is possible to use my Access database. In this scenario, the Access database will be local to my application. In my connection string I can simply use the path to the Access database inside my server's file system.

              But that Access is a desktop database not made to use it as a server database. This can lead to poor performance and maybe system crashes. And it is possible that there are some licencing issues for this use case I guess.
              Even if we are able to upload the file over the online server, how can we make sure it can be referred from the file system of the server?
              • 4. Re: Connecting Access Databases remotely?
                SanchitJavaDev
                I really liked your idea *@Kayaman* and I'm envisioning to have the application to work in the following manner:

                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.

                Can you please let me know how should we deal with this situation? Means, via Webservice or Rmi or some kind of Rpc calls.

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

                Edited by: Sanchit Java Dev on May 30, 2013 9:11 PM
                • 5. Re: Connecting Access Databases remotely?
                  Kayaman
                  Sanchit Java Dev wrote:
                  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.

                  Can you please let me know how should we deal with this situation? Means, via Webservice or Rmi or some kind of Rpc calls.
                  I would recommend Webservices, as RMI and RPC are a bit old fashioned. You can either use JAX-WS for SOAP calls, or the easier JAX-RS for RESTful webservices.
                  Please let me know if I should proceed in this manner.
                  I don't know your whole situation, but this seems like a workable solution.
                  • 6. Re: Connecting Access Databases remotely?
                    gimbal2
                    Kayaman wrote:
                    Please let me know if I should proceed in this manner.
                    I don't know your whole situation, but this seems like a workable solution.
                    Normally I'd agree without hesitation but in this case I'm not too sure, its Access we're talking about. How good is it at concurrent access I wonder? Tying webservices to a decent database works just fine, but what happens when you make it so multiple requests can bang with a hammer on the same single Access file (I refuse to call it a database)?
                    • 7. Re: Connecting Access Databases remotely?
                      SanchitJavaDev
                      Thanks for your responses guys. It seems I'm heading in a right direction (but can't completely say as I would also like to be concerned about the performance issues.)

                      For the time being I would like to consider this approach until it is fully confirmed.

                      Please let me know if I'm proceeding in a right way? (developed a webservice using Jax-ws testing application)
                           1. I wrote a Jax-ws webservice published and tested on the localhost - its returning the response (tested using Soap-Ui).
                                So, I would like to have the same application to be acting as a Server to be deployed on the Server housing the Microsoft Access Databases. It would connect the database and return the result to be consumed from Client.

                           2. From the other end I opened the Soap-Ui such that I edited the Endpoint like: (this call would be actually made from the Online Application)
                                Previous End point of the Wsdl- http://localhost:8080/myService/services/Invoker - tried and tested
                                Edited End point of the Wsdl for testing - http://remoteIp:8080/myService/services/Invoker

                                I got the Error stating:
                                                              ERROR:org.apache.http.conn.HttpHostConnectException: Connection to http://192.168.1.6:8080 refused.

                           Please let me know what I'm doing wrong or is there a special requirements for calling the services remotely?

                      EDIT

                      I took the IP address by executing ipconfig.

                      Edited by: Sanchit Java Dev on May 31, 2013 3:28 AM
                      • 8. Re: Connecting Access Databases remotely?
                        SanchitJavaDev
                        I would also like to have some another solution from the approach You 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?

                        Edited by: Sanchit Java Dev on Jun 1, 2013 2:52 AM
                        • 9. Re: Connecting Access Databases remotely?
                          jschellSomeoneStoleMyAlias
                          Sanchit Java Dev wrote:
                          After going through the link:
                          http://webcheatsheet.com/ASP/access_connection_strings.php , it seems we can perform remote Access Database connectivity.
                          I doubt it is as simple as suggested there.

                          There is no 'server' with MS Access. To do remote access you must connect with something and the connection string presented there suggests it is using a http server. The most likely guess there would be IIS - if so then that would need to exist on the target box.

                          Presuming of course that the data there has anything to do with reality.