There is (apparently) a known bug with the relationship between Access 97 and SQL Server 7.0, that being: When you upsize a table that has a field with the “Memo” data type it upsizes to a Text or an NText field on the server. If the table in question has several fields, the access database will function for a period of time (sometimes seconds, sometimes longer) then it will have an ODBC Call Failure.
I have found that (if possible) you can reduce the number of columns on the table, reduce the size of the comments section (varchar 5000) OR you can create an additional table to house the comment section (i.e. a table with a key identifying the record to which it relates from the primary table), but these are not always the solution.
IF all of the fields/columns on the table are used constantly then that negates the first option.
IF the notes in existance are already larger than the max size for the Varchar datatype (8000), then that negates the second option.
IF the comments section must appear in a continuous form, that negates the third option.
Are their any other work-arounds known for this situation?
Thanks for all suggestions.
Jeff
