Using info in MSysObjects to relink tables

Cosmos75

Cosmos75

Registered User.
Local time
Today, 14:19
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:
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
 
Thanks for the tip, will give it a shot!
 
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?
 
Thanks lagbolt,

I'm sorry my English is bad. When executing this code, my tables links delete.
 
That is correct. The 'Sub UnLinkMe()' deletes linked tables, or local tables too, so be careful!
To create a link, use 'Sub LinkMeUp()'
 
4162.png


Compile error :(
 
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,
 
You're welcome. Congratulations to you for overcoming the language barrier and asking your question!
 
The code above provided by MarkK is a 95% solution. You just need to provide the correct .Connect string for your SQL Server.
 
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. :)
 
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?
 
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.
 
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

K
Linking tables
Replies
6
Views
469
Pat Hartman
P
K
Ghost table in MSysObjects
Replies
5
Views
293
KitaYama
K
J
Help with using ADOX to Set TableDef connect property in foreign DB w/out actually connecting
Replies
4
Views
565
Pat Hartman
P
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
446
ricardocpa
R
A
MS ACCESS connect to SQL server BE: Authentication Prompt every time I attempt to
Replies
4
Views
295
GPGeorge
GPGeorge

Users who are viewing this thread

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