2. ASPRunnerPro General questions
  3. -2147467259
This topic is locked

-2147467259

 		3/9/2004 6:12:32 AM
ASPRunnerPro General questions
A
 awl author

I use MS Access (serwis.mdb) database with MS SQL (OCSSMALL03) tables linked and every time I try to view data in IE I receive above mentioned error number with description [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'OCSSMALL03' failed. There is dbconnection line:

<%

strConnection = "Provider=MSDataShape;DSN=serwis.mdb;UID=ocssmallquery;PWD="

%>

When Server.MapPath is used I receive the same error. When tables are imported there is no problem also there is no problem with ASP code built directly from MS SQL tables. So every day I have to import fresh tables from MS SQL into MS Access database <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=438&image=1&table=forumtopics' class='bbc_emoticon' alt=':(' /> .
best regards,
Adam.
Sergey Kornilov admin 3/10/2004

I guess it happens because IIS user don't have rights to connect to SQL server. Make sure that when you link SQL server tables in MS Access you use SQL server authentication instead of Windows authentication.
A
 		awl author 3/12/2004

During linking SQL tables there is no possibility to choose type of autentication. Only when ODBC DSN is configured I can make choice of SQL or Windows autentication. When SQL tables are linked I'm asked for password only.
kind regards,
Adam.
Sergey Kornilov admin 3/12/2004

Adam,
I see what you saying.
I this case I would recommend to connect to SQL Server directly without using linked tables in MS Access.
A
 		awl author 3/15/2004

Sergey,
Maybe it is easier to do it with SQL directly with one table what I've alredy tested but if I could do what I really need this way I would do it. It is much easier to prepare queries with MS Access and display data with ASP for more tables. Especially when I need data agregation using columns from different tables. OK, I know there is ASPRunner Pro released but I can't do it with Pro the same way as with MS Access. ASP Pro creates separate views of a few different tables or columns from those tables and I need one view of a few columns from different tables. I was aslo trying to re-build SQL query in "Edit SQL query manually" window of ASPRunner but it doesn't work. ASPRunner doesn't understand command SUM, GROUP BY, HAVING, etc built with a number of colums. I spent a few hours with my colleague specialist of SQL & Access without success. I just would like to know why ASP is not able to connect to our SQL database across MS Access tables linked with access password for SQL saved.
kind regards,
Adam.
Sergey Kornilov admin 3/17/2004

When you link SQL Server tables in MS Access you can create new ODBC DSN that points to your SQL Server. When you create DSN you have a choice between "Windows authentication" and "SQL Server authentication". You need to choose "SQL Server authentication" and type username and password.
This way it should work on ASP pages.