Link Tables to SQL Server (Access 97/SQL7.0)

Topic

New Reply

I have an access db with two linked tables. These are linked via odbc to a SQL db. One table returns all data accurately whilst the other shows some records as #ERROR. When that record is selected by the user a messagebox appears saying:

“The Field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

At first i thought that this was due to the record being editted in the SQL application but these records do not change ie record 10 always contains #ERROR in access. In SQL the record is fine.

I think now, this might be a problem converting from SQL data types to Access but do not understand why as there is no data stored in the Access db, it is only a way of getting to the data for reporting purposes.

If this is the case, how can i negotiate it ?

Thanks in advance.

Reply | Quote

Replies

Have you tried refreshing the linked table, or actually dropping the offending table and then reconnecting to it. We find that will typically fix these kind of problems. We run many Access databases against SQL 7 tables and have never seen a data type problem between the two. What kind of data types does your SQL table use?

Reply | Quote

i think i may have a better clue as to what is going on.

after some speculative queries i have discovered that the records that show as #ERROR have a least one field with a type of text and a content length off 255.

running a query on a particular field to show descending content length, the first three rows show #ERROR then the fourth returned haas a length of 254 characters.

the same query in SQL returns 3 rows of 255 then 254 etc.

Is there a fundamental difference in the way that the two databases treat data like this ?

Reply | Quote