Access 97: "Field is too small" error on upsized ODBC data set

[Guest]

I'm running an application in Access 97. I've given the back-end Access
database to our SQL group to upload into the corporate SQL server. They've
done this, and I can access the data ok with tools like Excel 2000 and MS
Query 2000. However, when I try to open the same table with Access 97, there
are several records marked with "#Error" in every field, and I get a message
that says "Field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data."

Note: I am not trying to change or add any data when I receive this
message. In fact, this message appears even when positioned on another
record. But I'm pretty sure this message is related to the records that are
marked with #Error. All fields in the #Error records are marked with #Error,
so I can't determine which particular field might be causing this. In
looking at the field contents in MS Query, there is nothing I'm seeing that
is unusual. I've got 667 records in this particular table, and 14 of the
records are marked as #Error.

This error seems to be related to Access, since I am able to successfully
see the suspect records in the other applications.

Any ideas of what I need to do in Access to correct this? Is this a result
of the upsizing conversion? Do I need to request some adjustment in that
process? Is there something in particular that I need to look at in the
actual data of these records?

 

[Pieter Wijnen]

Refresh the table links
Check the version of the ODBC Driver

HtH

Pieter

 

[Guest]

I've rebuilt the table links multiple times, all with the same results. I'm
running Win2000, SP4, which is the latest service pack for this OS.

The fact that I'm not getting the error in other applications on my desktop
that is using the same ODBC drivers would be an indication that the ODBC
driver is not at fault, wouldn't it? I'm running the SQL Server
2000.81.9054.00 driver and the SQL Native Client 2005.90.3042.00 driver. I'm
not sure which one is used in this particular ODBC connection.

 

[Guest]

I've done a little more testing. I accessed the SQL table from a WinXP
machine (that is, a different machine) by creating a new ODBC connection, and
using the same options as on the original machine. So this would be using a
newer ODBC SQL driver (SQL Server 2000.85.1117.00).

Within Access97 on the different machine, I linked to the SQL tables. Then
I tried to open the problem table. In this case, only one record displayed.
It was loaded with #Error across all of the fields. No other records were
displayed, either with normal data or with the #Error message. I got the
same error message in a dialog box.

So, different machine, different driver, similar, but not quite the same
results.

 

[Sylvain Lafontaine]

Make sure that a primary key has been defined for this table and that it is
based on a compatible type of field. For example, a primay key based on a
BIGINT would be a no go because Access 97 (and 2000/2/3?) doesn't understand
this type. It's also possible that a composite primary key (ie, based on
more than one field) could give you problem.

Also, if there are other unique indexes beside the primary key, make sure
that the name of these indexes come later in alphabetical order. (This was
an old problem with A97, don't know if it was ever corrected.).

There are a lot of other things that could give you trouble. For example,
if I remember correctly, A97 doesn't really understand Unicode; so any field
of type nchar, nvarchar or ntext might be a no go with A97.

If you still have problem, you should post here the full description of the
table in order to let people take a look at it but don't forget that A97 is
now really old stuff.