Solved

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

Posted on 2010-11-21
5
816 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:Srinivas Mantha
[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:Srinivas Mantha
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:Srinivas Mantha
ID: 34192956
Thanks for the prompt support
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
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: …

623 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