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)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?)
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.