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

Posted on 2004-08-06
Last Modified: 2008-03-06
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">
Question by:stephenrex

Accepted Solution

Fairco earned 250 total points
ID: 11736289
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"

Expert Comment

ID: 12708702
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question