Solved

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

Posted on 2013-11-14
4
1,114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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