Accessing .mdb over internet

Posted on 2006-03-29
Last Modified: 2007-11-27
Hello experts!

I am trying to build an application that allows the user to use a .mdb located locally on users computer and update an .mdb file on the internet.  The only reason for the local .mdb is because use of the form needs to be able to react off-line as well as on-line.  Specifically, the user will always update the local database and only update database over the internet when a)connected of course & b) when user specifies (ie. on_click). Synchronization of these databases is not the problem in a sense, I have been researching for the past few days and I am not able to narrow down a search on this subject, hence the question.  I am successfully updating the local database with OleDb Connections.  I understand that SQL server is my best bet, but my company and I are not able to use this technology yet and need for this application is growing. Now to the questions.
1). Am I able to do this? Can I connect to and successfully update,delete,insert data into an Access database over the internet?  I have been here (best by far) and a few other places to locate the connection strings but to no avail.  Also, I have visited most the links provided within EE forums for past subjects on this matter.  
2). With ADO & OleDb controls, do I connect to the .mdb via Internet with ConnectionString?  
           (ie. Provider=MS Remote;" & _
           "Remote Server=http://myServerName;" & _
           "Handler=MSDFMAP.Handler;" & _
           "Data Source=MyAdvworksConn")
Or rather something similar?  Is there something more involved I am missing or is this the only mandatory item to connect?  If I can connect and do one simple select or update, I will be off and running.
3). Is there another approach I am overlooking?
4). Is this a stable procedure? (Accessing .mdb's over Web)

This application is done in VB.NET 2005 (VS 2005 non-beta) and is quite stable as far as local database moves.  Thanks for your time and I appreciate ANY input into my situation.

Thanks in advance,

Question by:Kudzullc
    LVL 7

    Accepted Solution

    I had this problem in the past, I used webservices to handle my problem. Let's say, for example, the server that hosts the "remote" mdb is running, you could simply write an ASP.NET WebService to receive an insert statement and merely pass it on to the mdb, that way it occurs localy. I wouldn't suggest actually sending an INSERT statement but you get the idea. You could send parameters and the function in the WebService could build the statement behind the scenes. I am of the understanding that an Access MDB is NOT accessible across the internet, only an intranet.
    LVL 3

    Author Comment

    Thanks for the reply.  I am very unfamiliar with ASP.NET but will look into this.  If I build an ASP.NET application and let my VB.NET app talk to it, this ASP app has to reside on the remote server like a website, right?  Then my VB app gains internet access and populates the ASP app then allows the ASP app to connect to the remote server (which would be local for ASP app) for SQL stuff.  Does the ASP app have to be designed specifically for tables or does it just set a platform for the SQL data transfers?  Hmmm... let me rephrase that last one.  Can the ASP app be a universal container that can be dynamically built with the VB app and then be set with  SQL statements from the VB app...?

    LVL 22

    Assisted Solution


    I have an application that I've built that has nearly this exact same scenario.  I agree with wtconway, Web Services is the way to go.  The web service runs on the IIS web server of your choice, but must have direct access to the SQL server (same network).  From what you have above, you'd probably just have two methods, Insert and Update.  You could pass in a DataSet or any other serializable object to each method.  Your Web Service could then connect to your SQL server and make the appropriate updates or additions.

    The Web Service can be whatever you make of it, but typically you would control all of the SQL statements and whatnot within it.  If I'm understanding you properly.  So all that would be passed back and forth would be the actual data that is going to be added or updated (and the results of those transactions).

    I'm not sure if that clears anything up, but just think of this as another supporter for Web Services to solve the problem.

    -- Jason
    LVL 3

    Author Comment

    Scolja and tm,

    Thanks for your comments.  I have gotten down to the nitty gritty on this issue.  The past VB6 program(which I did not build) is using Coldfusion as a webservice by calling xmlhttp.Open "POST" to append data to the server.  It is quite effective and since I know CF I am going this route myself.  Thanks for all your input on this.  I was really hoping that one of you would have said "Yeah, the connection String is this #blah blah blah# and dont forget to check and make sure your connected, thats all you need" but It is a bit more complicated and I have already begun testing some test models.  It is very interesting.  Just a quick explanation....  I am passing all the fields per table into a form post through xmlhttp and submitting the cfm which tags the DSN I need to update.  Works great too!  But I will be looking to upgrade this software later this year and obtain a dedicated SQL Server for just this application.

    Thanks again!


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now