Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 17th, 2006, 02:18 PM
Registered User
 
Join Date: Jan 2006
Location: Southfield, Michigan, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create a Table using ADOX

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


 
Old January 17th, 2006, 05:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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.

Code:
Sub CreateNullableColumn()

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog

'Return Reference to current database.
Set cat.ActiveConnection = CurrentProject.Connection

'Create table
With tbl
   .Name = "tblRecords"

   ' Append new column
   With .Columns
      .Append "NewColumn", adWChar, 50

         'After appending columns, set
         'provider specific properties.
         With !NewColumn
            'ParentCatalog property on table exposes
            'database-specific (e.g., Jet) peoperties.
            Set .ParentCatalog = cat
                '.Properties("Jet OLEDB:Allow Zero Length") = True
                .Properties("Nullable") = True
         End With
   End With
End With

' Append new table to the provider catalog and clean up.
cat.Tables.Append tbl
Set cat = Nothing

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

Code:
For Each col In cat("NewTable").Columns
   For Each prp In col.Properties
      Debug.Print prp.Name
   Next prp
Next col
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:

Code:
With .Columns
   .Append "ID", adInteger
   With !ID
      Set .ParentCatalog = cat
          .Properties("Autoincrement") = True
          .Properties("seed") = CLng(20)
          .Properties("increment") = CLng(20)
   End With
End With
HTH,

Bob



 
Old January 17th, 2006, 06:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 17th, 2006, 06:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 17th, 2006, 09:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

 
Quote:
quote:Wouldn't the string:
Quote:

strSQL = strSQL & "CREATE TABLE TestTable2("

yield a null string which would propagate throughout the string.
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

 
Old January 18th, 2006, 10:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

if

strSQL = strSQL & "CREATE TABLE TestTable2("

works ok (without generating a null string)

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

:)


Rand
 
Old January 18th, 2006, 02:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old April 12th, 2006, 03:52 AM
Registered User
 
Join Date: Apr 2006
Location: springfield, illinois, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?




Similar Threads
Thread Thread Starter Forum Replies Last Post
Create generic XSL Template to create table Venkatachalapathy XSLT 5 March 11th, 2008 07:49 AM
Appending a Table to a Catalog using ADOX ShoeBucket Access VBA 3 June 15th, 2007 01:58 PM
Create Custom Field Property using ADOX BoGusman Pro VB Databases 2 July 13th, 2006 05:16 PM
ADOX speedlearner BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 June 23rd, 2006 04:15 PM
Create table from another table using ADO Kaustav Pro VB Databases 0 October 15th, 2005 05:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.