Sql server dropping connection with Access view

[Gill1978]

Hi there!!

Havin a right mare of a problem with a linked table (generated through vba code in Access).

When I run this code it opens a form with data retrieved from a view in sql server. There is a button on this form that when clicked is used as a search form and uses the same view (which is using the linked table generated earlier when the original form was opened). However when this search form is run I get the following error

"[Microsoft][TCP/IP Sockets] ConnectionRead(rec()). [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] General network error. Check your network documentation. | ODBC --call failed. |"

If before I open the search form, I go to the tables and manually open the linked table, then go and run the search form, it runs fine.

Also, if i use another database (UAT) database which has all the same views tables etc the forms run fine.

Could this be an issue with resetting/ reinstalling the ODBC sql driver. This is also happening to other users.

Can anyone help????

 

[Tapew0rm]

DISCLAIMER!!!! I'm just got my hands into SQL, be warned, this is just a work around. I had a similar problem with an xl link.
I changed the view to a procedure with all of the views calculations and had it make a table then edited all the calculations out of the original view. It worked, though it still doesn't solve the root problem.

So instead of Table -> Veiw -> XL

It went Table ->Procedure ->Table (w/calculations already done) -> View

I guess you could link directly to the new table, but I was lazy.

Another thing that sometimes works for me is

click start > Go to Settings > Control Panel > Admin Tools > Data Sources (ODBC) > Select System DNS tab > Select SQL Server (workstation) > Click configure > Click next > Press Client Configuration button > Change Network Libraries to named pipes.

 

[Gill1978]

Hey there the last option there didn't work.

Seems after a little more investigation its just one of the linked tables that loses its connection. This one provides the data for the whole form while the second one (which works) provides data for the subform.

The view that the 1st linked table looks at, is the view that I altered to add a new field this broke the code and then I reverted the view back to how it was and I still get the error I mentioned in the beginning.

Now am at a complete loss. I think maybe the thing is to check to see if the connection of the linked table has been lost if so re-establish the connection through the code. So can anyone help me with this??

Thanks

 

[hwkranger]

This is probably the wrong forum for this, but I'll answer:

Public Function fChangeTblConnectString(str_DBName As String, str_Server, str_Localtbname)
'This sample function changes the connect string of the linked SQL Server
'Events database to be DSN-less.

On Error GoTo TblCnt_Err

Dim db As Database
Dim t As TableDef
Dim int_error As Integer

Set db = CurrentDb
    For Each t In db.TableDefs
        's.MoveMeterBar
        'Just the one specified
        If t.Name = str_Localtbname Then
            If t.Attributes And dbAttachedODBC Then
              t.Connect = "driver={SQL Server};server=" & str_Server & ";" & _
              "database=" & str_DBName & ";" & "Trusted_Connection=Yes;" & "Network Library=DBMSSOCN;"
              t.RefreshLink
            End If
        End If
    Next
db.Close


If int_error > 0 Then
    MsgBox ("There were " & int_error & " errors during reconnection of tables.")
End If

Exit Function
TblCnt_Err:
    int_error = int_error + 1

End Function

will fix your linked table. This was sort of rushed -- pulled it from garbage, but you can modify it as you see fit. I have an old function that relinks all the tables in my db when it starts up..



Randall Vollen
Merrill Lynch

 

[Gill1978]

Hey ...

That seems great ... however do you know how to check the connection of a linked table that has previously been made so that the routine is only run when a connection has been dropped?

Thanks for all the help.

 

[Gill1978]

Put in some simple re-connection code ...
and the connection is still being dropped ... I'm thinking it must be sql server for some reason !!??
Does it have some memory of failures that needs to be cleared ... I'm totally lost!!