Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: ODBC - Call Failed. User doesn't have permissions.

    I've upsized my database to an SQL server, and now that's have created an ODBC connection, I'm trying to link my .adp with the DB on the SQL server. While it seems of the importing goes okay, I get this error multiple times before the import freezes and crashes Access.

    ODBC - Call Failed.

    [Microsoft][ODBC SQL Server Driver][SQL Server] The User does not have permission to perform this action. (#297)

    Normally I figure this would be an understandable issue for a normal user. That being said, I'm set up as DBO and have wide open access to the database. I'm thinking I need to have some access to the server, anyone have any ideas?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to setup permissions ON SQL Server first, adding in your login to the Security tab. Make sure to give you full access/permissions. You should try to use Mixed authenticiation (windows or SQL Server) when configuring your ODBC. When you installed SQL Server, if you selected only the SQL Server option, you may want to change it to mixed. Otherwise, if you use SQL Server only, you will need to supply the SA password.

    Again, make sure your permissions have "everything" checked included all the permissions for full control over the specific database. It's a 2 step process: 1 establish your login to SQL Server itself. 2. Establish your login (now in SQL Server) to the specific database (giving full control - ie. check everything you can for permissions.)

    Then test the ODBC configuration (the last step is test connection). If that works ok, you should be good to go.

    Lastly, make sure you always use the EXACT same name when connecting to the same specific database. Don't call the ODBC connection XYZ on 1 machine and then ZYX on another machine or you'll pull your hair out trying to figure out what's wrong.

    There are some SQL Server utilities in the code bank such as automatically creating the ODBC DSN and other code snippets you may want to browse, download and look at.
    Last edited by pkstormy; 11-21-08 at 04:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2008
    Posts
    35
    Thank you! I was worried that no one was goin to answer. From the DB side, I have all privledges, however I have none on the actual server side, I didn't set it up, and my employer doesn't want to consider me IT, so because of that, I don't have any permissions at all on the server. I'll pass your reply on to the Sys. Admin. and make him change it. Thank you again!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also...

    "Again, make sure your permissions have "everything" checked included all the permissions for full control over the specific database. It's a 2 step process: 1 establish your login to SQL Server itself. 2. Establish your login (now in SQL Server) to the specific database (giving full control - ie. check everything you can for permissions.)"

    I should've added....

    And then work your way backwards removing permissions and testing until you find the specific permissions you need for that database.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2008
    Posts
    35
    One more question, how do you write a query that asks for input in SQL? I've never done anything involving SQL, so I'm lost as to what to do. I know in Access you could type "like [something]", but I'm unaware of the SQL equivilent.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by punkn00dlez
    One more question, how do you write a query that asks for input in SQL? I've never done anything involving SQL, so I'm lost as to what to do. I know in Access you could type "like [something]", but I'm unaware of the SQL equivilent.
    Not sure what you mean here. Designing queries in MSAccess is using SQL. Perhaps if you go to query designer (in MSAccess), design your query with the criteria, and then in the upper left corner, change it from Design View to SQL view. That is your SQL statement.

    Keep in mind that if you're trying to do SQL in SQL Server versus MSAccess, the syntax is a tad different. What do you mean by SQL? Are you trying to do something in SQL Server or MSAccess?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The two big differences in syntax for using a LIKE operator are as follows:

    The text delimiter is a single quote instead of a double quote. So you'll be searching for 'a word' as opposed to "a word"

    The wildcard character is a % instead of a *. So you'll be searching for 'words that start with w%' instead of "words that start with w*"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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