Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2009
    Posts
    10

    Unanswered: ODBC - Call Failed

    I'm a database noob, so please be gentle...

    I'm using Access 2003 to open files in Access 2000 file format. I'm using a System DSN set up some linked tables through the ODBC Data Source Administrator.

    The particular Access database that I need to use gives me "ODBC--call failed" each time I try to use it. Testing the connection through the ODBC data source administrator gives me "Connection successful" every time, and I have several other Access files using the same data source that work fine.

    I thought that maybe it would be a server timeout, but the timeout is set to 60 seconds, and the error comes up after 5 seconds. I tried recreating the linked tables, and that didn't seem to help. Any ideas?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Are the other Access files also running off of Access 2003 with a 2000 format?
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by nckdryr
    Are the other Access files also running off of Access 2003 with a 2000 format?
    Yes, I open the files with Access 2003 and the format on those files is Access 2000 as well.

    Other users of these files (there are 3 of us) are apparently having the same issue where the data source works for any file but these. I just really don't want to spend the time reimporting forms, queries, local tables, etc., but it looks like that might be what i have to do...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Could you check some properties of the attached tables and see if they are coherent?

    Code:
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.DataBase
        Dim i as integer
        
        Set dbs = CurrentDb
        For i = 0 To dbs.TableDefs.count - 1
            Set tdf = dbs.TableDefs(i)
            If Len(tdf.Connect) Then    
                Debug.Print tdf.Name
                Debug.Print, tdf.SourceTableName
                Debug.print, tdf.Connect
            End If
        Next i
        Set tdf = Nothing
        Set dbs = Nothing
    Have a nice day!

  5. #5
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by Sinndho
    Could you check some properties of the attached tables and see if they are coherent?

    Code:
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.DataBase
        Dim i as integer
        
        Set dbs = CurrentDb
        For i = 0 To dbs.TableDefs.count - 1
            Set tdf = dbs.TableDefs(i)
            If Len(tdf.Connect) Then    
                Debug.Print tdf.Name
                Debug.Print, tdf.SourceTableName
                Debug.print, tdf.Connect
            End If
        Next i
        Set tdf = Nothing
        Set dbs = Nothing
    Have a nice day!

    Forgive me for being an idiot, but what do I actually do with this? I put it into a macro and run it, but it doesn't seem to do anything...where does the debug print go to?

  6. #6
    Join Date
    Jul 2009
    Posts
    10

    Interesting...

    Not sure if this helps, but I tried creating a new database from scratch and adding the links to it and am getting this message:

    ODBC -- call failed.
    [Oracle][ODBC][Rdb]%SQL-F-SYNTAX_ERR, Syntax error (#-1)

    I checked and am getting this message when using the other databases that work to if I try to add links...

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by jlatham
    Forgive me for being an idiot, but what do I actually do with this? I put it into a macro and run it, but it doesn't seem to do anything...where does the debug print go to?
    Go to your Modules tab of the Access window, create a new module, paste Sinndho's code at the bottom of the new module. Press ctrl+G, this will bring up the "Immediate Window", this is where debug.print sends to.
    Me.Geek = True

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're not an idiot and there is no shame in asking, so no need to apologize.

    (Thanks Nick!)

  9. #9
    Join Date
    Jul 2009
    Posts
    3

    Convenient.

    I am searching google to find a solution to this problem myself right now.

    It's identical.

  10. #10
    Join Date
    Jul 2009
    Posts
    3
    I see that your code is returning information about my connection string.

    My ODBC connection is working fine. When I add a linked table and select my machine datasource it pulls up the complete selection of tables within the database(this leads me to believe the ODBC itself is fine and in working order.)

    I add my linked table.

    When I try to open it. I get ODBC--call failed.

    the table is filled with #NAME? in all fields. The call fails instantly. It's just executing a SELECT * FROM TABLE, no other query.

    The result set returns fine through MS SQL Enterprise Manager.

  11. #11
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by nckdryr
    Go to your Modules tab of the Access window, create a new module, paste Sinndho's code at the bottom of the new module. Press ctrl+G, this will bring up the "Immediate Window", this is where debug.print sends to.
    Thanks.

    Here's an example of what I get (with DataSourceName and ServerName replacing the name of the ODBC Data Source and the Server):

    DR_TABLE
    DR_TABLE
    ODBC;DSN=DataSourceName;SERVER=ServerName@generic@ 262779431@W;SERVER=ServerName;TLO=0;SVR=ServerName ;CLS=generic;DATABASE=attach 'filename dkch53k_com:[ch53k_com_source]dr_database';XPT=2=tcp/ip;CSO=1;

    I'm not sure I see any issues with it...

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Does your Server require a username and password? I don't see either in the connection string, but the server may be set up with user authorization as well.
    Me.Geek = True

  13. #13
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by nckdryr
    Does your Server require a username and password? I don't see either in the connection string, but the server may be set up with user authorization as well.
    Yes, it does. The username is configured as part of the ODBC data source, and the first time I try to use the data source in Access, it pops up a box and prompts me to enter a password (prior to the call failed error coming up).

    To preempt the next logical question, I know it's the correct password (it's the same one I use in the other Access file, and the back end is stored on a VAX machine that I also telnet into with the same username and password).

    In case it matters, the ODBC driver I'm using is SQRDB3.DLL, version 3.02.00.00...should I try using a different driver?

  14. #14
    Join Date
    Jul 2009
    Posts
    3

    Fixed!

    My issue was resolved.

    I created a view that accessed the same table.

    I linked to the view, it worked.

    I tried to add the same keys that were present on the table to the view. It failed.

    The issue was in the Keys being too complex for access to handle.

  15. #15
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by withermage
    My issue was resolved.

    I created a view that accessed the same table.

    I linked to the view, it worked.

    I tried to add the same keys that were present on the table to the view. It failed.

    The issue was in the Keys being too complex for access to handle.


    Hey, don't cloud my thread....I'm still stuck! Good for you, though.

Posting Permissions

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