Table structure modification

[John]

Hi

How can I;

1. Rename a table field via code?

2. Set a property of a table field via code?

Many Thanks

Regards

 

[Allen Browne]

There's no one consistent way to do this kind of thing.

Several field properties can be changed programmatically only though DAO. If
the property has never been used, you may need to CreateProperty first. The
SetPropertyDAO() function on this page shows how to set the property and
created it if necessary:
http://allenbrowne.com/func-DAO.html#SetPropertyDAO

There are some properties that can't be changed after the field has been
created. For example DAO can't change the size of a field, though it can be
done by executing a DDL query statement. The ModifyFieldDDL() function here
illustrates how:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

To rename a field, you may need to add a new field, set its properties, copy
the data over, and then delete the old field.

 

[John]

Hi Allen

Many thanks.

I have borrowed code from your site and modified as below;

Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If (tdf.Name <> "Switchboard Items") And (tdf.Connect = "") Then
For Each fld In tdf.Fields
If fld.Properties("AllowZeroLength") = False Then
fld.Properties("AllowZeroLength") = True
End If
Next
End If
End If
Next

I am getting the invalid operation error on line
fld.Properties("AllowZeroLength") = True. What is the problem? I'm setting
this property to true as I need to upsize access db to sql server.

Thanks

Regards

 

[Peter Hibbs]

If the tables are in a back-end file (which they should be) then try
this :-

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

HTH

Peter Hibbs.

 

[Allen Browne]

I just tried using the SetPropertyDAO() function, and it worked.

Example:

Call
SetPropertyDAO(CurrentDb.TableDefs("MyTable").Fields("MyField"),"AllowZeroLength",dbBoolean,
False)

 

[John Spencer]

Well, I believe some fields don't have an AllowZeroLength property. For
instance, dates, boolean, ole objects, and number fields do not have the property.

Also, why are you changing the field property. SQL Server handles nulls with
no problems except in rare circumstances involving nulls in foreign key fields
if you are using the upsizing tool to create the structure (including
setting up the relations). If you use triggers, the triggers get set up in
such a manner as to prohibit nulls in the foreign key when you are creating
records.

(That may have changed as I have not used the upsizing tool in a while).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County