Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Arizona
    Posts
    5

    Unhappy Unanswered: ODBC call failed - communication link failure

    I tried posting this before, but I think I screwed up and posted it as a poll - sorry.

    Any one have any suggestions??? I am trying to open linked tables in Access 2003 on Windows XP (Jet 4.0 Service Pack 8 is installed). The tables reside in a SQL 2000 DB. I can view the details of linked, and I can open some of the tables, but two of the tables are causing problems. When I try to open them, one of the two following messages comes up:

    "ODBC Call Failed" and then the table opens but all of the cells have #Name?

    OR

    "ODBC Call Failed.

    [Microsoft][ODBC SQL Server Driver] Communication Link Failure (#0)" then when I close that, another box opens saying "Microsoft Office Access can't open the table in Datasheet view" and when I say ok to that, it then closes and just shows the table list.

    After one of these two tables triggers the Call failure error, then I can no longer open the other tables that were working - they all present the Call failure / communication link failure error. I have to close Access and then go back in to get the other tables to open again.

    Any suggestions are greatly appreciated.

    Cheryl

  2. #2
    Join Date
    Mar 2004
    Location
    Midwest
    Posts
    4

    Re: ODBC call failed - communication link failure

    Has anyone found an answer to this problem? I am having the same problem on a form that is refreshed every 20 seconds. After a while it has an error on one of the fields and says object no longer set. Then it screws up all of the other fields. If I try to open the table it says
    "ODBC call failed - Communication link failure (#0)"

    THANKS!!
    Randy


    Originally posted by cherylnm
    I tried posting this before, but I think I screwed up and posted it as a poll - sorry.

    Any one have any suggestions??? I am trying to open linked tables in Access 2003 on Windows XP (Jet 4.0 Service Pack 8 is installed). The tables reside in a SQL 2000 DB. I can view the details of linked, and I can open some of the tables, but two of the tables are causing problems. When I try to open them, one of the two following messages comes up:

    "ODBC Call Failed" and then the table opens but all of the cells have #Name?

    OR

    "ODBC Call Failed.

    [Microsoft][ODBC SQL Server Driver] Communication Link Failure (#0)" then when I close that, another box opens saying "Microsoft Office Access can't open the table in Datasheet view" and when I say ok to that, it then closes and just shows the table list.

    After one of these two tables triggers the Call failure error, then I can no longer open the other tables that were working - they all present the Call failure / communication link failure error. I have to close Access and then go back in to get the other tables to open again.

    Any suggestions are greatly appreciated.

    Cheryl

  3. #3
    Join Date
    Feb 2004
    Location
    Arizona
    Posts
    5

    Re: ODBC call failed - communication link failure

    I still have not found a solution
    cheryl


    Originally posted by rmersch
    Has anyone found an answer to this problem? I am having the same problem on a form that is refreshed every 20 seconds. After a while it has an error on one of the fields and says object no longer set. Then it screws up all of the other fields. If I try to open the table it says
    "ODBC call failed - Communication link failure (#0)"

    THANKS!!
    Randy

  4. #4
    Join Date
    Feb 2004
    Posts
    142

    Re: ODBC call failed - communication link failure

    Originally posted by cherylnm
    I tried posting this before, but I think I screwed up and posted it as a poll - sorry.

    Any one have any suggestions??? I am trying to open linked tables in Access 2003 on Windows XP (Jet 4.0 Service Pack 8 is installed). The tables reside in a SQL 2000 DB. I can view the details of linked, and I can open some of the tables, but two of the tables are causing problems. When I try to open them, one of the two following messages comes up:

    "ODBC Call Failed" and then the table opens but all of the cells have #Name?

    OR

    "ODBC Call Failed.

    [Microsoft][ODBC SQL Server Driver] Communication Link Failure (#0)" then when I close that, another box opens saying "Microsoft Office Access can't open the table in Datasheet view" and when I say ok to that, it then closes and just shows the table list.

    After one of these two tables triggers the Call failure error, then I can no longer open the other tables that were working - they all present the Call failure / communication link failure error. I have to close Access and then go back in to get the other tables to open again.

    Any suggestions are greatly appreciated.

    Cheryl
    Check to see if you have the proper permissions for the tables on the server.
    KC

  5. #5
    Join Date
    Feb 2004
    Posts
    142

    Re: ODBC call failed - communication link failure

    Originally posted by rmersch
    Has anyone found an answer to this problem? I am having the same problem on a form that is refreshed every 20 seconds. After a while it has an error on one of the fields and says object no longer set. Then it screws up all of the other fields. If I try to open the table it says
    "ODBC call failed - Communication link failure (#0)"

    THANKS!!
    Randy
    Check to see what the server connection timeout is set at.
    KC

  6. #6
    Join Date
    Mar 2004
    Location
    Midwest
    Posts
    4

    Re: ODBC call failed - communication link failure

    I have found a little more information on this problem. Here is what I found on another posting:


    "This behavior is normal and expected. When the instance fails over, all
    connections are closed, just like shutting down and restarting the server.
    Your ODBC test created a new connection so it works. Access does not know
    how to restart a lost connection, so it fails. If you write a custom app,
    you can include error trapping and reconnect logic to detect and deal with
    this situation and make things transparent to the end user.

    --
    Geoff N. Hiten
    Senior Database Administrator
    Careerbuilder.com
    "


    He says it is a hardware failure. I'm going to do a tabledef.refreshlink on error and see if that helps. I just don't know where the error will be thrown yet. Good luck!

    Randy

  7. #7
    Join Date
    Feb 2004
    Location
    Arizona
    Posts
    5

    Re: ODBC call failed - communication link failure

    Interesting stuff. I know it is not an issue on the SQL server side - it is something specific to that computer configuration. I am linked to the exact same database and tables on two other computers using Access (one Windows 98 Access 2000 and one Windows 2000 Access XP) and have not had that problem before now. Also, there are two trouble tables that seem to cause the problem. I can open some tables over and over again and be fine, but then when I try to open one of the two trouble tables, sometimes it opens and other times it does the communication link failure.

    Do you think it could be at all related to the ISP somehow? The two other computers I have working without a problem are on two different ISPs.

    If you have time, let us know how the relink thing works. I am not even sure I would know how to do that in Access.

    Thanks
    Cheryl

  8. #8
    Join Date
    Feb 2004
    Location
    Arizona
    Posts
    5

    Re: ODBC call failed - communication link failure

    Also, I think it is strange that I can still view table design details after I get the communication link failure, but I cannot see the data - but maybe that is stored locally when the table is linked???

  9. #9
    Join Date
    Mar 2004
    Location
    Midwest
    Posts
    4

    Re: ODBC call failed - communication link failure

    Ok, sorry for the delay, but see if this helps. I actually tried doing something else first to see if it would fix the problem. I went to Tools-Options-Advanced and changed to OLE timeout and number of retries to see if that will help. So far it has helped a little. I still think we have to run a direct line to our server, we have the computer on a switch connected to a few computers now. But here's some code that might help in the meantime.

    rs is a recordset that pulls information from a table. It is actually in a loop that relinks all the tables. This may be more code than you need. It creates a connection string, then if the table doesn't exist creates a new one or else deletes the table and relinks it. We actually run this everytime we open an Access Program linked to SQL Server.

    HTH,
    Randy



    strTblName = rs("LocalTableName") 'grab a table name

    'Build the connection string for the CreateTableDef command:
    strConn = "ODBC;"
    strConn = strConn & "DSN=" & rs("DSN") & ";"
    strConn = strConn & "APP=Microsoft Access;"
    strConn = strConn & "DATABASE=" & rs("DataBaseName" ) & ";"
    strConn = strConn & "UID=" & rs("UID") & ";"
    strConn = strConn & "PWD=" & rs("PWD") & ";"
    strConn = strConn & "TABLE=" & rs("ODBCTableName")



    If (DoesTblExist(strTblName) = False) Then

    Set tbl = db.CreateTableDef(strTblName, dbAttachSavePWD, rs("ODBCTableName"), strConn)

    db.TableDefs.Refresh
    db.TableDefs.Append tbl
    Else 'refresh the links on the table
    Set tbl = db.TableDefs(strTblName)
    db.TableDefs.Delete tbl.Name
    db.TableDefs.Refresh
    Set tbl = db.CreateTableDef(strTblName, dbAttachSavePWD, rs("ODBCTableName"), strConn)
    db.TableDefs.Append tbl

  10. #10
    Join Date
    Mar 2004
    Location
    Midwest
    Posts
    4

    Re: ODBC call failed - communication link failure

    Originally posted by cherylnm
    Also, I think it is strange that I can still view table design details after I get the communication link failure, but I cannot see the data - but maybe that is stored locally when the table is linked???


    Yes I am really sure that he Database design information is stored locally. Because you can add certain things to the database design locally like captions, input masks. Just can't change the type locally or the name of a field.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I recall answering a similar post to this a while back.

    A couple of things I might try:

    1. Make sure all ODBC DSN's connecting to the tables are named EXACTLY the same name. When I had the same type of problem as you're describing, one of the users was opening the mdb but had a slightly different ODBC DSN Name. Once I refreshed the tables for that user, it worked for them but then started happening for the other users (and I refreshed their linked tables which then broke it for the other user, etc...etc...) (this of course drove me crazy until I finally realized 1 user had a slightly different ODBC name.)
    2. Run DBCC on SQL Server against the database (and other associated repair type commands.) (There's a couple of commands I recall running which also seemed to fix this type of problem - sorry but I can't recall what they were offhand but there were 2 other ones besides the dbcc.)
    3. Try adding a timestamp field type to the SQL Server table.
    4. Check that everyone is using the latest version of MDAC. Perhaps re-install on the problematic computers.
    5. Log into the problematic computer as yourself to see if it's computer based or user based.
    6. Run a trace on SQL Server to see if you can identify where the problem is stemming from. Traces are a great tool to use.

    Here are a couple of links from the code bank which may be helpful:
    http://www.dbforums.com/6274793-post22.html (ODBC fixing/creating) - I add this code to every mdb I create and it will automatically create/refresh the ODBC DSN when the user opens the mdb (providing the user has permissions to create ODBC's.) I've never had an issue with this code (and everything I do is SQL Server based with linked tables into the mdb).
    http://www.dbforums.com/6274875-post31.html (to see who's using the SQL Server db)
    http://www.dbforums.com/6282465-post37.html (ODBC fixing/creating) - I think this is the same as the 1st link above.

    I've never had to change the connection time-out in any of the environments I used SQL Server (which has been many, many different environments). But you never know.

    You may also want to search this forum for ODBC problems. I recall responding to a problem similar to this many years ago.

    One last thing - I usually create all my tables as dbo_xxxxx in MSAccess first and then after upsizing, I don't need to change the name or any of my code. (I remove the dbo_ on SQL Server and then link them into MSAccess without again, having to change any coding. - although I doubt that this would be a sign for this type of problem.)

    I do also vaguely recall when we used daisy-chain hubs and had slow network cards, this was also problematic but again, not sure if this is anything that would be the cause of the problem you're describing.
    Last edited by pkstormy; 11-30-09 at 20:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Sep 2009
    Posts
    6

    ODBC Call Failed / Communication link failure

    Quote Originally Posted by pkstormy View Post
    I recall answering a similar post to this a while back.

    A couple of things I might try:

    1. Make sure all ODBC DSN's connecting to the tables are named EXACTLY the same name. When I had the same type of problem as you're describing, one of the users was opening the mdb but had a slightly different ODBC DSN Name. Once I refreshed the tables for that user, it worked for them but then started happening for the other users (and I refreshed their linked tables which then broke it for the other user, etc...etc...) (this of course drove me crazy until I finally realized 1 user had a slightly different ODBC name.)
    2. Run DBCC on SQL Server against the database (and other associated repair type commands.) (There's a couple of commands I recall running which also seemed to fix this type of problem - sorry but I can't recall what they were offhand but there were 2 other ones besides the dbcc.)
    3. Try adding a timestamp field type to the SQL Server table.
    4. Check that everyone is using the latest version of MDAC. Perhaps re-install on the problematic computers.
    5. Log into the problematic computer as yourself to see if it's computer based or user based.
    6. Run a trace on SQL Server to see if you can identify where the problem is stemming from. Traces are a great tool to use.

    Here are a couple of links from the code bank which may be helpful:
    http://www.dbforums.com/6274793-post22.html (ODBC fixing/creating) - I add this code to every mdb I create and it will automatically create/refresh the ODBC DSN when the user opens the mdb (providing the user has permissions to create ODBC's.) I've never had an issue with this code (and everything I do is SQL Server based with linked tables into the mdb).
    http://www.dbforums.com/6274875-post31.html (to see who's using the SQL Server db)
    http://www.dbforums.com/6282465-post37.html (ODBC fixing/creating) - I think this is the same as the 1st link above.

    I've never had to change the connection time-out in any of the environments I used SQL Server (which has been many, many different environments). But you never know.

    You may also want to search this forum for ODBC problems. I recall responding to a problem similar to this many years ago.

    One last thing - I usually create all my tables as dbo_xxxxx in MSAccess first and then after upsizing, I don't need to change the name or any of my code. (I remove the dbo_ on SQL Server and then link them into MSAccess without again, having to change any coding. - although I doubt that this would be a sign for this type of problem.)

    I do also vaguely recall when we used daisy-chain hubs and had slow network cards, this was also problematic but again, not sure if this is anything that would be the cause of the problem you're describing.
    I am not having any luck but my problems are very similar:

    I am running Access 2010 FE and SQL Server 2005 BE.
    I can execute pass through queries to my SQL Server succesfully by using DSNless connections.

    During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.

    Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection

    Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.

    Now there are two options on how to fix this problem, which in either case I find not USER Friendly.

    Restart my Access Application
    Wait approx 5-10 minutes to rerun the Pass Through Query

    I created a function to trap my ODBC Errors and this is what appears:
    ODBC Error Number: 0
    Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
    ODBC Error Number: 3146
    Error Description: ODBC--call failed.

    So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.

    Can anyone shed some advice on a solution? I appreciate any insight.

  13. #13
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by imchaz View Post
    I am not having any luck but my problems are very similar:

    I am running Access 2010 FE and SQL Server 2005 BE.
    I can execute pass through queries to my SQL Server succesfully by using DSNless connections.

    During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.

    Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection

    Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.

    Now there are two options on how to fix this problem, which in either case I find not USER Friendly.

    Restart my Access Application
    Wait approx 5-10 minutes to rerun the Pass Through Query

    I created a function to trap my ODBC Errors and this is what appears:
    ODBC Error Number: 0
    Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
    ODBC Error Number: 3146
    Error Description: ODBC--call failed.

    So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.

    Can anyone shed some advice on a solution? I appreciate any insight.
    Any ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •