maximum number of fields created through CREATE TABLE ? I seem to be stuck at 8

Question

As long as I stay under 9 fields, the table is created in the database.

From 9 fields on I get an error, which translated into English from Dutch is something like "Record is too big"

(I have added some nonsense fields to the code to get to 9)

 Using cmd As New OleDbCommand()
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "CREATE TABLE tblProject (ProjectID COUNTER, WrittenBy CHAR, ContactDetails CHAR, roetepetoet CHAR, TitleBlock CHAR, popopopopop CHAR, Producer CHAR, xxxooxxx CHAR, ProductionTitle CHAR, PRIMARY KEY (ProjectID))"

        cmd.ExecuteNonQuery()
    End Using

the error message is crazy long!

But when I look at the line numbers mentioned in the error message, there is nothing even closely related to what I'm doing as far as I can tell.

I have looked online, but what I found bears no relation to my problem.

Has anyone ever come across something similar and found a workaround? Your input is most appreciated! as always!

Answer 1

To be honest, I do not know why you receive the "Record is too large" error when using the CREATE TABLE statement with the CHAR keyword to specify the field type. In Access, you receive this error when the record is created and the sum of the entered characters in all fields exceeds the maximum limit.

A work-a-round is to use the TEXT keyword instead of the CHAR (or CHARACTER) keyword.

In regards to the record size limit, see Access specifications:

This limit appears to be approximately 2000 Unicode characters without compression.

To enable compression on a TEXT field, you need to apply the WITH COMPRESSION attribute to the field definition.

For example: WrittenBy TEXT(255) WITH COMPRESSION