ODBC -- connection to SQL Server failed

I'm helping a company upgrade from an all Access database to an Access 2000 frontend/SQL 2000 backend solution.  I've got a situation that I haven't seen before.  

Everything was running pretty smoothly.  Then a couple of days ago a user reported that one of her Access queries returned the following error:
"ODBC -- connection to SQL Server failed".  Seems like a simple connection issue, however it doesn't happen all the time.  I verified that when Access is first opened, the query will throw the error listed above.  If I then open any other query or table, and then try the troubled query, it will work and stay working for the rest of the time I have Access open, only to throw the error again once Access is restarted.

The query in question is calling only one table, it's very basic.  

Does anybody have any idea what could be causing the problem described above?
Thank you,
--Will
will_rein99Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nico5038Connect With a Mentor Commented:
Did you check upon the ODBC timeout setting for the query, this is by default 60, but I often use 600.
Just openproperties when in the query's design.

Nic;o)
0
 
nico5038Commented:
Did you check upon the ODBC timeout setting for the query, this is by default 60, but I often use 600.
Just openproperties when in the query's design.

Nic;o)
0
 
Alan WarrenConnect With a Mentor Commented:
Hi Will,

Is this an ADP front-end or MDB?
Is your connection using Integrated Security or SQL server login?

debug.print CurrentProject.Connection

Alan
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Karen FalandaysConnect With a Mentor Training SpecialistCommented:
To add to Nico's point, I like to change timeout to 0 for NO limit, as this is the typical error message you will get when the server cannot respond quickly enough!
Karen
0
 
will_rein99Author Commented:
All,
  The one linked table the query was using needed to be relinked from scratch.  Refreshing the linked table did nothing but removing the table entirely and relinking worked.  I had to reapply the formatting done to the linked table.
  I'm splitting the points to the group for helping.
Thank you,
--Will
0
 
AndyAmessCommented:
Will,

I know the question is closed but the error you got is one I had a couple of times if I Add a column to my Sybase table then the link does not seem to want to work as it doesn't match the backend. Is it possible that the one user had a link that had not been updated to fit to the back end changes.

Just a thought. No need to reply.

A. ;-)
0
All Courses

From novice to tech pro — start learning today.