[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1691
  • 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)

thanks,
mac
0
MacRena
Asked:
MacRena
  • 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
    RS.MoveNext
Loop

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

End Function
0
 
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
Are you just talking about altering the linked paths of existing table definitions?
(i.e. changing the connect property?)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:
s1_tblSomeTable
s2_tblSomeTable
s3_tblSomeTable

Now, when you wish to use, say, server two, rename all s2_ tables to just the tablename:
tblSomeTable
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.

/gustav
0
 
GRayLCommented:
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
;DATABASE=D:\Data\AcData\rtw.mdb

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

/gustav
0
 
GRayLCommented:
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.
0
 
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.

/gustav
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
 
MacRenaAuthor Commented:
thanks to all - i'll find another way.
mac
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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