Change column’s properties values

How do I change all the column values (Nullable=True and Allow Zero Length=True) in a table except PersonalID column? When the script is executed I get the error "-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." This is the code only for one table.I need to create more tables so setting Nullable property of each column is not practical.Please Help!
Here is the code

Public Sub DBcreation()
Dim tbl As New Table
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim prp As ADOX.Property
Dim oCn As ADODB.Connection
Dim sConStr As String


'Set Connection string
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\mydbase.mdb" & ";" & _
"Jet OLEDB:Engine Type=4;"
' 1) Create the DataBase
cat.Create sConStr

' 2) Create Table name "PDetails"
tbl.Name = "PDetails"
' 3) Add Fields
With tbl.Columns
.Append "PersonalID", adInteger 'Number
.Append "GHName", adVarWChar, 50 'Text
.Append "FirstName", adVarWChar, 50 'Text
.Append "FHName", adVarWChar, 50 'Text
.Append "Surname", adVarWChar, 50 'Text
.Append "BirthDate", adDate
.Append "Gender", adVarWChar, 10 'Text
.Append "Address", adLongVarWChar 'Memo
.Append "Pincode", adInteger 'Number
.Append "MobileNo", adInteger 'Number
.Append "HomeNo", adInteger 'Number
.Append "MaritalStatus", adVarWChar, 10 'Text
.Append "Profession", adVarWChar, 50 'Text
.Append "BloodGroup", adVarWChar, 10 'Text
.Append "Photo", adVarWChar, 50 'Text

' 4) 'Set the field properties.


With !PersonaltID 'AutoNumber.
.ParentCatalog = cat
.Properties("Autoincrement") = True
.Properties("Description") = "Automatically " & _
"generated unique identifier for this record."
End With

With !BirthDate
Set .ParentCatalog = cat
.Properties("Jet OLEDB:Column Validation Rule") = _
"Is Null Or <=Date()"
.Properties("Jet OLEDB:Column Validation Text") = _
"Birth date cannot be future."
End With

End With
' 5) Save the Table to the DataBase
cat.Tables.Append tbl
' 6) Set Column Properties
For Each col In tbl.Columns
For Each prp In col.Properties
If col.Name <> "PersonalID" Then
If prp.Name = "Nullable" Then
prp.Value = True 'error generated
'-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
End If
End If
Next
Next

'Clean up

Set tbl = Nothing
Set cat = Nothing
set prp = Nothing
End Sub

Here's another approach...

Code:

---

Option Explicit

' Add a reference to Microsoft ActiveX Data Objects 2.x Library
' and Microsoft ADO Ext 2.x for DDL and Security

Private Sub Form_Load()
'=============================
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim adoConn As New ADODB.Connection
Dim strConString$
Dim sSQL$, strLine$

    If Dir(App.Path & "\NewDB.mdb") = "" Then
        sSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & App.Path & "\NewDB.mdb;" & _
              "Jet OLEDB:Engine Type=5"
        cat.Create sSQL
       
        tbl.Name = "Table1"
        tbl.Columns.Append "Field1", adInteger
        tbl.Keys.Append "PrimaryKey", adKeyPrimary, "Field1"
        tbl.Columns.Append "Date", adDBTimeStamp
        cat.Tables.Append tbl
       
        Set cat = Nothing
        Set tbl = Nothing
    End If
   
    strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                    & App.Path & "\NewDB.mdb;Persist Security Info=False"
    adoConn.Open strConString
    Dim x As Integer
    x = 1
    Open App.Path & "\test.txt" For Input As #1
        Do While Not EOF(1)
            Line Input #1, strLine
            If strLine <> "" Then
                sSQL = "Insert Into Table1 (Field1,[Date]) Values (" & _
                  CInt(strLine) & ",#" & _
                  CDate(Now) & "#)"
         
          '    sSQL = "Insert Into Table1 (Field1) Values  (" & Chr(34) & strLine & Chr(34) & ")"
                Debug.Print sSQL
                adoConn.Execute sSQL
            End If
            x = x + 1
        Loop
    Close #1
   
    adoConn.Close
    Set adoConn = Nothing

End Sub

---

after execution of cat.Tables.Append tbl I get the error message "-2147217859 - Type is invalid."Even after adding on error resume next the loop is infinite. I even changed "Jet OLEDB:Engine Type=5" to Type=4 but the error is same.

With my code?

yes

You've corrupted that table, as it must not have Table1 in it.

Unzip the NewDB.mdb, and put it in the program's folder.

Debug.Print:

Quote:

---

Insert Into Table1 (Field1,[Date]) Values (4,#8/7/2010 12:03:59 PM#)
Insert Into Table1 (Field1,[Date]) Values (5,#8/7/2010 12:03:59 PM#)
Insert Into Table1 (Field1,[Date]) Values (6,#8/7/2010 12:03:59 PM#)
Insert Into Table1 (Field1,[Date]) Values (7,#8/7/2010 12:03:59 PM#)
Insert Into Table1 (Field1,[Date]) Values (8,#8/7/2010 12:03:59 PM#)

---

(my code running under Windows 7 x64)