edit the MSysObjects table
Posted on 2006-04-26
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)