Setting DefaultValue property with VBA

[JonWayn]

I have a procedure that takes SourceTableName, and DestTablename arguments
and creates a table matching the field structure of the table represented by
arg1. I use ALTER TABLE to add some standard fields to the destination table
if those fields didnt exist in the source table. After adding those fields, I
attempt to set the DefaultValue property of the new fields. I get the "Item
not found in collection" error and hovering over the elements of the
offending statement, I see that the offending object is the new field. If I
drag the execution mark up a few lines to the ALTER TABLE statement and
reexecute it, Access complains that the field does in fact exist. Is this a
bug in Access 2003, or am I doing something wrong here.

Also, if I had to set DefaultValues for a text field and a date field, which
has a formula, how do I code that with VBA? the Date field should have as a
DefaultValue, the following formula:
=IIf(Format(Date(),"h")>8,Date()+1,Date())

The text field has any standard string , eg. "Unk". What are the rules
governing the quoting of that string?

 

[Allen Browne]

Access DDL is very limited.

It is possible to specify a simple default value in a CREATE TABLE
statement, but only when executing under ADO (not DAO.) I'm not sure you can
get an ALTER TABLE to do that: most of the field properties cannot be set
via DDL query statments at all.

You can set those properties using DAO code. Many of the properties don't
exist until you create them, so you need a routine that discovers whether
the property exists, creates it if necessary, and assigns the desired value.
Grab the SetPropertyDAO() function from here:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

Actually, I did not follow what the default value was supposed to do. Date()
contains no time component, so Format(Date(), "h") will always return the
string "0", which you then compare to the numeric value 8, which probably
gets Access to typecast the first argument into a number, so it always
returns False, so the plus 1 never works?

 

[JonWayn]

I really like the functions you posted. However, they dont address my
problems. Also, you are absolutely right about Format(Date(), "h"); it should
have been Format(Time(), "h") instead. Thanks for the pointer.

Now, as stated in the original post, the object Access complains about is
the field object, not the field property. In fact, in that routine I created
3 additional fields to the ones in the source table; 2 text fields and 1
DateTime. The first text field's DefaultValue was set to the constant, 'Unk'
(objFld.DefaultValue = 'UNK'). That worked fine. The second text field's
default was set to the value of some variable; (objFld.DefaultValue = '" &
SomeVar & "'") (I know you cant tell what quotes I used there but its Single
quotes). Now that property doesnt get set. When the table is opened in Design
view its empty. That is also the case with the DateTime field. Warnings were
turned off so I guess both failed statements threw errors silently. I
temporarily turned warnings back on the know what the error was.

So in closing; it doesnt seem that my problem has to do with properties that
dont exist. Access just doesnt seem to know the field is there when I try to
set the property

 

[Allen Browne]

Not sure about the single quotes, but if you set the Default Value of a Text
field to a zero-length string, i.e.:
""
in the interface, or in code:
objField.DefaultValue = """"
This does not display anything when you view the field, but does insert the
zero-length string (as distinct from Null) as the user types new records.
This assumes the field's AllowZeroLength property is set to Yes, which may
not be a valid assumption, as the property is not set consistently across
different versions of Access, or methods of creating field, or of inserting
records.

Internally, date/time fields are stored as a special kind of floating point
number, where the integer part represents the date and the fraction is the
part of a day (e.g. 0.5 = noon, .25 = 6am.) Whatever default value you try
to set must actually resolve to something Access can recognise as a date and
resolve as a floating point date value.

This example shows how to set the DefaultValue of field MyDate in MyTable:
CurrentDb.TableDefs("MyTable").Fields("MyDate").DefaultValue = "=Date()"

 

[Jamie Collins]

Access DDL is very limited.

There is no such language. You probably meant Jet SQL DDL...

It is possible to specify a simple default value in a CREATE TABLE
statement, but only when executing under ADO (not DAO.)

You can also put the Access UI into ANSI-92 Query Mode to execute this
syntax natively.

I'm not sure you can
get an ALTER TABLE to do that:

Yes, a DEFAULT can be created/altered/dropped using an ALTER TABLE
statement.

most of the field properties cannot be set
via DDL query statments at all.

You probably think that way because you are confusing Access for Jet
(minor point: a query is SQL DML, not SQL DDL). Most of the features
of Jet that are also features of SQL-92 are accessible via Jet SQL DDL
(many of which are accessible only via Jet SQL DDL e.g. CHECK
constraints). Why would you expect to be able to use SQL DDL to alter
settings in a database management studio or a forms based RAD
development environment?

Jamie.

--