asked on

Linking to a SQL server via Access- "ODBC--connection to 'SQL Server###.##.###.#' failed."

I get the code below to work when entering a regular Access table, but can't get it to work with a linked table (dbo_regIDLookup). Is it the "ID" that is giving me the above error? I CAN connect to the linked table via Access.

<%@ Language=VBScript %>
<%
dim userid
userid = Session("userid")
if userid ="" then
Response.Redirect("exam_login.asp")
end if

'''''''''''''''''''''''''''''''''''''''''''''''''''
%> 
<style type="text/css">

</style>
<body bgcolor="#FFFFFF">
<table cellPadding="0" cellSpacing="0" border="1" width="85%" align="center">
<tr><td bgColor="#ffffff"><table bordercolorlight="#999999" bordercolordark="#999999" align="center" width="100%"><tr><td bgColor=gold><div align="center"><font face="arial" size="3">Testing Center</font></div></td></tr></table></td></tr><tr><td align="center">

<TABLE width="100%" border=1 align="center" cellPadding=0 cellSpacing=0>

<TR>
<TD style="background-color:#cccccc" width="50%" align="center"><form STYLE="MARGIN:O method="post" action ='student_profile.asp' id=form2 name=form2>
<input name="B1" type='submit' class='submitLink' value="View Profile and Scores"></TD></form>
<TD style="background-color:#cccccc" width="50%" align="center"><form method="post" action ='exam_login.asp' name=form1>
<input name="logout" type="submit" class='submitLink' value="Logout">   <input name="logout" type="submit" class='submitLink' value="Login">

</TD></form></TR></TABLE>
<table width=85% align="center">
<tr><TD>

<P class="style1" align=left>
<strong>Exam Instructions:</strong><BR>
Please make a note of the
following before choosing your exam.</p><OL class="style1" style="MARGIN-TOP: 0in" type=1>

<LI> Questions
are multiple choice type. I.E., only one answer is correct. The answers are represented by four radio buttons "<INPUT id=radio2
style="LEFT: 127px; TOP: 271px" type=radio name=radio2> ".

<LI>Questions appear one at a time. Confirm your answer before hitting
the Next button. You can come back and change the answer.
<LI>
For the entire exam you are
given some limited time (The timer is also displayed on the status bar of the
browser). </LI>
<LI>The exam will be removed from your list of available exams upon successful completion. </LI>
<LI>Students failing a test twice will not receive credit for the Course.</LI>
</OL>
</P>

</tr></TD></table>
<%
u_search=request.form("u_search")
u_exp=request.form("u_exp")
u_id=request.querystring("u_id")
'Checks to see if the user has submitted a search or
'clicked a hyperlink
if u_search <> "" or u_id <> "" then
accessdb="../db/bank_ama"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
'Determines if the user submitted a search or clicked a link
if u_id <> "" then
sql= "select * from dbo_regIDLookup where NO_SCH=" & u_id
else
sql = "select * from dbo_regIDLookup where "& u_exp &" like '%%" &u_search &"%%' "
end if
rs.Open sql, cn
%>
<%'if there are no observationb found display no record found
if rs.eof then %>
No selections could be found
<% ' if observations are found display them
else %>
<table align="center" width="85%">
<tr>
<td colspan="2" align="center"><u>Results</u></td>

</tr>
<%
rs.movefirst
variable = Replace(Request.Form("ExamName") , " ", "_")
do while not rs.eof%>

<tr>
<td align="center"><form style'margin:0' method='POST'action ='../user/exam_random.asp'><input name=start type='submit' class='submitLink' value='<%= rs("ExamName") %>'>
</td><td align="left"><font size="2"><%= rs("Description") %></font></a>
</td>

</tr>
<%
rs.MoveNext
loop%>
</table>

<%
' end check for no obs
end if
' end check for no input
end if %>
<hr><table>
<tr><td>
<form action="<%=request.servervariables("script_name") %>" method="post">
Enter the Course name or Class ID here: <input type="text" name="u_search" value="<%= u_search %>">
 
<select size="2" name="u_exp">
<option selected value="ExamName">Name of Course</option>
<option value="Description">Class ID</option>
</select>
 
<input type="submit" value="Search">
</form>
</td>
</tr>
</table>

ASKER CERTIFIED SOLUTION

Fairco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

masirof

No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: Fairco

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

masirof
EE Cleanup Volunteer

Linking to a SQL server via Access- &quot;ODBC--connection to 'SQL Server###.##.###.#' failed.&quot;

Linking to a SQL server via Access- "ODBC--connection to 'SQL Server###.##.###.#' failed."