reset or drop database connection to the sql server and refresh links to the tables

**Seth Schrock** · Sep 4 '14, 01:38 PM

Have you tried the TableDef.Refres hLink method? It would really help to see your code and what exactly you have tried.

**MBMSOFT** · Sep 4 '14, 01:59 PM

I tried different methods like ado,dao, and vba docmd.transferd atabase,aclink. .

ex. dao method

Code:

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

so if VPN connection to the server either sql-connection has been broken then I can't refresh table link on this way, only if I restart database mdb/mde
I.m considering that mdb/mde look previous connection...
so is there any way to drop old connection or reset database somehow withot closing it

**zmbd** · Sep 4 '14, 02:14 PM

one more question... which version of Access?

**Seth Schrock** · Sep 4 '14, 02:22 PM

In what way doesn't this work? Does it get an error message or does it run and just leave the tables not connected?

**MBMSOFT** · Sep 4 '14, 02:42 PM

Access 2003

The error is 3146 odbc call failed
So if connection is broken/lost when i connect again a can't refresh links to the sql server table, i have to restart mdb/mde

A tried as well to delete linked tables
And recreate them and again does not help

Only what i can
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(2n d ip - server local ip) and and that's it
If i lost connection for the 3rd time... I must restart application

It seems that mdb/mde file keep connection as active even it's broken

**zmbd** · Sep 4 '14, 03:53 PM

1) Please do not use either mostly or all uppercase letters in your posting.
We well understand your frustration with the situation... at one point or the other; we've been in situations that have caused much stress.
We also well understand that texting is not always the most efficient manner of communication and often something is clear to you or another and needs a follow-up question for someone else. We do not all think at the same speed, nor even in the way... that's the strength of the site (^_^) ; thus, humor us a tad, and help us to help you by provideing the requested information and allowing the oldtimmers to chew on the information.

2) You are not explicitly setting either DAO or ADO database object. This can create an entire set of issues that are more than a simple trouble shooting step.
DAO is supposed to be the default in ACC2003; however, failing to explicitly set the object type can cause issues.

3) You should have the Option Explicit set in your database. The following link will provide the explanation and details.
> Before Posting (VBA or SQL) Code
Don't let the title put you off here... it is the troubleshooting section that I'm referring you to. (^_^)

4) Your code has a few things that don't make sense to me. Following is a sanitized version of a code I have used for quite a while with good success without any of the connections issues you express.
>> Note, that while sanitizing the code, I may have broken something; also I usually I pass the table names, user, password into the function - I've removed these and hardcoded the information using your connection string and table name from your posted code; thus, no warranties as to "out of the box" operations.

Code:

Option Compare Database
Option Explicit

Function nodsnconnection()
    Dim zdb As DAO.Database
    Dim ztdf As DAO.TableDef
    Dim zLocaltbl As String
    Dim zRemotetbl As String
    Dim zstrCon As String
    Dim ZError As Error
    Dim zkillloop As Boolean
'
On Error GoTo zerrortrap
zkillloop = False
'
    zLocaltbl = "MyTable"
    zRemotetbl = "MyTable"
    zstrCon = "ODBC;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=My User;pwd=MyPasword;"
    Set zdb = CurrentDb()
    zdb.TableDefs.Delete zLocaltbl
'
'Edit>Added 2014-09-10
    '
    CurrentProject.OpenConnection
    '
    '
    Set ztdf = zdb.CreateTableDef(zLocaltbl, dbAttachSavePWD, zRemotetbl, zstrCon)
    zdb.TableDefs.Append ztdf
    '
    '
    nodsnconnection = True
'
zerrorcleanup:
    If Not ztdf Is Nothing Then Set ztdf = Nothing
    If Not zdb Is Nothing Then Set zdb = Nothing
    Exit Function
zerrortrap:
    nodsnconnection = False
    If zkillloop Then
        MsgBox "Non-recoverable error occured within error loop" _
            & vbCrLf & Err.Number _
            & vbCrLf & Err.Description, vbCritical, "Fatal Error"
        Resume zerrorcleanup
    End If
    zkillloop = True
    MsgBox "There are " & Errors.Count & " connection errors", vbExclamation, "Connection Errors"
    For Each ZError In DBEngine.Errors
        With ZError
            MsgBox .Number & " " & .Description
        End With
      Next ZError
    MsgBox "There are no more connection errors", vbExclamation, "Connection Errors"
    Resume zerrorcleanup
