• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1783
  • Last Modified:

edit the MSysObjects table

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)

  • 3
  • 2
  • 2
  • +2
4 Solutions
Jim P.Commented:
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
MacRenaAuthor Commented:
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.
Leigh PurvisDatabase DeveloperCommented:
Are you just talking about altering the linked paths of existing table definitions?
(i.e. changing the connect property?)
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Gustav BrockCIOCommented:
> 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.

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")
? mytd.properties!connect

When you create the link thru ODBC, the linked table still has a tabledef and is accessible thru code?
Gustav BrockCIOCommented:
> 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.

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.
Gustav BrockCIOCommented:
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.

Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
MacRenaAuthor Commented:
thanks to all - i'll find another way.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now