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