How to create very wide tables in Access?

I am using VB6 to create and load a table in an Access .mdb database. The table is very wide (6000 bytes wide) and when I load data there are about 200 rows that load and then I get a 'Record is too large' error which means that the table is too wide. I realize that the maximum size of all fields is 2000 bytes, but I am able to create this same table by using the Import/Export tool in SQL Server and then I am able to use it. So once the table is created, there is no problem querying and using it. I assume the limitation is in the DAO and ADO drivers. Also why can I load several hundred rows before I get this error?

Any idea on how to get around this, other than using MSDE?

Thanks in advance,

WayneM

Wayne

If you can "stuff" the data into SQL Server, why do you need Access?

You didn't provide any information about the data itself ... is there a
chance that simply copying it into Access doesn't reflect any normalization
(i.e., same as stuffing it into a spreadsheet?)?


--
More info, please ...

Jeff Boyce
Access MVP

The table is very wide (6000 bytes wide) and when I load data there are about 200 rows that load and then I get a 'Record is too large' error which means that the

Can you fly up about 10,000 feet and provide a more generalized view of what kind of data you are working with and what you want to do with it?

Jay

Jay,
The tables have about 250 fields all with varchar and dates and no numeric data. This is why the table is so wide. This data is used to analyze problems in a semiconductor factory, where these tables are joined with other data that has numeric data. There is an analysis tool that uses this data to perform analysis as to what happens over time, what events are common with problem products, etc. It then helps to pinpoint the problem cause. There are many of these wide tables that are used as well as many tables with numeric data. The tables with numeric tables also have about 250 fields, but since they are numeric data types, this is no problem.

The analysis tool creates a type of virtual view of all fields across many tables, so it looks more like a normalized database. This is done for performance reasons, since the tool is a very fast interactive data visualization tool.

Thanks,

Wayne

"Jay Vinton" wrote:

The table is very wide (6000 bytes wide) and when I load data there are about 200 rows that load and then I get a 'Record is too large' error which means that the

Can you fly up about 10,000 feet and provide a more generalized view of what kind of data you are working with and what you want to do with it?

Jay

The tables have about 250 fields all with varchar and dates and no numeric data.

Wow, you are way over limit on row size, and almost at the limit for number of columns per table. It seems unlikely that you need so many columns.

Has the data been normalized? If it's already in at least third normal form, then maybe you should re-work your relationship model.

If this is as good as it gets, you should switch to MSDE or SQL Server.

Jay

Even if your import tables are in 3rd normal form, I would
consider breaking them down further so that you have like
75-100 columns in each table, then run your analysis pgm
on them.

Are you aware that SQL Server is like 30% faster than
access in a similar query? (and it can handle larger
tables)

In answer to your second question, the first 100 or so
rows are probably just under the limit for row size and
the 101st (or whereever it stops) jumps the limit by a few
bytes.


-----Original Message-----
The tables have about 250 fields all with varchar and
dates and no numeric data.

Wow, you are way over limit on row size, and almost at
the limit for number of columns per table. It seems
unlikely that you need so many columns.

Has the data been normalized? If it's already in at least
third normal form, then maybe you should re-work your
relationship model.

If this is as good as it gets, you should switch to MSDE
or SQL Server.

Jay

.

If you can create this table from SQL Server, then either ADO or DAO must be
able to handle the record.
How are you doing it in VB? ADO or DAO? OLEDB or ODBC? Is this in fact the
same data as tested with the SQL Server export? When you say 'about 200
rows', do you mean any 200 rows? Or is it failing repeatedly on one specific
record?

And if you can get the data from SQL Server, can you just link to a Server
view? Why do you need the data in an Access table at all?

(david)


"WayneM" wrote in message
...
I am using VB6 to create and load a table in an Access .mdb database. The
table is very wide (6000 bytes wide) and when I load data there are about
200 rows that load and then I get a 'Record is too large' error which means
that the table is too wide. I realize that the maximum size of all fields
is 2000 bytes, but I am able to create this same table by using the
Import/Export tool in SQL Server and then I am able to use it. So once the
table is created, there is no problem querying and using it. I assume the
limitation is in the DAO and ADO drivers. Also why can I load several
hundred rows before I get this error?

Any idea on how to get around this, other than using MSDE?

Thanks in advance,

WayneM