Memo fields clobbered (Access 2003)

I have a table with 250 fields. Of course you are wondering why 250
fields... what could I possibly be storing in so many fields?

I am using this table as a general import table for files that vary
based on user selections. The input files are CSVs and can number from
2 to 175 columns. Each field is usually able to fit into a Text field
(< 255 characters) but every now and then a longer field creeps in.
since I don't know in which column a long field will show up, I had to
make all the fields Memo fields. It seems that Access can't handle
more then 100 Memo fields on an import.

I want to extend the number of importable fields to 250 (hopefully
beyond whatever they will ever need). Since the total character count
for all fields combined will be way less than the length of a single
Memo field (32K characters), I am trying to read the CSV row into a
Memo field in another table and then I parse the CSV field myself and
place the field values into fields in the 250-column general import
table.

This is working fine except that when I start running queries like
this one:

UPDATE tblRawImport SET Field108 = "Some value" WHERE ID = 150; (ID is
the Access defined key)

The updates to columns 1 - 100 work fine. When I am updating a field
number over 106, Access clobbers some of the fields that I have
already populated. So Field1 and Field2 and perhaps up to Field5 get
overwritten with gibberish (looks like some Asian character set).

So, my questions:

Is there a practical limit to the number of fields that Access 2003
can handle (separate from the published 255)?

If so, can any of you think of another way to get this data
automatically (via VBA) imported into an Access table?

Thanks one and all,
-Steph

Re: Memo fields clobbered (Access 2003)

wrote in message
news:ee580f2f-0d9b-455a-9591-e6105dbfd330@i29g2000prf.google groups.com...
> I have a table with 250 fields. Of course you are wondering why 250
> fields... what could I possibly be storing in so many fields?
>
> I am using this table as a general import table for files that vary
> based on user selections. The input files are CSVs and can number from
> 2 to 175 columns. Each field is usually able to fit into a Text field
> (< 255 characters) but every now and then a longer field creeps in.
> since I don't know in which column a long field will show up, I had to
> make all the fields Memo fields. It seems that Access can't handle
> more then 100 Memo fields on an import.
>
> I want to extend the number of importable fields to 250 (hopefully
> beyond whatever they will ever need). Since the total character count
> for all fields combined will be way less than the length of a single
> Memo field (32K characters), I am trying to read the CSV row into a
> Memo field in another table and then I parse the CSV field myself and
> place the field values into fields in the 250-column general import
> table.
>
> This is working fine except that when I start running queries like
> this one:
>
> UPDATE tblRawImport SET Field108 = "Some value" WHERE ID = 150; (ID is
> the Access defined key)
>
> The updates to columns 1 - 100 work fine. When I am updating a field
> number over 106, Access clobbers some of the fields that I have
> already populated. So Field1 and Field2 and perhaps up to Field5 get
> overwritten with gibberish (looks like some Asian character set).
>
> So, my questions:
>
> Is there a practical limit to the number of fields that Access 2003
> can handle (separate from the published 255)?
>
> If so, can any of you think of another way to get this data
> automatically (via VBA) imported into an Access table?
>
> Thanks one and all,
> -Steph

Write a function to open the text file, look at the 1st record and build a t
emp table to receive the file.

Re: Memo fields clobbered (Access 2003)

On Thu, 17 Jan 2008 09:54:50 -0800 (PST), steph@landauconsulting.net
wrote:

If you can repeat that "overwriting with Asian chars" in A2007 SP1,
accdb format, I'm sure MSFT would be interested.
Those CSV files probably contain repeated data, right?
E.g.
Product1, Size1, Price, other attributes
Product1, Size2, Price, other attributes
(use your imagination)
In a relational database this data should be split up into several
tables.
If you are running into the limitations of Access, you either reassess
your strategy (perhaps with the benefit of professional help), or you
go to a different database engine such as SQL Server which allows for
1024 columns (aaarrrggghhhh) and varchar(8000) and many memo (they
call it Text) fields that actually work and don't corrupt.

-Tom.


>I have a table with 250 fields. Of course you are wondering why 250
>fields... what could I possibly be storing in so many fields?
>
>I am using this table as a general import table for files that vary
>based on user selections. The input files are CSVs and can number from
>2 to 175 columns. Each field is usually able to fit into a Text field
>(< 255 characters) but every now and then a longer field creeps in.
>since I don't know in which column a long field will show up, I had to
>make all the fields Memo fields. It seems that Access can't handle
>more then 100 Memo fields on an import.
>
>I want to extend the number of importable fields to 250 (hopefully
>beyond whatever they will ever need). Since the total character count
>for all fields combined will be way less than the length of a single
>Memo field (32K characters), I am trying to read the CSV row into a
>Memo field in another table and then I parse the CSV field myself and
>place the field values into fields in the 250-column general import
>table.
>
>This is working fine except that when I start running queries like
>this one:
>
>UPDATE tblRawImport SET Field108 = "Some value" WHERE ID = 150; (ID is
>the Access defined key)
>
>The updates to columns 1 - 100 work fine. When I am updating a field
>number over 106, Access clobbers some of the fields that I have
>already populated. So Field1 and Field2 and perhaps up to Field5 get
>overwritten with gibberish (looks like some Asian character set).
>
>So, my questions:
>
>Is there a practical limit to the number of fields that Access 2003
>can handle (separate from the published 255)?
>
>If so, can any of you think of another way to get this data
>automatically (via VBA) imported into an Access table?
>
>Thanks one and all,
>-Steph