Accessing .mdb over internet

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 http://www.carlprothman.net/Default.aspx?tabid=93#MSRemoteAccess (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,
Lucas

LVL 3
KudzullcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wtconwayCommented:
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 asp.net, 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KudzullcAuthor Commented:
wt,
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...?

Thanks,
Lucas
0
Jason ScolaroCommented:
Kudzulluc,

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
0
KudzullcAuthor Commented:
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!

Lucas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.