I created a .DSN file (pointed at SQL Server) and placed it on my file 

server.  I created a pass-through query in Access 97 that uses this file 

as its data source.  It works fine.  It shows me data from the SQL server.



I copied the connection string from this pass-through query into a 

function to try to establish the connection using VB. The connection fails.



Here is the function.  Please help if you can.



Sub useron()

    On Error GoTo useron_error

    Dim Gostr As String

    Dim wrkODBC As Workspace

    Dim conPubs As Connection

    Dim testguest As Recordset

    Dim testguest2 As String

    

    Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _

        "admin", "", dbUseODBC)



    Set conPubs = wrkODBC.OpenConnection("Connection1", _

        dbDriverNoPrompt, , _

        "ODBC;DSN=QISA;Description=QISA 

Server;UID=dm;PWD=;DATABASE=QISA;Network=DBMSSOCN")



Set testguest = conPubs.OpenRecordset("SELECT username, accesslevel FROM 

usage where username = '" & sGetUser & "' and DBUsed = '" & CurrentDb.Name 

& "';", dbOpenForwardOnly)

     On Error GoTo linenext

    testguest2 = testguest.Fields("AccessLevel")

linenext:

    If testguest2 = "" Then

       Gostr = "INSERT INTO Usage (UserName, Lastlogin, AccessLevel , 

DBUsed) VALUES ('" & sGetUser() & "','" & Now() & "','Guest','" & 

CurrentDb.Name & "');"

       conPubs.Execute Gostr

    Else

        Gostr = "update Usage set lastlogin = '" & Now() & "' where 

username = '" & sGetUser & "' and DBUsed = '" & CurrentDb.Name & "';"

        conPubs.Execute Gostr

        Gostr = "update Usage set lastlogoff = NULL where username = '" & 

sGetUser & "' and DBUsed = '" & CurrentDb.Name & "';"

        conPubs.Execute Gostr

    End If

    Rem MsgBox testguest2

    testguest.Close

    conPubs.Close

    wrkODBC.Close

GoTo outtahere

useron_error:

    MsgBox "Your Data Source has not been defined.  Please call your 

System Administrator for help."

    

outtahere:

End Sub