Solved

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

Posted on 2010-11-21
5
801 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

696 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