Results 1 to 7 of 7

Thread: Odbc

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: Odbc

    Hi All, Little rusty here. I upsized an Access 2003 database to SQL Server 2008. Keeping Access 2003 frontend. There was a group created to access the database. Then there was an ODBC connection on the machine of each user and they use windows authentication. The users are able to open the table but cannot write to it. I went back to the database on SQL Server. Clicked the security tab under the database and found that the user group has dbo as default schema db owner under database role membership. Do I need to change this to db datawriter and db datareader? Also, do they need anything under the security tab under for the server login? Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, dbo access is more than sufficient to allow them to edit the data.
    Check the source queries for your forms. Perhaps they are not simple selects of the underlying data. For instance, if you simply open a small database table from Access (without using a form), can you edit the data?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Great. I'll check and post back. Thanks so much for your response.

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    ok. There is a box that pops up for a option to enter a password or check box to "Use Trusted Connection" If I put in a password; I can go on but when I try to check box; error:

    Code:
    Connect Failed Sql State: 28000 Sql Server Error: 18456 [Microsoft][ODBC Sql Server Driver][Sql Server] Login Failed for user .....
    I don't want to have the users to enter a password each time they want to access the database. How can I avoid this. I would like their logon to the frontend to be sufficient.
    Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There appears to be more than one problem based on your posts.

    Connectivity is one issue, and a lot depends on how much authority (permissions) you've given your users. Keep in mind that if you grant permissions at the AD user level, then your users can do whatever you give them permission to do from any application (including Excel, MS-Access, etc) as well as within your application. If you do this, I would strongly recommend that you create an AD group and place the users into that group rather than trying to maintain permissions for dozens or hundreds of users. SQL Server provides other ways to manage this security that may work better for you.

    A second issue is the problem that your application is having making changes to the database. Please capture the full error message and post it, because I think that the problem is in the code rather than in the permissions. If you don't include all of the columns of at least one unique key in your INSERT/UPDATE/DELETE statements, MS-Access can't change the data in the database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to either use SQL Server security (where the users connect via a sql server login and passord) or integrated security (where the user's network identity is used to validate their connection).
    You are apparently using SQL Server security.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    I found what I was doing wrong. When I linked the tables I should have checked the "remember password". Now the users do not get this popup. The database consists of only 3 users.
    Thanks so much for your responses.

Posting Permissions

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