Simple question with a long answer (I'm sure)
We have a site that uses .asp pages to read/write to an access database and read from a SQL db. This data base sits on the same server as the web. We also have a SQL database located on a different server. We use a system dns for communication between the access db and the sql db.
Opening the access data base all is well and we can read all the sql tables/fields, write and display queries with no problem. We have a login for the SQL database with read only rights. All is good.
We publish with FrontPage 2003
We created an .asp file for a data view using the system dns. We setup the connection to login using SQL authentication with the user name and password. We have two db connections in the site. One for access and one for SQL. When using the sql connection we can see and read all table/fields and display them through IE.
Here's the problem and the question.
We created a qry in access that links 2 of the attached tables from SQL db. (this works fine viewing in the access db) When we create a results view using the access connection we can see the qry and all fields available with in the qry in design view in FP 2003. Problem is when published and view through IE we get this error back:
Database Results Wizard Error
Description: [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'sqlconnection' failed.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Here's the question: Can .asp use an access qry that is attached to link sql tables? I cant imagine why not.
Any tips or pointers?