Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 ? <img src=/S/scream.gif border=0 alt=scream width=15 height=15> <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •