Msysobjects table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can you edit this table, I just have read access to it. Or is there a way
to change with ADOX or VBA code the content inside that table.
At the end I just want to get access to the content of the ForeignName field
inside that system table.

Thanks a lot
 
You cannot (and should not) make changes to MSys tables.

What type of object's ForeignName field are you interested in?
 
Mike said:
How can you edit this table, I just have read access to it. Or is there a
way
to change with ADOX or VBA code the content inside that table.
At the end I just want to get access to the content of the ForeignName
field
inside that system table.

Thanks a lot
Click to expand...

to edit

application.Currentdb.TableDefs("LocalName").SourceTableName

you need to DELETE and RECREATE the tabldef.

Unfortunately, just changing the string value would only crash
the database engine, because there is a lot of meta data
stored in the binary fields of that record, and it needs to
be updated too.

(david)
 
I can read with application.Currentdb.TableDefs("LocalName").SourceTableName
but I cannot change or write another value with it.

I have tried this procedures too:

Public Sub test()
Dim cat1 As New ADOX.Catalog
Dim cmd1 As ADODB.Property
Dim tb As ADOX.Table
Dim tbName As String
Dim PrcName As String
Dim strSQL As String

cat1.ActiveConnection = CurrentProject.Connection

'Go throught the tables collection of the database
For Each tb In cat1.Tables
tbName = Trim(tb.Name)
If InStr(1, Left(tbName, 11), "SERTEC_VUE_", 1) > 0 Then
Set cmd1 = cat1.Tables(tbName).Properties.Item(4)
strSQL = cmd1.Value 'I get
"SERTECON.VUE_ADRESSE"
strSQL = "SERTECQC.VUE_ADRESSE" 'The place to change
the value with a function
cmd1.Value = strSQL 'an error is generated
End If
Next tb

Set cat1 = Nothing
Set cdm1 = Nothing

End Sub

But I get the error message: Multiple step OLE DB operation generated
errors. Check each OLE DB status value.

My problem is that I have mulitple copy of an oracle database (Virtual
database or oracle Schema of the main database) and that I want to use the
same interface and be able to change connection without having to tell every
time what are the primary key of every linked table in my access interface
manually.
At the end, if we cannot just change the ForeignName in MSysObjects and keep
all the other connection properties, His there a way to copy all the
information about the linked table and reconstruct all those link with the
little ForeignName change.

If you ever had an example.....

Thanks!!!
 
Mike said:
I can read with
application.Currentdb.TableDefs("LocalName").SourceTableName
but I cannot change or write another value with it.
Click to expand...


That's right.

To edit that value you need to DELETE and RECREATE the tabledef.

Unfortunately, just changing the string value would only crash
the database engine, because there is a lot of meta data
stored in the binary fields of that record, and it needs to
be updated too.

(david)
 
OK we delete delete and recreate those link.

How do you get the knowledge of the unique index from a linked table.
Because I want to automate the procedure.
So before deleting a linked table, I need to know what is the unique index
of the linked table that I am about to delete (to be able to recreate it
automatically). If I don't create a unique index on my linked table, I won't
be able to update the record from access. And I don't want to use the linked
table manager inside access, because I will have to answer about 200
questions on what is the unique key for that linked table.

So I was able to get all the field name of a unique index on a local Access
table, but I can't find the way to get the same info from a linked table. I
use ADOX, What is the problem, where do they hide that unique index for a
linked table. If I get that info, I will be able to recreate all those linked
table afterward easely.

Thanks agaij and have a good day!! BYE!!!
 
Back
Top