Solved

Connecting to Microsoft Access Database on the Web server from a local host

Posted on 2010-11-21
5
795 Views
Last Modified: 2012-05-10
I have a Microsoft access database on the web server that will get updated with new data from a front end application software based on traditional ASP. Frequently, I need to transfer the database to my local computer through FTP software to enable transferring some data to oracle database. I have written an application based on traditional VB6.  Everything is working well.  
      Now, I want to get connected to the Microsoft Access Database on Web server from a local system through a DSN and any other method. The idea is to transfer the data directly from web server to the oracle database in my local system.  I know the IP address of the web server and also the path of the database file on the web server.  Is it possible to create a DSN with the web server parameters that are known to me i.e. IP address and the path of the file.
0
Comment
Question by:smanthanims
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Shane32EE
ID: 34184070
You would need a network connection or VPN to accomplish this, since Access's engine is client-side, not server-side.  So you can't connect via a DSN to the server, because there is no Access database engine running on your web server.  However, I would recommend writing an ASPX page that retrieves database results and returns them in XML format.  Then a local VB.Net application could retrieve the ASPX page convert the results to an in-memory dataset, at which point you could import the data back to Oracle.  Since ASP.Net and VB.Net natively support converting datasets to XML and back, it would be rather easy to create.  VB.Net can also natively retrieve a web page quite easily.  Password protection could be as simple as hashing the current day & hour against a fixed key, and passing the password through the querystring.

If you want to use VB6 and classic ASP, I would still write it the same way.  It just would be a lot harder since you'd have to write the dataset<-->XML and VB6<-->web code yourself.

Shane
0
 
LVL 84
ID: 34186684
Shane is correct. The only thing I'll add is this:

In general, unless you can navigate to the Access database using Windows Explorer, you cannot "connect" to the Jet database. So unless your web server is on the same network segment as you, or you can somehow establish a VPN connection (which would be highly unlikely) you are out of luck in regard to a direct connection.

0
 

Author Comment

by:smanthanims
ID: 34189541
Ok.  My VB application software for data migration from MS access to oracle also involves transfer of some data from oracle to MS access.  Few fields simultaneously would get uptated in both the databases.  Therefore, a simple XML output of MS access table data would not suffice.  If I migrate totally from MS Access to MySQL database on the web server (which I plan to do any, irrespective of the current problem), would it be possible to get connected to MySQL database on the web server with a DSN connection (of course with internet on).  If not a DSN, PHP coding would also be good enough.  Any thoughts?
0
 
LVL 3

Accepted Solution

by:
Shane32EE earned 500 total points
ID: 34189658
Sure -- you just have to configure MySQL and/or your firewall to allow TCP/IP traffic from the internet, and make sure your passwords are set in MySQL.  I have never done that myself, so I'm not sure what the security implications would be (if any), or what the performance would be like.

To create connection strings, visit this webpage:

http://www.connectionstrings.com/mysql

To create a DSN, open Control Panel and open "ODBC Data Sources".  From there, you can use a wizard to create a MySQL DSN, providing you have a ODBC MySQL driver installed such as this one:

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Shane
0
 

Author Closing Comment

by:smanthanims
ID: 34192956
Thanks for the prompt support
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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