End Function

Yes, of course, I borrowed bits and pieces of the above code from various books... can you find it online... most likely something close to as it is a fairly standard bit of DSNless connection code.

**MBMSOFT** · Sep 5 '14, 02:03 PM

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

**zmbd** · Sep 5 '14, 03:37 PM

Messing with MSysObjects table === messing with the Windows Registry file...
You mess this table up there is most likely no help for you.

+ Please attempt the code I provided.
+ The code you provided does not handle the connections properly from what I can tell.

**MBMSOFT** · Sep 6 '14, 12:10 PM

yes i tried your code as well

but the problem remain...
when I lost connection to the sql server I can't refresh links to the tables.

before connection has been lost ... I can refresh or relink tables as much times I want...

but after losing connection (I'm connecting to the sql server through VPN connection) and reconnecting to sql server I can't any more refresh links with code...
I must close APP and start again

Because of that I asked you is there some possibilities for automatic refresh or reset or reload system objects in MDB?MDE file

it seems that lost connection is stored somewhere in database and access keep that as active until it has been closed

**zmbd** · Sep 6 '14, 04:49 PM

The code I posted has not failed for me in the manner you are experiencing.

I just noticed, I did break something minor in the cleanup, Line28 is an extra bit and shouldn't be there as there are cleanup code in lines 31 and 32...
You should also have these same types of clean up code in all of your VBA to release anything set once no longer required - rule of thumb, if you open it - close it, if you set it - release it.

If you did run the code I gave you, how many error messages popped up?
There should have been at least two. The exact number and text of the errors would help.

Have you talked to your IT department? This sounds very much like an issue in how they've set up your VPN and there are too many variables to go over in a thread but I would be asking about addressing limits and persistence of connection.

**zmbd** · Sep 6 '14, 06:06 PM

Just a troubleshooting step...

In a new module

Code:

Option Compare Database
Option Explicit

Public Sub ShowConnectString()

    Dim objCurrent As Object

    Set objCurrent = Application.CurrentProject
    Debug.Print "The current base connection is " & objCurrent.BaseConnectionString
    Set objCurrent = Nothing

End Sub

Now run your database until the connection fails as you've described.
Once the issue has re-appeared run the above code.

Press <Ctrl><g> and copy and past the string you find there into a post here ... please enclose the string in code tags by selecting it and then clicking on the [CODE/] button.

Please post all new/additional errors

**MBMSOFT** · Sep 10 '14, 07:03 AM

I always get the same message before and after connection fails

Code:

PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Projects\ACCESS\mbm_soft\OTN\OTN.mdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=C:\Documents and Settings\Administrator\Application Data\Microsoft\Access\System.mdw

**MBMSOFT** · Sep 10 '14, 07:11 AM

are those articles could be usefully with this issue

Error - Office.com - Microsoft Support

http://support.microsoft.com/kb/216950

Pooling in the Microsoft Data Access Components

http://msdn.microsoft.com/en-us/library/ms810829.aspx

**zmbd** · Sep 10 '14, 01:26 PM

Both of those articles are outside of the scope of what I can offer you here... more along the lines of what your IT-Dept could do to help.

I the code I posted... I've added:
CurrentProject. OpenConnection
the code block in Post#7
Leave this without any options. It should kill all of pending connections that Access has control over.
I've inserted this after the table definition deletion in an attempt to avoid any "in use" types errors.

caution here... there is a potential for error if you run the CurrentProject. OpenConnection a second time.

I've only added CurrentProject. OpenConnection to the code here as a special case use, Ideally, you should be able to just run the CurrentProject. OpenConnection command as is from a single line, one time, and have everything re-establish without the need to relink/refresh the linked tables without the need for any other listed in post#7.

reset or drop database connection to the sql server and refresh links to the tables

reset or drop database connection to the sql server and refresh links to the tables

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment