Memofield in SQL Server causes ODBC error

[heso]

I have a user interface in MSAccess97 that accesses a database in SQL Server 2000 through ODBC. When I put a memofield in my table and open the table in MSAccess it starts of fine. I can access everything and alter everything, including the memofield. Then after 1 or 2 minutes the connection seems to be lost, I get the error message "ODBC call failed" and my table looks like this:

__________________________________________________
| ZktId | StartDate | Etc... | MemoField |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________
| #Name? | #Name? | #Name? | #Name? |
__________________________________________________

When I remove the memofield everything works fine again.
Datatype of the memofield is text, ODBC version 2000.81.7713.00 from 07/13/01.

Does anyone know the solution?

Thanks, Henri Sonnenschein

 

[Zequel]

Though I haven't seen your specific problem, I have had problems with Memo/Text and BLOBs fields. Sometimes BLOB fields create a problem with the field delimiters databases use to return results at some level (I haven't seen the raw data, only the problematic results). There are two workarounds, one is to specify the SELECT statement so it selects the Memo field last, SELECT Zip,Statdate,..,NameofMemofField instead of selecting it with * or in the middle. The second option is to break it into a second table and link by the primary key of the first table. Hope this helps. I used the second option and my problems went away.

 

[heso]

The problem is just as I described, there was no programming involved yet. Just a linked table in MSAccess97 and when you open it, after a short time it looks like above.
The table contains 10 fields and the memofield is allready the last field in the table.
Upon your message, I stripped the table, containing just an Id-field, a relational field and the memofield. Opening the new linked table in MSAccess97 it stays surprisingly stable, even after some hours. Allthough I still don't know what caused the failure, your reply has been helpfull to me.

Thanks Zequel Henri Sonnenschein