Modify linked tables properties

[Gus]

Hi everyone.

How can I change properties on a linked table
programmaticaly like adding new columns or changing
column properties ?

Thanks
Gus

 

[Roger Carlson]

With DAO, you can change modify it programmatically:

'**************************************************
Sub exaModifyLinkedTable()
'DAO DDL example
'demonstrates modifying a table, fields, properties
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

' Create the table and a field
Set db = OpenDatabase("c:\path\to\MyDatabase.mdb")
Set tdf = db.TableDefs("MyTable")
Set fld = tdf.CreateField("NewField", dbText, 50)

' Set field properties
fld.AllowZeroLength = True
fld.DefaultValue = "Unknown"
fld.Required = True
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"

' Append field to Fields collection
tdf.Fields.Append fld

End Sub
'**************************************************

You can also modify a table using SQL:

'**************************************************
Sub exaModifyLinkedTableSQL()
'SQL DDL example
'Alter Table example adds several fields to an existing table
Dim db As DAO.Database

' Create the table and a field
Set db = OpenDatabase("c:\MyDatabase.mdb")

db.Execute "alter table MyTable add Newfield1 text(10);"
db.Execute "alter table tblResults add Newfield2 text(2);"
db.Execute "alter table tblResults add Newfield3 date;"

End Sub
'**************************************************

However you cannot set some of the properties with SQL.

 

[Douglas J. Steele]

While Roger's given you some very good code, I just wanted to make sure you
knew that you cannot make changes to linked tables. You must make the
changes in the back-end database that contains the tables, not in the
front-end.

 

[Gus]

Thank you for your reply.

I will try right now

Gus

 

[Roger Carlson]

Thanks to Doug for pointing out that the code is not working through the
link I should have been more explicit in the explaination.

The code I gave DOES NOT change the table through the link. Instead, it
opens the other database programmatically and makes the change. This code
would not work if I used:

Set db = CurrentDb