Refresh linked tables when connection has been lost whit out closing access database

[MBMSOFT]

I have MDB database linked to SQL SERVER through VPN connection...
I created links to the sql server ..
Links are dsnless...

Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.
I receive message 3146 sql connection failed
I must close database and start again...

I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked...
Only way I can relink is to change ip adress in conn.string
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) and and that's it
if i lost connection for the 3rd time... i must restart application

it seems that access database keep the previous connection..
Any idea how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...

 

[DavidAtWork]

have you tried simply deleting the sql tables and rerun the code to connect again.

David

 

[MBMSOFT]

have you tried simply deleting the sql tables and rerun the code to connect again.

David

Yes i tried, but doesn't help

It seems that mdb/mde database keep first connection as active even it is broken...

 

[vbaInet]

I created links to the sql server ..
Links are dsnless...

How are these links created?

 

[MBMSOFT]

Function dao()

tbl = "MyTable"
CurrentDb.TableDefs.Delete tbl
Set A = CurrentDb()
Set B = A.CreateTableDef(tbl)
B.Connect = "ODBC;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=My User;pwd=MyPasword;"
B.SourceTableName = tbl
A.TableDefs.Append B
A.TableDefs(tbl).RefreshLink

End Function

 

[MBMSOFT]

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...

 

[vbaInet]

dao is a poor choice for a function name and let's do things properly:

Public Function LinkTdf()
    Dim db          As DAO.Database
    Dim tdf         As DAO.TableDef
    Const strTable  As String = "MyTable"
    Const STR_CONN  As String = "ODBC;" & _
                                "driver={SQL Server};" & _
                                "Server=MyServer;" & _
                                "database=MyDataBase;" & _
                                "uid=My User;" & _
                                "pwd=MyPasword;"
    
    Set db = CurrentDb
    db.TableDefs.Delete strTable
    db.TableDefs.Refresh
    
    Set tdf = db.CreateTableDef(strTable)
    tdf.Connect = STR_CONN
    tdf.SourceTableName = strTable
    
    With db
        .TableDefs.Append tdf
        .TableDefs.Refresh
        .TableDefs(strTable).RefreshLink
    End With
    
    Set tdf = Nothing
    Set db = Nothing
End Function

Try the above.

But I've got two more questions:
1. Where did you get the connection string from? Did you get it from the tabledef property, you made it up or you got it from the Description property of the linked table?
2. Are you sure that the SourceTableName is correct?

 

[MBMSOFT]

i tried your way... but no success ...
conn string is ok and table table is ok
so to resume
my way as well work even is not written properly
so i can refresh links in both ways
problem is when i loose connection to server (i'm connected to the server throgh vpn) loosing connection can be caused by unstable internet connection to most of the time ...
so when i lose coonection i cant refresh links.... I MUST RESTART APPLICATION..
so i want find a way to do that without restarting app ...
it should be something like refreshing mdb/mde .. it looks like that mdb/mde file keep broken connection as active

 

[vbaInet]

I added extra lines of code which I was hoping would help.

Since it's a VPN and there's loss of or drop in Internet connection it could be SQL Server session lingering around somewhere.

Have you tried the same code on an Access back end?

 

[MBMSOFT]

vbainet...
i found something but i don't understand quite
could you see this artikal
http://support.microsoft.com/kb/195225

 

[vbaInet]

Do the symptoms apply to your case?

 

[MBMSOFT]

i told you... when connection has been lost... i should clear something... i don't know what...there must be a way to reconnect the tables after connection is reestablished

 

[vbaInet]

Read the symptoms:

When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns: 

Run-time error 3146 "ODBC call failed".

Does the symptom apply in your case?