Lossing SQL Server Connection FOR A SQL View

[ricardo1023]

I linked a SQL server view using ODBC and works well. Everytime I close the Access database (accdb Access 2010) and open back and click on the view I have to reenter the user ID, Password etc to get to the view data. I got a piece of code from the internet to refresh my links and it works for all the linked tables but not for the view. Can you help me? Here is the code that I am using

Function RefreshODBC()

On Error Resume Next
Dim tdf As TableDef, db As DAO.database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
' I added the next if statement to see if I could refresh this way but it do not help either
If tdf.Name = "dbo_Hector_Databases_View" Then
tdf.Connect = "ODBC;DRIVER={sql server};DATABASE=MASTER" & _
";SERVER=test\test" & ";UID=sa;PWD=*test*;"
tdf.RefreshLink
End If
If Len(tdf.Connect) > 0 Then
If Left$(tdf.Connect, 4) = "ODBC" Then
tdf.RefreshLink
End If
End If
Next
Set tdf = Nothing
Set db = Nothing
MsgBox "Refresh Complete"
End Function



Thank you

 

[1DMF]

I use the same method and my views work fine.

I take it you linked the view as a standard datasource the same way you do for tables?

Have you checked the permissions on the view SQL side, just to be sure?

This is the function i call to re-link the DNSLess Tables

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
    
AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & err.Description
End Function

If the view hasn't been correctely re-linked it wil be using trusted connection and your logon credentials which will cause problems with loss of authentication to access the view.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts