Status: Solved
Priority: Medium
Security: Public
Views: 1222
Last Modified: 2013-11-19

Ms ACCESS, VBA Table Re-link, navigation pane formatting lost

Hi Dear All,

I am using the following code to update ODBC connections for my existing tables.
See code at bottom.

However i grouped on the navigation pane all my tables, queries, forms on related groups .For example : ClientsTable , ClientEditForm is grouped under CLIENT

When i relink all tables with VBA code, all this formatting is lost , and relinked tables goes to Unassigned objects,

Is there anyway to prevent this ?

Sub ReLinkAllTablesFromMsSql()

'Returns all linked tables
    
    Dim tdf As TableDef, db As Database
    Set db = CurrentDb
    db.TableDefs.Refresh
    
    For Each tdf In db.TableDefs
        
        With tdf
            
            If Len(.Connect) > 0 Then
              

                             If InStr(.Connect, "SQL Server") > 0 Then
                             '.Attributes = DB_ATTACHSAVEPWD
                                NewConnectionStringDatabase = Null
                                NewConnectionStringDatabase = Left(Right(.Connect, Len(.Connect) - InStr(.Connect, "DATABASE=") - 8), Len(Right(.Connect, Len(.Connect) - InStr(.Connect, "DATABASE=") - 8)) - 1)
                               NewConnectionStringDatabase = "ODBC;DRIVER=SQL Server;SERVER=SERVERNAMEHERE;Database=" & NewConnectionStringDatabase & ";Trusted_Connection=True;APP=2007 Microsoft Office system;"
                               .Connect = NewConnectionStringDatabase
                               .RefreshLink
                             End If

             End If
        
        End With
    Next
    
    
    
    Set tdf = Nothing
    Set db = Nothing
End Sub

Open in new window

vedointernet

Asked: 2013-11-14

Advertise Here

Microsoft Access

1 Solution

LVL 61

mbizupCommented: 2013-11-14

I've seen very little information about this, so I would guess that it cannot be *easily* done. I think you would have to rebuild those groups through code.

However, you might want to take a look at the sample posted here:
http://www.utteraccess.com/forum/Access-2007-NavigationPan-t1953151.html

I can't personally vouch for it, but the author claims that the sample does what you are asking for.

vedointernetAuthor Commented: 2013-11-14

Dear All,

I found the solution while googling it, so i wanted to share :

use this code BEFORE RELINKING TABLES WITH VBA : ( it will export the nav pane structure to an xlm file)

 application.ExportNavigationPane "d:\NavigationPane.xml"

Open in new window

use this code AFTER RELINKING TABLES WITH VBA :

( it will import the nav pane structure from the xlm file)

 application.ImportNavigationPane "d:\NavigationPane.xml",false

Open in new window

LVL 61

mbizupCommented: 2013-11-14

Good stuff -- thanks for sharing!

vedointernetAuthor Commented: 2013-11-19

the is the exact answer

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Ms ACCESS, VBA Table Re-link, navigation pane formatting lost

Join & Write a Comment Already a member? Login.

Featured Post