SQL SERVER Vies is lossing its connection To The SErver

[ricardo1023]

I created an Access datbase 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

 

[genomon]

Try deleting the view, then relinking it. Make sure "Save Password" is checked in the link dialog box.

Gluais faicilleach le cupan làn