ODBC --Call Failed Error

[mizveggie]

I have been working on this Access 2007 application for at least 6
months now. I have distributed live versions running on several user
workstations. Beginning yesterday, I started to get an ODBC --Call
failed error in my development environment. I have not changed any
settings and originally did not change any ODBC connections. My
application links to SQL Server 2005 tables, and when I have a query
that accesses these tables I will sometimes get the error. The
behavior is incredibly erratic. Within one procedure I execute 2 SQL
calls which incorporate the linked SQL tables. The first one
executes, then the second returns the ODBC --Call failed error.
However, this morning they both executed fine for several hours. Very
strange behavior. I changed the ODBC connection from a file
connection to a machine data source and that has no effect on the
behavior. I have profiled SQL Server and I see the successful
procedure calls, but I do not see any attempts at connection that have
failed. I have tried to trace ODBC itself, but either I do not know
what I am doing (possible and likely) or the log file is not being
created at all. Any and all help will be very greatly appreciated.

 

[golfinray]

I have ODBC failures occasionally. What always fixes it for me is to
re-create the connection. Open a new instance of Access. Do file\new and
scrool down to ODBC and re-enter your server, password, etc. Also check the
timeout time. If it is too short it will timeout before you get loaded. Also
remember the sql server has an ODBC driver that does occasionally fail.

 

[mizveggie]

How can I check the timeout on SQL Server native ODBC Connections?

 

[golfinray]

Tools/options/advanced - I set mine to 1500 seconds. Hope that helps

 

[mizveggie]

Sorry - stupid question, but Tools in what or where?

 

[golfinray]

Also try going to control panel/data sources and see if your ODBC driver
hasn't crapped out.

 

[golfinray]

Access has a tools menu somewhere. Generally the same place you backup your
database.

 

[ake]

you may try the following:
on the query, click on design mode
right click on the query page, choose properties
query properties should pop out
change the ODBC timeout to 180 instead of 60