Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

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

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
Srinivas Mantha
Asked:
Srinivas Mantha
  • 2
  • 2
1 Solution
 
Shane32EECommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
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
 
Shane32EECommented:
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
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Thanks for the prompt support
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now