refresh SQL connection (1 Viewer)

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
Refresh linked tables when connection has been lost whit out closing access database

I have MDB database linked to SQL SERVER through VPN connection...
I create links to the sql server with dao method..
everything works fine but when I lost VPN connection i cant refresh links to the tables with ado method, I receive message 3146 sql connection failed
I must close database and start again...

Any idea how to cut previous sql connection and refresh links to the tables with vba code without closing access database
 
Last edited:

Rx_

Nothing In Moderation
Local time
Today, 08:30
Joined
Oct 22, 2009
Messages
2,803
Code:
' basically, the linked tables are set with values.
' My process has a Local Table with all of the TableName
' A procedure (not shown) looks at each table name and sets up the 
' DNSLess2 settings
' Then at the end of the connection - it refreshes the collection
' you will probably be interested in the refresh below for your error
Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
    Dim ConnectString As String
    Dim strConnectionString As String
    Dim scn As String
    Dim sLocalName As String
    Dim UID As String
    Dim pwd As String
    Dim ServerString As String
    ServerString = "MyServerInstance\MyDBInstance"
    UID = "MySuperUserID"     ' temp SQL name change later  ' example UN/PW SQL security
    pwd = "MyPW"
    sLocalName = TableName  ' "ATableName" ' <<<---manually add a table name here for one single table
    Dim DatabaseName As String
    
    If fTest = False Then
        DatabaseName = "MYDBName"
    Else
        DatabaseName = "MYTESTDBName"    ' <<<<<<<<<<------------  DBT or DB  ////////Uncomment for Test DB Server name next
    End If
    ' example of hard coded Server / SQL Server name - instead of variable
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=MyServerl\MySQLServer;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
       "SERVER=MyServerl\MySQLServer;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    
    
    ModifiedRefreshDNSLess2 = strConnectionString
    'Debug.Print strConnectionString
End Function
 
Sub RerefreshLinkedTables() ' refreshes Linked tables doesn't refresh icon
  ' On your error, this might be the refresh you are looking for        
            Dim dbs As DAO.Database
          Dim tdf As TableDef
10        Set dbs = CurrentDb
20        For Each tdf In dbs.TableDefs
30            If Len(tdf.Connect) > 0 Then
40                    tdf.RefreshLink
                      'Debug.Print tdf.Name
50            End If
60        Next tdf
70        Set dbs = Nothing
End Sub
 

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
hm... it does not solve problem as i could understand... could you send ftest code...
ANYWAY PROBABLY I NEED THE WAY TO CLOSE PREVIOUS DNSLESS CONNECTION TO SQL SERVER WHICH HAS BEEN BROKEN
AND CREATE IT ONCE AGAIN
SOMETHING LIKE
APPLICATION.CURRENTPROJECT.CLOSE CONNECTION (IT PROBABLY WORKS IN ACCESS PROJECT BUT NOT IN MDB )
 

Rx_

Nothing In Moderation
Local time
Today, 08:30
Joined
Oct 22, 2009
Messages
2,803
I don't close anything when I switch my connections from the production to the test db, I just delete all the linked tables and remake them from scratch, using the local table to keep the names.

If by chance you loose connection. Then go to the Linked Table refresh (manually) does it reconnect?
 

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
Also manual linked table manager refresh doesn't work (btw i need automatic way to relink tables, but i can't refresh even manually) except to close application and start again...
A try to change methods, (ado, dao and vba way docmd.transferdatabase, aclink...) and try to chanfe drivers in connection string odbc,native client sqloledb)...
The only way is to connect server to different ip address e.g.192.168.0.2 -first ip(router ip) then 192.168.0.31(local server ip), but if i lost connection second time no alternative ip which i can use third time to refresh links
:(
 

Rx_

Nothing In Moderation
Local time
Today, 08:30
Joined
Oct 22, 2009
Messages
2,803
The VPN is a little different.
Lets try just one table first and see what happens.
On the code that I posted, hard code in the connections for 1 table.

I had to look at my old code to remind me. First I drop all linked tables, just totally destroy them. See Function DropAllLinkedTables
Then I rebuild them with code. See the code posted above, re-create the linked table.
Then I Refresh them so the icon looks good.

Once you see this works with one table, then build a local table with the name of all the linked tables. Ust that table to loop and rebuild again.
For about 150 tables, it only takes a couple of seconds to do this.

Code:
Public Function DropAllLinkedTables()
    Dim tdf As DAO.TableDef
    
10        For Each tdf In CurrentDb.TableDefs    ' If the table has a connect string, it's a linked table.
              ' Drop all tables and rebuild again from local table SQL_Linked where names are stored
20            If Len(tdf.Connect) > 0 Then
30                DoCmd.DeleteObject acTable, tdf.Name
40            End If
50        Next tdf
End Function



Code:
Sub RerefreshLinkedTables() ' refreshes Linked tables doesn't refresh icon
          Dim dbs As DAO.Database
          Dim tdf As TableDef
10        Set dbs = CurrentDb
20        For Each tdf In dbs.TableDefs
30            If Len(tdf.Connect) > 0 Then
40                    tdf.RefreshLink
                      'Debug.Print tdf.Name
50            End If
60        Next tdf
70        Set dbs = Nothing
End Sub
 

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
i have done that
i tried now one more time
It doesn't work...

Only way I can relink/refresh tables links is to change an ip adress in conn.string

conn.string="ODBC;driver={SQL Server};Server=192.124.0.2(or .32); database=MyDataBase;uid=My User;pwd=MyPasword;"

eg 192.124.0.2(1st ip- router server ip) and after connection failed i can use 192.124.0.32(2nd ip - server local ip - relink is successful ) and that's it
if i lost connection for the 3rd time... i must restart application
:(
 

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
I send a short way of dao connection... in my code is written as you wrote...

In mean time i found something interesting, it might help that one

I open systable MSysObjects so
access write links inside, when i delete link manually or with code, the link in systable still exists,
if i restart access if the link has been deleted before closing, it no exists anymore in MSysObjects
and you can relink table successfully

So if there is some way to edit MSysObjects - delete links(but as red some - it's not recommended) or just reset or refresh the MSysObjects with some command it seems it will be grate solution for this issue...
Any opinion about this...
 

MBMSOFT

Registered User.
Local time
Today, 15:30
Joined
Jan 29, 2010
Messages
90
:( No...That's what i already have...
So I should look how to refresh msysobjects after i loose connection to sql server through vpn and then reconnect again
 

Rx_

Nothing In Moderation
Local time
Today, 08:30
Joined
Oct 22, 2009
Messages
2,803
Is this what you are doing?

SELECT MSysObjects.Database, MSysObjects.Connect, MSysObjects.ForeignName, IIf(IsNull([Connect]),"",Right(Left([Connect],Len([Connect])-1),Len([Connect])-(InStr(1,[Connect],"PWD=")+4))) AS connectPW, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))
ORDER BY MSysObjects.Database, MSysObjects.ForeignName;

MSysObjects.Database = File Name & Full File Path
MSysObjects.ForeignName = Linked Table Names (Type = 6)
connectPW = Password (if applicable)
 

Users who are viewing this thread

Top Bottom