?
Solved

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

Posted on 2013-11-14
4
Medium Priority
?
1,145 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 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