Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC -- connection to SQL Server failed

Posted on 2004-04-25
6
Medium Priority
?
2,789 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:will_rein99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 10914187
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10914188
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
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 300 total points
ID: 10914476
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Assisted Solution

by:Karen Falandays
Karen Falandays earned 150 total points
ID: 10931429
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
 

Author Comment

by:will_rein99
ID: 10931787
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
 
LVL 4

Expert Comment

by:AndyAmess
ID: 10948505
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question