ODBC Call Failed

[laughingdevil]

Hi, hopefully someone can help me.

I'm developing a front end in Access 2002 (SP3) for a SQL Server 2008 DB.

I have a link table in access that links to a view in SQL Server.

When opening the link table the data in it is displayed fine.

I have a query which then uses this link table, in effect to filter by a "log_id" in effect the number assigned to the site on data import. These are contained in the view and if I open the link table and filter the list the ones I'm looking for are there.

However using this very basic query seems to take an age, and more often than not result in this error message.

Anyone got any ideas? I've checked a few of the MS KB and internet forumns and not found anything that seems to apply.

 

[boblarson]

Check to see if any of the fields in the table (within SQL Server) are set to the datatype of

varChar(Max)

I've had issues with that as well. So, a quick change to something like:
varChar(10000)
that would be reasonable for the field, tends to solve that issue. There are also some issues sometimes with Null fields and the use of the Bit datatype as well.

 

[laughingdevil]

Thanks for that, no big varchars but some nulls, I did a bit of indexing on the key fields that most of my searches are based on, has vastly improved performance from 2:30 for a query to <1 sec.

Didn't see indexing as a response on any other MB /KB I checked but defo worth it IMO.