I have an Access database that has some regular old Access tables in it. But I also have some ODBC links in the same Access database to some tables on a SQL Server database. Is it possible to connect to my Access database and query the SQL Server tables through my Access connection in ASP? I've tried it, but I keep getting an error, so I'm not sure if its possible. I'd like to avoid having to connect directly to the SQL Server database.
For example, say the DSN to the Access database is called 'AccessDSN' and within Access I linked to SQL Server tables using a DSN called 'SQLDSN'. If I try to query any of the SQL Server tables through ASP using the 'AccessDSN' connection, it gives me this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SQLDSN' failed.
The reason I want to use Access is I have some queries that link my Access tables to the SQL Server tables.
Is it even possible to do it this way or do I have to link directly to the SQL Server to get the other tables?
Thanks for your help!