Solved

How do I connect to Access on an external server via ODBC DSN in ASP?

Posted on 2009-07-08
9
436 Views
Last Modified: 2012-05-07
I am working on an ancient system that still uses ASP and Access as their respective platforms for web and data.  We operate across three different servers. Thing is, my web page is located on Server A and the database is located on Server C. I have created a DSN on Server C for the database, but am having trouble configuring the connection string to look for the DSN on that server. I keep getting the following error because it's looking for the DSN on Server A where it is not:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/ccl/stuorgs.asp, line 228

I'm sure that I'm just not configuring something properly. I'm used to having the database on the same server on which I am working. Help!
<%
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DATABASE=ccl; SERVER=xxx.xxx.xxx.xxx; DSN=ccl;UID=xxxx;Password=xxxx;"
set rs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT ID, Name FROM table ORDER BY ID;"
rs.Open SQL, Conn
%>

Open in new window

0
Comment
Question by:vcbertini
[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
  • 4
  • 4
9 Comments
 
LVL 85
ID: 24812417
Why is your DSN on a different server than your ASP page? I'm not sure, but I don't think you can reference a DSN on a remote machine - but, again, I've never tried it.
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 250 total points
ID: 24813145
LSM is correct. The DSN must be established on the local machine. If you have Access installed on the local machine, you can use this code instead of a DSN.
-chuck wood
<%
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\share\path\db.mdb;User Id=admin;Password=;"
set rs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT ID, Name FROM table ORDER BY ID;"
rs.Open SQL, Conn
%>

Open in new window

0
 

Author Comment

by:vcbertini
ID: 24813465
This is an architecture I inherited when I started working here. They have one hosted server (at Verio) that runs IIS - that is where the bulk of their website resides.  They also have two on-site servers that I think the original intent was to host the faculty web pages and also the intranet, but over the years they came to host some of the external web applications as well.  One server is secure (with a certificate) and one is not and both run IIS.  The database I need access to resides on the secure internal server and the web page I am modifying is on the remote, hosted web server. See my problem? I cannot move things around right now because we are on the verge of a major overhaul/consolidation so it's not really worth the effort at this point.
0
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24813503
You can either set up a DSN on server A or use the code snippet to point to your Access database on server C.
-chuck wood
0
 

Author Comment

by:vcbertini
ID: 24813549
The DSN on sever A could only point to a database on server A, correct? or is it possible to point to another server? I'm not all that familiar with how those things work.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24813659
A DSN can point to a database on the local server but is often used to point to a database on another server.

DSN: Short for Data Source Name. Data Source Name provides connectivity to a database through an ODBC driver. The DSN contains database name, directory, database driver, UserID, password, and other information. Once you create a DSN for a particular database, you can use the DSN in an application to call information from the database.

There is also what is known as a "DSN-less connection." Instead of using a DSN to connect to a database, the developer specifies the necessary information right in the application. With a DSN-less connection the developer is free to use connection standards other than ODBC, such as OLE DB.

You can create a DSN on server A to point to the database on server C or you can use a DSN-less connection like the snippet I posted to point to the database on server C.
-chuck wood
0
 

Author Comment

by:vcbertini
ID: 24815388
This is the error I got when I tried to use the code above:

Microsoft JET Database Engine error '80004005'
Could not find installable ISAM.
/ccl/stuorgs.asp, line 229

I have not set any sharing permissions on the data folder - aren't there significant security ramifications to sharing a data folder?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24816121
You need for the web host administrators to fix their data components. See this article:
http://databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html 

Yes, there are security ramifications but you just need to be careful. First, share the data folder using a name with a dollar sign (share$) so that it is not visible. Second, put onely Read permissions for the account that you ASP runs under.
- chuck wood
0
 

Author Closing Comment

by:vcbertini
ID: 31601776
I haven't gotten it to work yet, but I understand the concept. Reconsidering this architecture where the remote DSN usage is even necessary. Thank you for your input.
0

Featured Post

Independent Software Vendors: 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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

707 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