vedointernet
asked on
Ms ACCESS, VBA Table Re-link, navigation pane formatting lost
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good stuff -- thanks for sharing!
ASKER
the is the exact answer
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.