MSysObjects

[Vsn]

Hi all,

I thought to have a good idea to easily change the data location for a
database from which I had to change the linked tables (file).

UPDATE MSysObjects SET MSysObjects.[Database] =
\\Fmedxb\Data\Operations\D_AdminFinance\OperationsDB\OperationsDBData\PersonnelOperationsData.mdbWHERE
(((MSysObjects.Database) Is Not Null))WITH OWNERACCESS OPTION;



This however does not work since i do not have the right permission to edit
this table. Could someone tell me how this could be done. I am the creator
of the DB and so assume I have all permissions, unless Microsoft does not
want me to change items in the MSys tables.


Thx,

Ludovic

 

[Allen Browne]

I don't think you will be able to write to MSysObjects.

The usual way to do this is to loop through the TableDefs collection.
Skip system tables and any deleted tables (name starts with "~".)
For any that have a value in their Connect property, assign the newvalue,
and RefreshLink.

 

[missinglinq via AccessMonster.com]

Allen is correct, MSysObjects are read only! It's not nice fooling with
Mother Nature!

 

[Vsn]

Allan, thx.

Can you explane your 'TableDefs' collection, is that a table or so......
Would I be able to edit the named collection with a update query? I do have
to handle quit some table links which have been moved and will be moved in
the future.

Thx,
Ludovic

I don't think you will be able to write to MSysObjects.

The usual way to do this is to loop through the TableDefs collection.
Skip system tables and any deleted tables (name starts with "~".)
For any that have a value in their Connect property, assign the newvalue,
and RefreshLink.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I thought to have a good idea to easily change the data location for a
database from which I had to change the linked tables (file).

UPDATE MSysObjects SET MSysObjects.[Database] =
\\Fmedxb\Data\Operations\D_AdminFinance\OperationsDB\OperationsDBData\PersonnelOperationsData.mdbWHERE
(((MSysObjects.Database) Is Not Null))WITH OWNERACCESS OPTION;

This however does not work since i do not have the right permission to
edit this table. Could someone tell me how this could be done. I am the
creator of the DB and so assume I have all permissions, unless Microsoft
does not want me to change items in the MSys tables.

Thx,

Ludovic

 

[Allen Browne]

Here's an example of how to loop through the TableDefs and set the Connect
property of the attached tables:
http://allenbrowne.com/ser-13.html
That example assumes they are attached to another MDB in the same folder,
but it should give you the idea.

Here's another example of using the TableDef to get list details of the
fields of a table and their properties:
http://allenbrowne.com/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allan, thx.

Can you explane your 'TableDefs' collection, is that a table or so......
Would I be able to edit the named collection with a update query? I do
have to handle quit some table links which have been moved and will be
moved in the future.

Thx,
Ludovic

I don't think you will be able to write to MSysObjects.

The usual way to do this is to loop through the TableDefs collection.
Skip system tables and any deleted tables (name starts with "~".)
For any that have a value in their Connect property, assign the newvalue,
and RefreshLink.

Hi all,

I thought to have a good idea to easily change the data location for a
database from which I had to change the linked tables (file).

UPDATE MSysObjects SET MSysObjects.[Database] =
\\Fmedxb\Data\Operations\D_AdminFinance\OperationsDB\OperationsDBData\PersonnelOperationsData.mdbWHERE
(((MSysObjects.Database) Is Not Null))WITH OWNERACCESS OPTION;

This however does not work since i do not have the right permission to
edit this table. Could someone tell me how this could be done. I am the
creator of the DB and so assume I have all permissions, unless Microsoft
does not want me to change items in the MSys tables.

Thx,

Ludovic

 

[Vsn]

Thx, Allen this is very helpful.

Ludovic

Here's an example of how to loop through the TableDefs and set the Connect
property of the attached tables:
http://allenbrowne.com/ser-13.html
That example assumes they are attached to another MDB in the same folder,
but it should give you the idea.

Here's another example of using the TableDef to get list details of the
fields of a table and their properties:
http://allenbrowne.com/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allan, thx.

Can you explane your 'TableDefs' collection, is that a table or so......
Would I be able to edit the named collection with a update query? I do
have to handle quit some table links which have been moved and will be
moved in the future.

Thx,
Ludovic

I don't think you will be able to write to MSysObjects.

The usual way to do this is to loop through the TableDefs collection.
Skip system tables and any deleted tables (name starts with "~".)
For any that have a value in their Connect property, assign the
newvalue, and RefreshLink.

"Vsn" <vsn at hotmail> wrote in message
Hi all,

I thought to have a good idea to easily change the data location for a
database from which I had to change the linked tables (file).

UPDATE MSysObjects SET MSysObjects.[Database] =
\\Fmedxb\Data\Operations\D_AdminFinance\OperationsDB\OperationsDBData\PersonnelOperationsData.mdbWHERE
(((MSysObjects.Database) Is Not Null))WITH OWNERACCESS OPTION;

This however does not work since i do not have the right permission to
edit this table. Could someone tell me how this could be done. I am the
creator of the DB and so assume I have all permissions, unless
Microsoft does not want me to change items in the MSys tables.

Thx,

Ludovic