MS Access create table default values

[zyphon]

I have used the following query :
CREATE TABLE messages([ID]autoincrement,[user] text,[message] memo,[topicID] integer,[msgdate] date,PRIMARY KEY ([ID]));
which works fine. I need to find default values for the fields.
Thanks.

 

[psemianonymous]

Assuming you're using Access tables, you can get the DAO TableDef object which has a collection of Fields which have the property "DefaultValue".

i.e.

    CurrentDB.TableDefs("Table1").Fields("Field1").DefaultValue
    'or
    CurrentDB.TableDefs!Table1!Field1.DefaultValue

I've never needed to change this in code, so I don't know for sure, but I believe you will have to re-append the field to the fields colelction or run some sort of "Update" to save the changes. Or maybe not.



Alternately, consider *NOT* deleting the table in the first place so you don't have to re-set the default value every time. Instead delete the table *contents*, and APPEND those contents. Easier in the long run. And you get to keep relationships!