Results 1 to 6 of 6
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    pass through query for stored procedure wont run

    I have a linked SQL table and I moved it from a sql server that was local to the machine access was running on to a stand alone sql server.

    Since then my stored procedures wont run. The procedure runs if I run in management studio but doesnt when I run it from access. I am using the same system DSN to connect the query to the database that i used to connect to the table.

    I can see the data in the table but not run the stored procedure.

    Is there some special permissions I need to give or something?

  2. #2
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Update, it is clearly a permissions issue because I created a new user DSN and the pass through worked. However, I don't want to have to create a new user DSN for each person that may end up running this query so I need some help getting a system DSN to work.

    Thanks

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I use DSN-less code now but previously used a File DSN stored on the SQL Server. That meant I didn't have to visit each user's computer.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I didn't want to use file because I used my admin credentials to create the connection and I was afraid it would be unsecure. Yesterday, I did go ahead and use the file dsn and it did work fine. I don't know how secure that is though.

    I also read that using a file dsn is basically using a dsn less connection because that is how it creates it.

    I looked up how to do a dsnless connection and it seemed a bit complex.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not aware of a security issue with a file DSN, but it wouldn't be the first thing I was ignorant of.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Only issue I could see is that the password is shown when you open the dsn as a txt file, but normally that isn't the case. However, never use your administrator account to create a dsn. Create a separate login for that application on your database server and a user on the database. Give them the rights they should have (don't forget to include the right to execute procedures). Be very careful with your admin account, don't forget it has full rights on all databases, including the system databases.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Text Box Value To SQL Server Stored Procedure
    By Juan4412 in forum Programming
    Replies: 1
    Last Post: 04-11-2017, 06:58 AM
  2. Help with SQL stored procedure
    By emmahope206 in forum Access
    Replies: 1
    Last Post: 05-24-2016, 05:03 PM
  3. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  4. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  5. Pass image parameter to stored procedure
    By Kencao in forum Programming
    Replies: 3
    Last Post: 04-28-2010, 11:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums