Solved

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

Posted on 2010-11-21
5
807 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 85
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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