Modifying Linked Table Properties

[Chaplain Doug]

Access 2003. How may I modify a linked tables properties from the front end
of a database? I do not what to have to open the back end to make the
changes. Ultimately, either I need to know what options to set in the front
end so that linked tables may be modified (the structure that is) or I need
to know the VBA code I can use to modify a linked tables properties. I have
posted this question in another form elsewhere to no avail. Thank anyone who
can help.

 

[Scott McDaniel]

Access 2003. How may I modify a linked tables properties from the front end
of a database? I do not what to have to open the back end to make the
changes. Ultimately, either I need to know what options to set in the front
end so that linked tables may be modified (the structure that is) or I need
to know the VBA code I can use to modify a linked tables properties. I have
posted this question in another form elsewhere to no avail. Thank anyone who
can help.

You cannot directly edit the linked table in the FE, and you should not attempt to edit an in-use database (i.e. do all
your work when NO ONE is using the database). For that reason, it makes little sense to perform your edits in the
frontend using code, but it can be done.

You can use DAO to edit the table:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = DAO.OpenDatabase("C:\SomeFolder\db1.mdb")
Set tdf = dbs.TableDefs("Table1")

Set fld = tdf.CreateField("Somefield", dbText)
fld.AllowZeroLength = True
fld.Required = False
tdf.Fields.Append fld

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

You can also use SQL to perform some functions, but DAO provide access to more properties and such.

Note that you'll get an error if you try to perform this on an in-use table, or on a table that doesn't exist, or try to
add a field twice, etc etc ...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Peter Hibbs]

Doug,

Have a look at :-

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'

which does what you want.

HTH

Peter Hibbs.