Unanswered: ASP ADO Error - ODBC Connection Failed - Using SQL Server Linked Tables In Access
I am using SQL Server 2000 sp3. The mdac version on the web server is 2.7 I am linking one table from sql server into an Access 2000 Database using an ODBC connection from a system DSN. There is a query that joins the data from the sql server table onto some jet tables. Everything runs correctly from within Access. Only when I try and select * from my access query within ASP do I get the
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'MyDSN' failed.
I have searched numerous forums and have found this to be a re-occuring issue but have never found an answer. I have done this before but I just cant figure out what is wrong this time
I have been down that road too. I have tried SQL Server and Windows Authentication. I used administrator for IIS and for the connection info for the ODBC System Datasource. I turned on auditing on the sql server for logging all logins and it appears to me that the request for the login never even happens so I don't think its a permissions issue on the sql server. I checked the permission on the access database and the folder that it is in. I have tried running it on Developer and Enterprise editions of sql server, professional and windows 2000 server, the web site on one machine and vice versa, the web server and sql server on the same machines. It wouldn’t surprise me if its something in SP3 or MDAC 2.7 which is on both of my development machines and the production machines.
If someone could tell me how to reverse MDAC perhaps I could track it back that way.