I have tried to make a DSN and a DSN-less connection to SQL databases both on my local machine and on my network. I *can* make a connection through Microsoft Query Analyzer to both databases through Windows authentication.

The ASP code is as follows:

1) For the DSN-less connection
<%
    Dim objConn
    Dim objRec
    Dim strConnect

    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")
    strConnect= "Driver=(SQLServer);Server=Remote; DataBase=Warehouse;UID=myname;PWD=mypassword;"
    objConn.Open strConnect

    objRec.Open "Select top 10* from myTable", objConn, 0,1,2

For the local machine substitute:
       strConnect = "Driver=(SQL Server);Server=(local);DataBase=localDB;UID=myname ;PWD=mypassword;"
%>

2) For the DSN connection I connect the ODBC to the remote database, naming the DSN "Remote" and to the local database naming it "Local". Both Test connections succeed.

I use the following code:

<%
    Dim objConn
    Dim objRec
    Dim strConnect

    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")

  'For remote connection
        objConn.Open "DSN=Remote"
   'For local connection
        objConn.Open "DSN=(local)"

%>

In all cases I get back the error message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I know the data source is there, and I *did* mention the default driver.

What's going on? Why can't I make this connection?

John


Johnny6000

does this help? http://www.aspfaq.com/show.asp?id=2126

try with using provider instead of driver.
 provider=sqloledb.1;server=remote;...........