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
   end if    
 %> <!-- #INCLUDE FILE = "connect_ama.asp" -->
<style type="text/css">
font-size: x-small
.style1 {font-size: x-small}
.style2 {font-size: medium}
  .submitLink {
   color: #00f;
   background-color: transparent;
   text-decoration: underline;
   border: none;

   cursor: hand;
<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>
    <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"> &nbsp;&nbsp;<input name="logout" type="submit" class='submitLink' value="Login">
<table  width=85% align="center">

<P class="style1" align=left>
<strong>Exam Instructions:</strong><BR>
Please&nbsp;make a note of the
following&nbsp;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.
              For&nbsp;the entire exam&nbsp;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>

'Checks to see if the user has submitted a search or
'clicked a hyperlink
if u_search <> "" or u_id <> "" then
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
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%">
<td colspan="2" align="center"><u>Results</u></td>

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

<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>


' end check for no obs
end if
' end check for no input
end if %>
<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>
<input type="submit" value="Search">
Who is Participating?
FaircoConnect With a Mentor Commented:
Try using Jet instead of ODBC and see if the problem still appears. Using Jet I can access the linked tables as well.
Set the connection string this way, everything else can remain the same:
cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\project\myDatabase.mdb"
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.

EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.