Using info in MSysObjects to relink tables (1 Viewer)

Cosmos75

Cosmos75

Registered User.
Local time
Yesterday, 18:33
Joined
Apr 22, 2002
Messages
1,281
I am using this query to pull data from the MSysObjects table;
SELECT MSysObjects.Database, MSysObjects.Connect, MSysObjects.ForeignName, IIf(IsNull([Connect]),"",Right(Left([Connect],Len([Connect])-1),Len([Connect])-(InStr(1,[Connect],"PWD=")+4))) AS connectPW, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))
ORDER BY MSysObjects.Database, MSysObjects.ForeignName;

MSysObjects.Database = File Name & Full File Path
MSysObjects.ForeignName = Linked Table Names (Type = 6)
connectPW = Password (if applicable)

Is there anything that I am overlooking with using the results of this query to relink tables? The one limitation that I can think of is that I will only be able to relink tables that have already been linked. I can use this to relink tables where the back-end folder/file has been moved or renamed. But then again, I am not 100% sure how the MSysObjects table is populated though it does seem to have everything I need.

I had thought about having 2 tables - one to store all the back-end file info (File Name, Path, Password) and another to store the names of all the linked tables. As I was poking around in the MSysObjects table I found all that stuff was already there for the tables that are already linked in the Front-End.
 
Last edited:
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
I solve this using a table in the front end called something like LinkedTables. Open a recordset on this table and traverse it. For each table name in there, delete the existing links using something like...

Code: 
Public Sub UnLinkMe(strTable as string)
[COLOR=Green]   'this sub deletes a table def.  For linked tables it deletes the link only![/COLOR]
   CurrentDb.TableDefs.Delete strTable
End Sub

Then, get a path to the new back end, traverse your LinkedTables recordset again, and call this sub for each table...

Code: 
Public Sub LinkMeUp(strTable as string, strPath as string)
   Dim tdf as DAO.TableDef
[COLOR=Green]   'create the new tabledef[/COLOR]
   Set tdf = CurrentDb.CreateTableDef(strTable)
[COLOR=Green]   'connect it to the back end .mdb file on disk[/COLOR]
   tdf.Connect = ";DATABASE=" & strPath
[COLOR=Green]   'specify the table name in the back end .mdb[/COLOR]
   tdf.SourceTableName = strTable
[COLOR=Green]   'append the linked table locally[/COLOR]
   CurrentDb.TableDefs.Append tdf
End Sub
 
Cosmos75

Cosmos75

Registered User.
Local time
Yesterday, 18:33
Joined
Apr 22, 2002
Messages
1,281
Thanks for the tip, will give it a shot!
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
Hey there, welcome to the forum. This is what they call a zombie thread, because it returned from the dead.
Are you having trouble with that code?
 
A

alpeki99

New member
Local time
Today, 02:33
Joined
Dec 7, 2007
Messages
4
Thanks lagbolt,

I'm sorry my English is bad. When executing this code, my tables links delete.
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
That is correct. The 'Sub UnLinkMe()' deletes linked tables, or local tables too, so be careful!
To create a link, use 'Sub LinkMeUp()'
 
A

alpeki99

New member
Local time
Today, 02:33
Joined
Dec 7, 2007
Messages
4


Compile error :(
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
You don't need the parenthesis unless you are calling a function to return a value ...
Code: 
LinkMeUp "TableName", "c:\Path\To\Some\Database.mdb"
Cheers,
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
You're welcome. Congratulations to you for overcoming the language barrier and asking your question!
 
J

javier_g_r

New member
Local time
Today, 01:33
Joined
Nov 5, 2014
Messages
2
Anybody knows how can I relink tables linked from SQL Server?

:)
 
BigHappyDaddy

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Yesterday, 16:33
Joined
Aug 22, 2012
Messages
205
The code above provided by MarkK is a 95% solution. You just need to provide the correct .Connect string for your SQL Server.
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
Wow, dual zombie thread. The orig post was 2005. Woke from the dead in 2010. Now it's 2014 and this thread lives again! I've never seen anything like it!

Thanks to everyone who made it possible. :)
 
J

javier_g_r

New member
Local time
Today, 01:33
Joined
Nov 5, 2014
Messages
2
Trying to use the ODBC DSN like this:
"DSN=MyDSN;UID=User;PWD=pwd;APP=Microsoft Office 2010;DATABASE=WAREHOUSE"
I receive a run-time error 3170 "Could not find installable ISAM.

:(

Any idea?
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:33
Joined
Mar 17, 2004
Messages
8,181
What I would do first is link a table using the wizard. Then, open the MSysObjects table and look at how that table is linked, namely check the "Connect" and "Database" fields, and see how the wizard implements the connection. Use that format as your starting place.
 
G

GBalcom

Much to learn!
Local time
Yesterday, 16:33
Joined
Jun 7, 2012
Messages
459
OK, I'll resurrect this one more time....

Did you have any luck? I'm trying to re-establish the links to an SQL db. I want this to be a dsnless connection, as I'm hiding the username and password for the db from the users, and this will be deployed off site.

I did open up the mysobjects table, and look up the connection string, and I've tried adding the username and password to the end of it, but I'm still getting this error:

"Object Invalid or no longer set"

The code I'm using is a bit different, so I'm posting it here:

Code: 
Public Sub RefreshTables(strIn As String)
    '====================================================================
    ' Comments:  Used on frmSplash at startup
    ' State of Code: Under Development
    ' Params  :
    ' Returns :
    ' Created : 12/01/2014 08:19 AM GB
    ' Modified:
    '====================================================================
    
    'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd

'Define Variables
    Dim daoTableDef As DAO.TableDef
    Dim strLocalTableName As String
    Dim strConnection As String
    
    'the name of the local linked table to refresh
    strLocalTableName = "dbo_tblJob"
    
    'build connection string
    strConnection = _
        "Description=acsSQL;DRIVER=SQL Server Native Client 11.0;SERVER=184.168.xxx.xx;APP=Microsoft Office 2010;DATABASE=CabProgram;UID=MyUserID;PWD=MyPassword"
        
    
    Debug.Print strConnection
    'this code assumes that the linked table object has already been created and only needs to be refreshed
    Set daoTableDef = CurrentDb.TableDefs(strLocalTableName)
    daoTableDef.Connect = strConnection
    daoTableDef.RefreshLink
    
    'cleanup
    Set daoTableDef = Nothing
    
    



    'ErrorHandlerStart
EXIT_PROCEDURE:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description, vbCritical, "ModCore.RefreshTables"
    Resume EXIT_PROCEDURE
    'ErrorHandlerEnd

End Sub


Thanks!
 
You must log in or register to reply here.

Similar threads

J
Help with using ADOX to Set TableDef connect property in foreign DB w/out actually connecting
Replies
4
Views
215
Pat Hartman
P
N
Solved Mass Importing Txt Files
Replies
14
Views
414
ebs17
E
P
Relinker form
Replies
3
Views
233
JtrilloJ
J
J
Access FE - SQL Server BE - relink tables
Replies
13
Views
578
sonic8
S
J
Create DSN-less connection to SQL Server
2 3
Replies
48
Views
2,068
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom