Wrox Programmer Forums
|
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 February 11th, 2005, 06:18 PM
Registered User
 
Join Date: Jul 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADOX Access column add error

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

 
Old February 11th, 2005, 08:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old February 11th, 2005, 11:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old February 12th, 2005, 07:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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"



 
Old February 26th, 2005, 04:05 PM
Registered User
 
Join Date: Jul 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Add 1 to next column deontae45 SQL Language 2 December 18th, 2006 01:42 PM
Access ADOX - delete PK Derek_05 Access VBA 7 January 4th, 2006 03:32 AM
Access ADOX - delete PK Derek_05 Access 0 December 14th, 2005 10:56 AM
add column (C#) kobystud C# 1 July 21st, 2004 10:58 AM
ADOX MSDASQL Access Properties Collection is EMPTY Xentrax Access 0 May 27th, 2004 10:36 AM





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