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
andyb7901Asked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
You will find the answer to this Q and many other code examples here:
http://allenbrowne.com/func-ADOX.html#CreateTableAdox
0
 
andyb7901Author Commented:
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
0
 
andyb7901Author Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

It certainly does.  Under the heading 'required field'!!
0
 
andyb7901Author Commented:
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?
0
 
andyb7901Author Commented:
I have looked through the document and can not get anything to work? Anyone have any advise?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.