Results 1 to 4 of 4
  1. #1
    JAPA1972 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jan 2019
    Posts
    12

    Failure to call stored procedure

    I have created a stored procedure in SQL server called sp_MyProc which takes no parameters for the sake of simplicity. My Access DB talks to SQL server via an ODBC driver (DamProdSQL) whose parameters are shown below. I have created DamProdSQL with the windows wizard. All my tables in SQL server have been linked to Access via DamProdSQL. One of these tables is ‘DAM’ which I use to retrieve the connection properties. The below code will trigger the ‘ODBC call failed’ error. I am a dbo in SQL server as I am the administrator. What am I missing here? Thanks.


    Dim qdef As DAO.QueryDef

    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.Connect = CurrentDb.TableDefs("DAM").Connect ‘Dam is a table in SQL server which is linked to
    ‘Access by DamProdSQL
    qdef.SQL = "EXEC dbo.sp_MyProc "
    qdef.ReturnsRecords = False
    qdef.Execute ‘this triggers ‘ODBC call failed’ error


    ----------This is displayed by the windows wizard when I test the ODBC driver after creating it.

    Microsoft ODBC Driver for SQL Server Version 17.05.0001
    Data Source Name: DamProdSQL
    Data Source Description:
    Server: co-nt-dmn6
    Use Integrated Security: Yes
    Database: FLD
    Language: (Default)
    Data Encryption: No
    Trust Server Certificate: No
    Multiple Active Result Sets(MARS): No
    Mirror Server:
    Translate Character Data: Yes
    Log Long Running Queries: No


    Log Driver Statistics: No
    Use Regional Settings: No
    Use ANSI Quoted Identifiers: Yes
    Use ANSI Null, Paddings and Warnings: Yes


    -------------this is displayed by Debug.Print qdef.Connect -------------------------------------

    ODBC;DSN=DamProdSQL;UID=MyUserName;Trusted_Connect ion=Yes;APP=Microsoft Office;DATABASE=FLD;

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    859
    Can you link to the SQL tables using the ODBC link? Have you tried calling the SP with a pass-through query?

  3. #3
    ssanfu is online now Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,602
    In everything I have read, it says that system stored procedures are named with a "sp_" prefix. When you create a stored procedure, you should use a different name. I use "Usp_" as a prefix.


    You didn't say, but did you get "TESTS COMPLETED SUCCESSFULLY!" when you clicked the "Test Data Source" button?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,213
    Quote Originally Posted by ssanfu View Post
    In everything I have read, it says that system stored procedures are named with a "sp_" prefix. When you create a stored procedure, you should use a different name. I use "Usp_" as a prefix.
    This is true but they are stored under the sys. schema not dbo.

    eg. sys.sp_addrole
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 3
    Last Post: 10-09-2019, 10:26 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. ODBC Call Failure
    By askjacq in forum Database Design
    Replies: 3
    Last Post: 01-16-2013, 02:06 PM
  5. stored procedures failure
    By MrGrinch12 in forum Programming
    Replies: 1
    Last Post: 06-23-2010, 12:54 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