Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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