Access FE, SQL BE, Linking Issues

[Nitro]

Hello All,

I'll try to summarize the situation as best as I can.

I have an Access 2003 FE linking to an SQL 2000 BE using a DSN-Less Connection String. Both are running on my local machine for development.

I created a LinkTables method (see below), so I can select different Connection Strings for use in different locations (ie. onsite at client, at my office for development). I have no problems getting it to work on my local machine. I even took it to another office where SQL was running on a server, and the Access FE was on a workstation. The LinkMethod worked perfect.

Onsite at the client different things happen. It links fine while on ther server.
However, when I try it on a workstation, it doesn't link correctly. It says everything linked successfully, but when I try opening a table I get the error:

Connection faled :
SQLstate : '28000'
SQL Server Error : 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(NULL)'.
Reason 'Not assoccated with a trusted SQL Server Connection'

Then it opens a Login box asking for a login. If I enter the correct Username and Password, all the tables open. Its almost as if the UID and PWD in the Connect String are being ignored.

Any idea's what the problem is?

Thanks.

Public Sub LinkTables(gLocation As String)
Dim DBPath As String
Dim td As TableDef
Dim p, i As Integer
Dim s, c, e As String

    On Error GoTo Error
    DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
    
    On Error Resume Next
    If gLocation = "" Then Exit Sub
    c = DLookup("ConnStr", "Settings", "ID = '" & gLocation & "'")
    If c = "" Then Exit Sub
    On Error GoTo Error

    ' For Testing
    c = "ODBC;DRIVER=SQL Server;UID=audituser;PWD=audituser;SERVER=CORPMAT;DATABASE=AuditTest"
    
    i = 0
    For Each td In CurrentDb.TableDefs
        s = td.Connect
        If Left(s, 4) = "ODBC" Then ' Check for SQL Link
            td.Connect = c
            td.RefreshLink
            i = i + 1
        End If
    Next
    Set td = Nothing
    If e <> "" Then
        MsgBox "Error Linking Tables:" & cr & e, vbExclamation
    Else
        MsgBox CStr(i) & " Tables Linked Successfully. Please re-open the database."
        Quit
    End If
leave:
    Exit Sub
Error:
    MsgBox "Error Linking Table! " & td.Name & cr & " Error: " & Error$, vbCritical
    Resume leave
End Sub

 

[mattd]

Access linked table problem

Hi Nitro,

I know I am resurrecting an old post, but I am experiencing the same linked table problem you posted about and can't find a solution on any of the forums. Did you ever manage to resolve it?

Any light you can shed on the problem would be very appreciated!!

I've done some investigation of my own and it looks as though Access ignores the connection string you specify and sets 'trusted_connection=yes' in the connection string if your Windows login has permission to access the SQL Server database.

However, if your Windows login does not have permission to run a select against the specific table you are trying to link to, you then get the login failed error...

Hope you can help.

Many thanks,
Matt.

 

[DataMiner]

I know this is old, but I am having a very similar problem, but it seems to be intermittent. (see my post "change to dsn-less tables"). So... did you ever get an answer to this?

 

[mattd]

Hi Dataminer,

The problem I was experiencing turned out to be related to the way Access pools connections.

If you already have a connection open to a database using integrated security, but then subsequently try to establish a connection (to the same database) using sql server authentication it will re-use the connection already open (hence the connection string is set to trusted_connection=yes).

This is a flaw with MS Access and will prevent you using DSN-less connections if you are using a mixture of Windows and SQL Server authentication.

Hope this helps.
Matt.