Solved

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

Posted on 2013-11-14
4
1,092 Views
Last Modified: 2013-11-19
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

EXAMPLE OF NAvigation pane groups

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

0
Comment
Question by:vedointernet
  • 2
  • 2
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39647633
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.
0
 

Accepted Solution

by:
vedointernet earned 0 total points
ID: 39647763
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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39647767
Good stuff -- thanks for sharing!
0
 

Author Closing Comment

by:vedointernet
ID: 39658816
the is the exact answer
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question