Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
1 day, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Ms ACCESS, VBA Table Re-link, navigation pane formatting lost
Posted on 2013-11-14
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 ?
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
[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
Active 1 day ago
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.
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.
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)
use this code AFTER RELINKING TABLES WITH VBA :
( it will import the nav pane structure from the xlm file)
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"
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month1 day, 21 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.