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

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

[Seth Schrock]

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

[MBMSOFT]

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

ex. dao method

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]

one more question... which version of Access?

[Seth Schrock]

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]

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(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 mdb/mde file keep connection as active even it's broken

[zmbd]

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.

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]

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]

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]

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]

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]

Just a troubleshooting step...

In a new module

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]

I always get the same message before and after connection fails

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]

are those articles could be usefully with this issue
http://support.microsoft.com/kb/216950
http://msdn.microsoft.com/en-us/library/ms810829.aspx

[zmbd]

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.

[MBMSOFT]

when i put line currentproject.openconnection in code 7 as you wrote
I got err message 2467 the expression you entered refers to object that is closed or doesn't exists

[zmbd]

Do you have any open access objects during these events:
Forms, tables, queries, reports, and are these objects still open when you try to refresh the connections?

[jimatqsi]

It looks to me like your links are dependent on the IP address of and the IP address gets changed with each new connection. Can your network guy set you up with static IPs for this? Or, perhaps better yet, can you not use the name of the server to make your connection, instead of the IP address?

I don't really understand why the router IP ever is useful for making your connection to the database. But I'm far from being a network guy.

Jim

[zmbd]

jimatqsi
It looks to me like your links are dependent on the IP address of and the IP

MBMSOFT
Only way i can relink is to change ip adress in conn.stringEg 192.124.0.2(1st ip- router server ip)

Absolutly J,
Missed that statement in MBMSOFT's post - never hardcoded an IP address in any of my code because one can't rely on them to stay static

It doesn't however, explain why the Connections are being held up when the there's a hickup in the network. They should drop and become available again without neededing Access to be closed and re-opened.

M:Please post your exact connection string. You can use "*" for the actual sensitive bits.

I suspect now that J's pointed it out that you're using something like
DRIVER={SQL Server};Server=ip;....
when, as J noted:
DRIVER={SQL Server};Server=servername;.... might be the better way.

This article my trend to use DSNLess links: Using DSN-Less Connections It's an old MVPAccess link so be aware that it talks about ADO being the default... in ACC2003 DAO returned to being the default - that will make sense when you read the article.

[MBMSOFT]

not at all... just the code... i thought the same, that there might some object keep connection ... and just used only the code but again is the same

[zmbd]

not following your last post there M

[MBMSOFT]

i have tried in the past to use server name instead of ip adress in conn string but not always was that successfully (sometimes it happened some confusen that server name was not recognized in the network) i'm trying now to use servername and i'll see

[jimatqsi]

I see that problem sometimes with remote desktop. There are some instances where I can connect to the target system with server name and other instances where I have to to use the IP Address.Recent experience indicates that if I have one connection made with the IP address (over a VPN) then additional remote desktop connections can be made with the server name.

Here's a somewhat dated page discussing configuration settings on a network connection to address this.
http://technet.microsoft.com/en-us/l.../cc940089.aspx

Jim

[MBMSOFT]

BTW
I discovered something.. it might guide us to somewhere
I wrote code which after reestablishing connection is trying refresh links every 30 seconds and i found out that that it succeed to refresh link after 20-25 times trying (aprox.700sec/11min) so it seems old connection has been kept that time

[zmbd]

actual connection info please