Relinking Access Linked Tables (Multiple Back-Ends)

[sherryj2]

Hello, I'm a total newbie here, but I have been searching for days for the answer to this without any luck.

I used your function to refresh my linked tables upon opening the front end of my database. It worked like a charm for linked tables that reside in only one SQL Server database. Then I moved on to my next front end, that has linked tables that reside in 3 separate SQL Server databases, and no joy, of course. Is it possible to expand this code to look to the 3 different databases?

Thanks in advance for any help you can provide.

Jennifer

[NeoPa]

Hi Jennifer.

I've split this from Relinking ODBC Tables using VBA as that is an article and you need a question thread.

Administrator

[NeoPa]

I don't have any such database to play with at the moment, but if you can post some details about the .Connect strings of the tables you want to change, and what about them you want to change, then we can look into it for you.

A copy of the current state of your code related to this would be helpful too.

[sherryj2]

Here is the code I mentioned I was using above:

http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba

It works for a single back-end, but not for multiple backends

[sherryj2]

Here is how I have modified the code that Mary provided (I only added the ODBC connection string):

Function relinkTables()

Dim tdf As DAO.TableDef

     For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
             tdf.Connect = "DRIVER=SQL Server;SERVER=OurServer;DATABASE=sdeTrails;Trusted_Connection=Yes"
             tdf.RefreshLink
        End If
    Next

End Function

It goes through the list of linked tables and refreshes them all for my single back-end Access front-end. But when I try to run this code in an Access front-end that has linked tables in 3 different SQL server databases (all on the same server instance), it won't work. I understand why (because the table I want to refresh is not in the database specified in the single connect string I provided), but I just don't know how to say in code "either look for these tables in Database #1 or look for these tables in Database #2 or look for these tables in Database #3, and then refresh the link".

Does that make any sense?

Thanks-
Jennifer

[NeoPa]

Here is the code I mentioned I was using above:

http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba

It works for a single back-end, but not for multiple backends

Jennifer,

I'm afraid you'll have to do better than that.

I asked for your code. Not a link (that I've already posted anyway) to a thread which contains multiple posts which include code.

I also asked for the .Connect information you start with, and an explanation of what you want them to be changed to.

If there's anything here you don't understand then please say so. Otherwise I would expect answers before I spend more time trying to help you.

[NeoPa]

I've now seen your more recent post and am going through it. It looks more helpful & I will post a response shortly.

[NeoPa]

That's much clearer and is something we can certainly work with.

I have some more questions though (1 a repeat but some new ones as we have moved forward) :

1. What is the current .Connect string set to for each table (or some examples if there are many)?
2. What is the rationale behind the relink? Is there something we can work with intelligently? Or is it largely indeterminate?
3. Is there anything which indicates which of the 3 databases any table should be found in?
4. What are the names of the other 2 databases (One appears to be sdeTrails)?

[NeoPa]

For want of any immediate answers (That's not a problem. Immediate is not something to expect when dealing via a web site) you can try this template code. You'll need to put your own names in, but it should be close at least :

Sub relinkTables()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

    strConnect = "DRIVER=SQL Server;SERVER=OurServer;DATABASE=%D;" & _
                 "Trusted_Connection=Yes"
    On Error Resume Next
    Set db = CurrentDB
    For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                .Connect = Replace(strConnect, "%D", "sdeTrails")
                Call .RefreshLink
                If Err > 0 Then
                    .Connect = Replace(strConnect, "%D", "db2")
                    Call .RefreshLink
                    If Err > 0 Then
                        .Connect = Replace(strConnect, "%D", "db3")
                        Call .RefreshLink
                    End If
                End If
            End If
        End With
    Next

End Sub

NB. There is no value returned so a subroutine procedure is more appropriate than a function one - hence the change.

[sherryj2]

NeoPa:

1: here are three connection strings used in the multiple back-end scenario:

DRIVER=SQL Server;SERVER=O urServer;APP=Mi crosoft Data Access Components;WSID =OSC-SHERJ1-9794;DATABASE=V egetation;Netwo rk=DBMSSOCN;Tru sted_Connection =Yes

DRIVER=SQL Server;SERVER=O urServer;APP=Mi crosoft Data Access Components;WSID =OSC-SHERJ1-9794;DATABASE=M onitoring;Netwo rk=DBMSSOCN;Tru sted_Connection =Yes

DRIVER=SQL Server;SERVER=O urServer;APP=Mi crosoft Data Access Components;WSID =OSC-SHERJ1-9794;DATABASE=s deMonitoring;Ne twork=DBMSSOCN; Trusted_Connect ion=Yes

2. I want to force the refresh because my users often get the "ODBC call failed" message and most are savvy about refreshing the links in the linked table manager, but I would like it to be more seamless. Somehow it's always my fault when things don't work properly.

3. Do you mean is there anything in the name of the table that might be able to categorize it into one of the three databases?

4. other database names provided in connection strings above.

When I look in the MSysObjects table, I see the connections strings for each table. Is there a way to just use that info for the refresh?

Thank you for your patience/help.

Jennifer

[sherryj2]

Great. I will give that a try right now.

[sherryj2]

NeoPa:

Wow, that totally worked. Thanks very much for your input! I knew it was simple, but I had no idea how to do it.

[NeoPa]

1: Here are three connection strings used in the multiple back-end scenario:
2. I want to force the refresh because my users often get the "ODBC call failed" message and most are savvy about refreshing the links in the linked table manager, but I would like it to be more seamless. Somehow it's always my fault when things don't work properly.
3. Do you mean is there anything in the name of the table that might be able to categorize it into one of the three databases?
4. Other database names provided in connection strings above.

When I look in the MSysObjects table, I see the connections strings for each table. Is there a way to just use that info for the refresh?

1. Cool. Just what the doctor ordered :)
2. This looks to me like the connection string is not required to be changed at all. Simply a retry of the link. Please confirm.
3. Yes. Absolutely that is my question. Irrelevant if the response to #2 above is to confirm though.
4. Cool. I saw that thanks.

As to your final question, I see this as relating directly to #2. If you have no wish to change the details of the .Connect string, but simply to attempt a relink of what is already specified, then that can be accomplished most easily (of all the possible scenarios). Bear in mind I can test, therefore guarantee, nothing. However, you could try out the following simpler code if this describes your situation :

Sub relinkTables()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

    Set db = CurrentDB
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Then Call tdf.RefreshLink
    Next

End Sub

[NeoPa]

NeoPa:

Wow, that totally worked. Thanks very much for your input! I knew it was simple, but I had no idea how to do it.

No worries Jennifer. When you got the answers to the questions going it became a whole lot easier to work with.

Can you let me know how you get on with the code in the previous post (#13) please. I'm curious to see if that works for you.

[sherryj2]

Wow. That was much simpler even. Yes you are right that all I needed to do was refresh (relink) the link for each table. I had searched all over Google for a simple way (that I could understand) which is how I came across msquared's "insight" on this site.

It amazes me that I spent the better part of two days trying to find the answer to that. I suppose it's time I just learned how to write some code, no?

Thanks again.