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;DRI
' front-end program for the "DCF" company
Description=MyData_DEF;DRI
' front-end program for the "GHI" company
Description=MyData_GHIF;DR
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;P
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
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;DRI
' front-end program for the "DCF" company
Description=MyData_DEF;DRI
' front-end program for the "GHI" company
Description=MyData_GHIF;DR
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;P
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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
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
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.
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
/gustav
Glad to be of assistance. May all your days get brighter and brighter.
ASKER
thanks to all - i'll find another way.
mac
mac
ASKER
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.