The following code produces the table I want, with the exception that it somewhere, somehow, forces all columns to be "Required", even though I've never explicitly told ADO to do so. So how do I prevent the "Required" property in ADOX from affixing itself to every column in my table??????


Any HELP would be appreciated!!!!



Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim idxPrimary As New ADOX.Index

Dim strField As String
Dim lngYear As Long

Dim lngYearFirstDescription As Long
Dim lngYearLastDescription As Long

Const accVolume As String = "Volume_"
'************************************************* ***************************************
Set cnn = Access.Application.CurrentProject.Connection
Set cat.ActiveConnection = cnn
'************************************************* ****************************************
lngYearFirstDescription = GetYearFromYearIdentifier(lngYearFirst)
lngYearLastDescription = GetYearFromYearIdentifier(lngYearLast)
'************************************************* ****************************************
With tbl
    .Name = accGenerateVehicleBuildRecordsource
    '************************************************* ************************************
    .Columns.Append "Vehicle_Build_ID", adInteger
    .Columns.Append "Download_Date", adDate
    .Columns.Append "Platform_ID", adInteger
    .Columns.Append "Name_Plate_ID", adInteger
    .Columns.Append "Production_Start", adDate
    .Columns.Append "Record_Number", adInteger
    '************************************************* ************************************
    For lngYear = lngYearFirstDescription To lngYearLastDescription
        strField = Strings.Trim(accVolume & lngYear)

        .Columns.Append strField, adInteger
    Next lngYear
    '************************************************* ************************************
    With idxPrimary
        .Columns.Append "Vehicle_Build_ID"
        .Columns.Append "Download_Date"

        .Name = "Primary_Key"

        .Unique = True
        .PrimaryKey = True
    End With
    '************************************************* ************************************
    .Indexes.Append idxPrimary
End With
'************************************************* ****************************************
With cat.Tables
    .Append tbl
    .Refresh
End With
'************************************************* ****************************************
cnn.Close
'************************************************* ****************************************
Set tbl = Nothing
Set idxPrimary = Nothing
Set cat = Nothing
Set cnn = Nothing

Hello,

You need to expose the Jet-specific column properties on your new column which is done through the column's ParentCatalog property. Setting a column's "Jet OLEDB:Allow Zero Length" or "Nullable" properties to True will make the field non-required.

You can see all the other jet column property settings byy creating a new column, the runnung:

The Jet-specific properties exposed are:

Autoincrement
Default
Description
Nullable
Fixed Length
Seed
Increment
Jet OLEDB:Column Validation Text
Jet OLEDB:Column Validation Rule
Jet OLEDB:IISAM Not Last Column
Jet OLEDB:AutoGenerate
Jet OLEDB:One BLOB per Page
Jet OLEDB:Compressed UNICODE Strings
Jet OLEDB:Allow Zero Length
Jet OLEDB:Hyperlink

For example, to create an autoincrement field use:

HTH,

Bob

Just as an aside, if you use SQL or DAO, both of which are considerably simpler than using ADOX (and let you use Jet data types), the required property defaults to NO:

Sub CreateTable1()

    Dim dbs As Database
    Dim strSQL As String

    Set dbs = CurrentDb()

    strSQL = strSQL & "CREATE TABLE TestTable2("
    strSQL = strSQL & "PersonID Autoincrement, "
    strSQL = strSQL & "FirstName Text(50), "
    strSQL = strSQL & "LastName Text(50), "
    strSQL = strSQL & "HireDate DateTime, "
    strSQL = strSQL & "HireRate Currency, "
    strSQL = strSQL & "SupervisorID Number, "
    strSQL = strSQL & "Terminated YesNo);"

    dbs.Execute strSQL
    dbs.Close

End Sub

Sub CreateTable2()
    Dim dbs As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim prp As Property

    Set dbs = CurrentDb()
    Set tdf = dbs.CreateTableDef("TestTable3")

    With tdf
        .Fields.Append .CreateField("PersonID", dbLong)
        .Fields("PersonID").Attributes = dbAutoIncrField
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("HireDate", dbDate)
        .Fields.Append .CreateField("HireRate", dbCurrency)
        .Fields.Append .CreateField("SupervisorID", dbInteger)
        .Fields.Append .CreateField("Terminated", dbBoolean)
    End With

    dbs.TableDefs.Append tdf


End Sub

Bob

Wouldn't the string:

strSQL = strSQL & "CREATE TABLE TestTable2("

yield a null string which would propagate throughout the string.

replace it with

strSQL = "CREATE TABLE TestTable2("


Rand

  No. Concatenating an empty string and a string leaves you with the value of the string.

Dim str As String
str = "" & "" & "" & ""
str = str & "Something"
Debug.Print str --> "Something"


But I agree your version is more intuitive. Code works fine either way, though.

Better yet:

strSQL = "CREATE TABLE TestTable2(" & _
               "PersonID Autoincrement, FirstName Text(50), " & _
               "LastName Text(50), HireDate DateTime, " & _
               "HireRate Currency, SupervisorID Number," & _
               "Terminated YesNo);"

More efficient, less readable. So many ways, so little time.

Bob

if

strSQL = strSQL & "CREATE TABLE TestTable2("

works ok (without generating a null string)

then I've been working with SQL Server too long!!!

:)


Rand

It's all just one big programming blur, isn't it...;)

Hey people - got a big computing project due in tuesday. any help would be much appreciated! ;)

I haven't found the code above to work for making a column in access nullable! originally my field type was adcurrency and I changed it to advarwchar, but same error occurs!
I'm using Jet 4.0:



Private Sub cmdAddToday_Click()
Dim DBtable As Table Dim DBcatalogue As New ADOX.Catalog
Dim Today As String
Dim ColumnCount As Integer
Dim NewColumnIndex As Integer
Dim Counter As Integer
Dim blnCheck As Boolean
Dim strCurrent As String

strCurrent = Me.adoSecurities.Recordset.Fields("SEDOLNumber")

Today = Format(Date, ShortDate)
DBcatalogue.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pmp.mdb"
Me.dgdPrices.ReBind
Me.dgdPrices.Refresh

Set DBtable = DBcatalogue.Tables("tblsecurity")
ColumnCount = DBtable.Columns.Count

For Counter = 0 To (ColumnCount - 1)
If DBtable.Columns(Counter).Name = Today Then
blnCheck = True
Exit For
Else
blnCheck = False
End If
Next Counter

If blnCheck = True Then
MsgBox "A field already exists for today's date", vbExclamation, "Error"
Me.cmdAddToday.Enabled = False
Else
DBtable.Columns.Append (Today), adVarWChar, 6
With DBtable.Columns.Item(Today)
Set .ParentCatalog = DBcatalogue
                .Properties("Jet OLEDB:Allow Zero Length") = True
                .Properties("Nullable") = True
         End With

'N.B. Microsoft Access ODBC driver doesn't provide programmatic means of determining nullability property of column in table (http://support.microsoft.com/default...b;en-us;185823) Hence, field added has default property set to 'required' thus preventing adding of new records



The problem (according to MS) has been fixed with Jet 4.0, but this is not the case.

Can anyone help?