edit the  MSysObjects  table

Posted on 2006-04-26
Last Modified: 2008-02-26
hello experts,
i have a front-end program in Access 2000 that i propogate to 3 different MDE's (one for each of 3 SQL back-end databases).

to do this, i link the ODBC tables (through the DNS) to the SQL databases, then delete the linked tables to my Test Database, and rename (take out the "dbo.") the new tables to the Companies' databases.

this takes about 5 minutes each..

what i want to do is to (in code) re-write the values in the [Connect] field of each of the 3 front-end MDE's [MSysObjects] table and put in the correct connection string for the individual SQL databases.

here's an example of the [Connect] field's 3 values:

' front-end program for the "ABC" company
Description=MyData_ABC;DRIVER=SQL Server;SERVER=XYZ;UID=sa;PWD=;APP=Microsoft® Access;WSID=ANTEC4;DATABASE=MyData_ABC

' front-end program for the "DCF" company
Description=MyData_DEF;DRIVER=SQL Server;SERVER=XYZ;UID=sa;PWD=;APP=Microsoft® Access;WSID=ANTEC4;DATABASE=MyData_DEF

' front-end program for the "GHI" company
Description=MyData_GHIF;DRIVER=SQL Server;SERVER=XYZ2;UID=sa;PWD=;APP=Microsoft® Access;WSID=ANTEC4;DATABASE=MyData_GHI

so, as you can see, all i have to do is re-write the connection value in each record that has a link to this database.

Dim strSQL As String
Dim strCoInitials As String

' (loop 3 times, setting strCoInitials to the initials of the 3 companies)
strSQL = "Description=MyData_" & strCoInitials & ";DRIVER=SQL Server;SERVER=XYZ;UID=sa;PWD=;APP=Microsoft® Access;WSID=ANTEC4;DATABASE=MyData_" & strCoInitials

the problem is that the table data is read-only.

how can i edit thiese values.  can i open it in binary (naaa, not worth it).

what do you think (i mean, besided that it's risky editing that data, and that Microsoft may change it.   i'm a fool who rushes in and God protects us fools)

Question by:MacRena
    LVL 38

    Accepted Solution

    There is no way to directly edit the MSysObects table.

    But you can link to both and rename them as appropriate very easily.

    Public Function Rename_DEF_Tables()

    Dim DB As dao.Database
    Dim RS As dao.Recordset
    Dim SQL As String

    'The SQL query to open the recordset
    SQL = "SELECT Name " & _
        "FROM MSysObjects " & _
        "WHERE Connect Like '*Description=MyData_DEF*'"

    Set DB = CurrentDb()                'Use the current database
    Set RS = DB.OpenRecordset(SQL)      'actually open the recordset

    Do Until RS.EOF
        DoCmd.Rename "DEF_" & Mid(RS!NAME, 5, 100), acTable, RS!NAME

    Set RS = Nothing        'Close out the recordset and free the memory space.
    Set DB = Nothing

    End Function
    LVL 4

    Author Comment

    jimpen ,
    i don't want to remane the tables unless i'm actually manually linking to the new database.
    i want to skip that step and simply edit the Connect property of the table (thereby eliminating the need to delete and relink).

    what you said is >>"There is no way to directly edit the MSysObects table."<< and that's the sad answer.
    i have been studying this on the net and have only found that answer.

    so maybe i can't do this.  i'll keep looking, but thanks for the comment.
    LVL 44

    Assisted Solution

    by:Leigh Purvis
    Are you just talking about altering the linked paths of existing table definitions?
    (i.e. changing the connect property?)
    LVL 48

    Assisted Solution

    by:Gustav Brock
    > I want to skip that step and simply edit the Connect property of the table
    > (thereby eliminating the need to delete and relink).

    That's not possible.

    You have two options. One is the traditional method to write code to perform the relinking, optionally with linked (local) table names of your choice.

    The other, which I certainly would recommend in your case as you already do rename the linked table names, is to link to each of the three servers one by one. For each server, (re)name your local table names to identify which server you connect to, like s1_tblSomeTable.
    Done that, you have three sets of linked tables, like:

    Now, when you wish to use, say, server two, rename all s2_ tables to just the tablename:
    which is the name you presently use in your app.
    Change server to, say, server one by renaming the table names in use back to s2_tblSomeTable, then rename the set of s1_ tables as to tblSomeTable etc.

    This second method is proven and has the additional advantage that it is extremely fast.

    LVL 44

    Assisted Solution

    What don't I understand about this problem?  In an Access front end, with a bunch of linked tables, you have a table definition for each.  Stored within that definition is a connect string - available via code to alter to anything you want - or so I thought!  In the Immediate pane:

    set mydb=currentdb
    set mytd = mydb.tabledefs("rtwpics")

    When you create the link thru ODBC, the linked table still has a tabledef and is accessible thru code?
    LVL 48

    Expert Comment

    by:Gustav Brock
    > What don't I understand about this problem?  

    That after adjusting the Connect property you have to do a Relink of the tabledef.
    Also, that's slow. It's way faster to have sets of linked tables you just rename.

    LVL 44

    Expert Comment

    Thanks, gustav, I get little glimmers every once in a while.  At home here I don't connect to anything so I can't examine.
    LVL 48

    Expert Comment

    by:Gustav Brock
    You can just attach to another Access database. Same stuff as attaching tables in an SQL Server but, of course, the connection strings look different.

    LVL 38

    Expert Comment

    by:Jim P.
    Glad to be of assistance. May all your days get brighter and brighter.
    LVL 4

    Author Comment

    thanks to all - i'll find another way.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now