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.
COURSE of the MONTH
11 days, 22 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 today
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
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
Course of the Month11 days, 22 hours left to enroll
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.