CREATE TABLE with 'Required' set

[PearlGI]

Hi
I've created the coding required to create a new table, but I also need to set the 'Required' parameter to 'Yes' on some selected fields.

Is is possible to do this whilst creating the table with the 'CREATE TABLE...' statement or is there another way of programatically setting the 'Required' parameter?

 

[ajetrumpet]

personally, i would never create a table in vb. use the interface. yes, there is a way to do it, but why?

 

[PearlGI]

Why? Because this is part of a user interface which needs to create a temporary table to validate and error handle data import files containing potentially unknown fields.
It wouldn't be much of a user interface if the user had to manually use the Access table interface to create a table that the user doesn't even know is needed!

I've now researched some more and have found that it is not possible to do this with 'CREATE TABLE'.
I now know it can be done using TableDefs, but have tried the following code with no success.

Set rsTemp = db.OpenRecordset("ValnFields")
Set td = db.OpenRecordset("tmpImport")
Do Until rsTemp.EOF
    If rsTemp!Required = True Then
        td.Fields(rsTemp!Field).Required = True
    End If
    rsTemp.MoveNext
Loop

Spotted the error in the above code, the line starting 'Set td' was opening a recordset rather than a TableDef. Doh!

Basically this moves through a table called ValnFields and if the field is deemed as being required, the appropriate field in table tmpImport needs to have the Required parameter set to True.

Can anyone help?

 

[pbaldy]

Why is it not possible with CREATE TABLE? According to Help, NOT NULL can be used, which accomplishes your goal, does it not?

 

[Banana]

pbaldy is correct; NOT NULL is basically Access's Required property. Only one caveat, though- if you include an definition for default, Access will insert it automatically and not raise an error if the value is not changed.

 

[PearlGI]

Of course

Many thanks, had not made the connection between NOT NULL and Required

 

[DCrake]

Another issue related to Create Table is that if you create the table in the local front end you will subject it to increased bloating. IF you attempted this in a seperate mdb THEN linked the table this would reduce the bloating effect. There is no reason why the mdb cannot reside locally on the pc but if it is needed by others then this is another issue.

David