Error connecting to access tables "linked" to sql server tables

[nazzaro]

When calling asp pages that connect to data in an Access97 database with tables linked to a Sql Server 7.0 database, the following error is raised:

Error #=80004005
Description = [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'CEMDPS' failed.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = S1000
NativeError = -2001

The web server (IIS 4.0) is on one server, the Access97 database on another server and the Sql Server 7.0 database on yet another.

Data can be accessed successfully from Access97 on client machines (no ODBC failure).

Data Access Components 2.5 are installed on the servers with both IIS 4.0 and the Access97 database (ODBC connection to the Sql Server 7.0 database from each tests OK). Data Access Components 2.5 are installed on each client machine (again, ODBC connections to the Sql Server 7.0 database from the client machines tests OK). The same DSNs are used in all cases.

The event log on the Server with the Sql Server 7.0 database shows a successful use of permissions by the user attempting access via the asp page.

Any suggestions?

 

[DougP]

since you did not leave your code I assume you are opening the DSN "CEMDPS"
Why don't you just leave Access out of it and connect direct to SQL Server?

here is the code for a SQL connection:
-----------------------------
<%
Set Conn = server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;driver=SQL Server;server=yoursite.com;uid=userID;pwd=password;database=yourdatabase;&quot;
Set RS = Conn.Execute(&quot;SELECT Count(*) AS Recs FROM [yourtable]&quot
%>
Records Found&nbsp; <%=RS(&quot;Recs&quot%>
-------------------------
This returns a record count so you know if its working or not.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[nazzaro]

The application is a massive one (for us, anyway) with dozens of VB and ASP applications that use the Access97 database. Maybe eventually we will code directly to the Sql Server 7.0 database, but for now, we want to get our apps working with the least amount of recoding.