asked on

Creating Access Tables Via VBA

Hi

I am creating a table on the fly through VBA. Can anyone tell me how I would create the table with the requireed field set to NO on all records. My current code is;

With tbl1
.Name = GapTitle & " - " & Date
For j = 1 To NoOfControls
FieldText = Me("Text" & j)
.Columns.Append FieldText, adVarWChar, 30
Next
End With

Thanks

ASKER CERTIFIED SOLUTION

peter57r

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

andyb7901

ASKER

I have tried the following but with no luck;

With tbl1
.Name = GapTitle & " - " & Date
For j = 1 To NoOfControls
FieldText = Me("Text" & j)
.Columns.Append FieldText, adVarWChar, 100
Next
For j = 1 To NoOfControls
FieldText = Me("Text" & j)
.Columns(FieldText).Properties("Required").Value = False
Next
End With

andyb7901

ASKER

peter57r, your example does not have anything on the "Required" Field? If I do the following it works fine, but it needs the table to be created first?

Set db = CurrentDb()
Set tbl = db.TableDefs("tblDepartments")

Set fld = tbl.Fields("Organization")
tbl.Fields.Append fld
fld.Properties("Required").Value = False
fld.Properties("AllowZeroLength").Value = True

peter57r

peter57r, your example does not have anything on the "Required" Field

It certainly does. Under the heading 'required field'!!

andyb7901

ASKER

Do you mean this section;

'Required field.
With !Surname
Set .ParentCatalog = cat
.Properties("Nullable") = False 'Required.
.Properties("Jet OLEDB:Allow Zero Length") = False
End With

I have scanned the document for "Required" and this is what is baught back? If I have missed the section I am sorry, could you provide the section extraxt for me?

andyb7901

ASKER

I have looked through the document and can not get anything to work? Anyone have any advise?