locked
ODBC Error. MS Access to SQL.. RRS feed

  • Question

  • We have a an Access database with 5 tables linked to a SQL 2000 database.(stop laughing we are planning on upgrading it. :) 

    The account "loguser" is used to link tese tables with the sql server and on ODBC to the Database.  Testing the odbc and or refreshing the links works fine with no errors.  When I click on two of the tables I get an ODBC-Call Failed error.  The other three are fine. 

    If I grant the user SQL admin rights everything works fine. (all tables show, ODBC works and the table are relinked fine)

    This server is a dublicate of another server running sql and the databases, permissions etc are identical.. and that one works fine..

    Since the tables work fine when given SQL admin rights but not when it has the other rights does this mean its a permission problem?  Or should i look at something else?  If it is permissions how can I go about finding out what it is?

    Any help is appreciated.

    Thanks

    Moe

     


    Glad to be here
    Thursday, August 12, 2010 7:40 PM

Answers

  • Hi,

    Could you please elaborate a bit on your scenario? From you description, I think you created 5 SQL Server linked tables in an Access database and have connection problems with two of them, right? What’s the error message you received? Which version of Access are you using?

    If I grant the user SQL admin rights everything works fine. (all tables show, ODBC works and the table are relinked fine)

    Do you mean it works fine if you grant db_owner or System Administors to the login? I suggest you check if the current user has sufficient permissions on the two tables:

    If not, we can use the GRANT statement to grant the permissions.

    For more information, please see:

    Managing Users Permissions on SQL Server
    http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm

    GRANT
    http://msdn.microsoft.com/en-us/library/aa258909(SQL.80).aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, September 2, 2010 5:54 AM
    Monday, August 16, 2010 8:01 AM

All replies

  • Could you share the ODBC failure information? error code, message, etc.

    Is there any other difference between 2 servers? OS, language, etc.


    Shuhai Shen - I love programming, travel and photographing. Welcome to my blog: http://leonax.net
    Friday, August 13, 2010 6:39 AM
  • The error says ODBC CALL FAILED (error 3146).

    We have moved the databases over from one server to the other a few times but in this case everythign works but this.

    The servers are the same.. (just in different locations.)

    Thanks

    Moe


    Glad to be here
    Friday, August 13, 2010 1:28 PM
  • Hi,

    Could you please elaborate a bit on your scenario? From you description, I think you created 5 SQL Server linked tables in an Access database and have connection problems with two of them, right? What’s the error message you received? Which version of Access are you using?

    If I grant the user SQL admin rights everything works fine. (all tables show, ODBC works and the table are relinked fine)

    Do you mean it works fine if you grant db_owner or System Administors to the login? I suggest you check if the current user has sufficient permissions on the two tables:

    If not, we can use the GRANT statement to grant the permissions.

    For more information, please see:

    Managing Users Permissions on SQL Server
    http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm

    GRANT
    http://msdn.microsoft.com/en-us/library/aa258909(SQL.80).aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, September 2, 2010 5:54 AM
    Monday, August 16, 2010 8:01 AM