Column Widths and Jet 4.0

[Guest]

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that there
is one column (the [Description] column) that I need to INSERT a lot of data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way for
me to tweak the "field," which is actually a column in this case, so that I
can get more data into it using the OLE DB provider? Thanks for any help.

 

[Douglas J. Steele]

What version of Excel? I believe in most versions, a cell can only hold a
maximum of 255 characters.

 

[Guest]

It's Excel 2003, and I can certainly type more than 255 chars into that
column, so that doesn't seem like it would be the problem. But thanks.

What version of Excel? I believe in most versions, a cell can only hold a
maximum of 255 characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that
there
is one column (the [Description] column) that I need to INSERT a lot of
data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way
for
me to tweak the "field," which is actually a column in this case, so that
I
can get more data into it using the OLE DB provider? Thanks for any help.

 

[Douglas J. Steele]

Unfortunately, I believe that the default drivers don't actually result in
Excel 2003 format.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's Excel 2003, and I can certainly type more than 255 chars into that
column, so that doesn't seem like it would be the problem. But thanks.

What version of Excel? I believe in most versions, a cell can only hold a
maximum of 255 characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT
a
row of data into an Excel speadsheet. The problem I encounter is that
there
is one column (the [Description] column) that I need to INSERT a lot of
data
into, but I keep getting a "field is too small" error from the Jet
engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any
way
for
me to tweak the "field," which is actually a column in this case, so
that
I
can get more data into it using the OLE DB provider? Thanks for any
help.

 

[Guest]

Since the OLE DB Provider for Jet 4.0 is intended primarily for Access and
NOT Excel, is the issue that Access text fields can only have 255 chars? I
see that you are a Microsoft Access MVP, so I thought you might know this.
Thanks.

Unfortunately, I believe that the default drivers don't actually result in
Excel 2003 format.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's Excel 2003, and I can certainly type more than 255 chars into that
column, so that doesn't seem like it would be the problem. But thanks.

What version of Excel? I believe in most versions, a cell can only hold a
maximum of 255 characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT
a
row of data into an Excel speadsheet. The problem I encounter is that
there
is one column (the [Description] column) that I need to INSERT a lot of
data
into, but I keep getting a "field is too small" error from the Jet
engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any
way
for
me to tweak the "field," which is actually a column in this case, so
that
I
can get more data into it using the OLE DB provider? Thanks for any
help.

 

[Douglas J. Steele]

It's possible. I'm afraid I didn't look closely at the "plumbing" when the
OleDB provider was released.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Since the OLE DB Provider for Jet 4.0 is intended primarily for Access and
NOT Excel, is the issue that Access text fields can only have 255 chars? I
see that you are a Microsoft Access MVP, so I thought you might know this.
Thanks.

Unfortunately, I believe that the default drivers don't actually result
in
Excel 2003 format.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's Excel 2003, and I can certainly type more than 255 chars into that
column, so that doesn't seem like it would be the problem. But thanks.

:

What version of Excel? I believe in most versions, a cell can only
hold a
maximum of 255 characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to
INSERT
a
row of data into an Excel speadsheet. The problem I encounter is
that
there
is one column (the [Description] column) that I need to INSERT a lot
of
data
into, but I keep getting a "field is too small" error from the Jet
engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any
way
for
me to tweak the "field," which is actually a column in this case, so
that
I
can get more data into it using the OLE DB provider? Thanks for any
help.

 

[John Nurick]

Hi David,

The subject of column types in Excel came up recently in
microsoft.public.access.externaldata, subject line
values in an exported file

If you take a look at it you may get some ideas about how to make an
Excel column work like "Memo" rather than "Text".

However:
1) Are you certain the problem isn't a manifestation of the issue
described at http://support.microsoft.com/?id=207668 (When you export a
query that contains an expression that results in more than 255
characters, the expression is truncated in the exported file)?

2) Recent Access service packs (e.g. Office 2003 SP2) have removed the
ability to use queries or linked tables to write to Excel worksheets,
and I was under the impression that this affected the Jet engine
generally. What version and service packs of Office are you using?

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that there
is one column (the [Description] column) that I need to INSERT a lot of data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way for
me to tweak the "field," which is actually a column in this case, so that I
can get more data into it using the OLE DB provider? Thanks for any help.

 

[Guest]

I have actually done a little research and answered my own question. Here is
what is going on: When you connect to an Excel spreadsheet using the Jet OLE
DB provider, the Jet engine examines the first X rows of the file, where X is
a value in the registry that defaults to 8, to see what OLE DB data type
should be assigned to each column. When the first 8 rows are empty, as in my
case, it assigns a data type of Text(255) to that column, so that is where
the 255 character limit was coming from. So what I did was in the first 8
rows of my spreadsheet in the column that I wanted assigned the data type of
Memo, that is a huge text field not a tiny one, I typed 256 characters into
the [Description] cells. Then when I connected to the Excel spreadsheet, Jet
saw that it could not fit this data into a Text data type, so it assigned
this column the Memo data type instead, and now my code works perfectly. The
only adjustment I had to make was that when my application consumes the Excel
file it has to ignore the first 8 rows, because those rows are only used to
"trick" the Jet engine. If anyone would like more details or info on what I
found out on this topic, just let me know. Thanks, guys.

Hi David,

The subject of column types in Excel came up recently in
microsoft.public.access.externaldata, subject line
values in an exported file

If you take a look at it you may get some ideas about how to make an
Excel column work like "Memo" rather than "Text".

However:
1) Are you certain the problem isn't a manifestation of the issue
described at http://support.microsoft.com/?id=207668 (When you export a
query that contains an expression that results in more than 255
characters, the expression is truncated in the exported file)?

2) Recent Access service packs (e.g. Office 2003 SP2) have removed the
ability to use queries or linked tables to write to Excel worksheets,
and I was under the impression that this affected the Jet engine
generally. What version and service packs of Office are you using?

I am trying to use the OLE DB Provider for Jet 4.0 in .NET 2.0 to INSERT a
row of data into an Excel speadsheet. The problem I encounter is that there
is one column (the [Description] column) that I need to INSERT a lot of data
into, but I keep getting a "field is too small" error from the Jet engine.

Is this an Excel limitation, a Jet limitation, or what? Is there any way for
me to tweak the "field," which is actually a column in this case, so that I
can get more data into it using the OLE DB provider? Thanks for any help.