When I attempt to create an Access table and add over 7 columns then
I get the following error: Runtime error '-2147467259 (80004005)':
Record is too large
Anyone know why? Is that the limit of appending to a ADOX table object?
Using VB 5.0 w/ADO Ext 2.8
Code follows:

Private Sub CmdInsert_Click()
Dim cn As New ADODB.Connection
Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & PathtoMDB & "db1.mdb"
Set Cat.ActiveConnection = cn

objTable.Name = "Table1"
objTable.Columns.Append "ASSET", adWChar
objTable.Columns.Append "MANUFACTURER", adWChar
objTable.Columns.Append "MODEL", adWChar
objTable.Columns.Append "DESCRIPTION", adWChar
objTable.Columns.Append "SERIAL_NUMBER", adWChar
objTable.Columns.Append "PLANT", adWChar
objTable.Columns.Append "DEPARTMENT", adWChar
'commenting out the following line removes the error
objTable.Columns.Append "CAL_DUE_DATE", adWChar
Cat.Tables.Append objTable
MsgBox "Finished creating mdb Table1 file", vbOKOnly, "Message"
cn.Close
Set cn = Nothing
Me.Hide
Unload Me

End Sub

The problem isn't that you've exceeded the maximum number of columns you can append to a table. I suspect its that you've exceeded the maximum number of bytes a Jet record can contain by using the fixed-length adWChar data type with its default defined size (which gets doubled since adWChar stores Unicode values). Try using its variable length version: adVarWChar. Or use adWChar, but specify a defined size like:

objTable.Columns.Append "MANUFACTURER", adWChar, 50

HTH,

Bob

You got me curoius about why exactly your code breaks at 8 records.

adWChar maps to the Jet "Text" (nchar) data type which has a maximum field length of 255 characters, which is the default length your fields recieve. However, adWChar stores a unicode charatcer string, and Unicode characters require 2 bytes of storage. So when you use ADOX to create an adWChar field with a default field size, you get:

510 bytes + 2 bytes of overhead = 512 bytes required for physical storage in a Jet data page.

The maximum allowable Jet 4.0 record size is 4,096 bytes. 4,096 divided by 512 is 8, give or take a few bytes.

So when you attempt to append your 8th record, the physical storage requirements of the record exceed the amount of free space available in a Jet data page (4,096 bytes).

BUT, if you look at the created adWChar field in table design view, "Unicode Compression" is enabled ("Yes"). Oddly though, it isn't, unless you also enable it through ADOX.

I was playing around with the following, and it simply demonstrates that you can add as many adWChar fields to a Jet table created using ADOX as you like, so long as you also enable Unicode Compression via ADOX:

Sub PointlessUnicodeExercise()

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

    Set cat.ActiveConnection = CurrentProject.Connection

    With tbl
        .Name = "Table1"
        With .Columns
            .Append "Column1", adWChar
                With !Column1
                    Set .ParentCatalog = cat
                        .Properties("Jet OLEDB:Compressed " _
                                    & "UniCode Strings") = True
                End With
            .Append "Column2", adWChar
                With !Column2
                    Set .ParentCatalog = cat
                        .Properties("Jet OLEDB:Compressed " _
                                    & "UniCode Strings") = True
                End With
            .Append "Column3", adWChar
                With !Column3
                    Set .ParentCatalog = cat
                        .Properties("Jet OLEDB:Compressed " _
                                    & "UniCode Strings") = True
                End With

                'ETC....ETC....ETC....

        End With
    End With

    cat.Tables.Append tbl
    Set cat = Nothing

End Sub

When mapping ADOX string data types to Jet data types adVarWChar is the way to go.

Bob

Oops...


"record" in:

"why exactly your code breaks at 8 records"

and

"when you attempt to append your 8th record"

should be "column" or "field" instead of "record"

Thanks for the help. The variable char formatting fixed it.