Results 1 to 4 of 4
  1. #1
    Misjel is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Belgium
    Posts
    9

    Call stored procedure in SSMS from Access 2016 doesn't work.

    Hi,

    When I execute the code below then I get an error.
    Function Get_Recordset_Customer(CustomerID)
    Dim cn As ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim paramx As ADODB.Parameter

    Set cn = CurrentProject.AccessConnection
    Set paramx = cmd.CreateParameter("@CustomerID", adInteger, adParamInput)
    paramxValue = CustomerID
    cmd.Parameters.Append paramx

    With cmd
    .ActiveConnection = cn
    .CommandText = "sp_Customer"
    .CommandType = adCmdStoredProc
    Set Get_Recordset_Customer = .Execute
    End With
    End Function
    The stored procedure "sp_Customer" is present in SQL Server Management Studio.

    The message i get is :
    "The Microsoft Access database engine cannot find the input table or query 'sp_Customer'. Make sure it exists and that its name is spelled correctly".

    The initial database in which this code was developed was an Access-adp database and everything worked perfectly there.

    I can access my tables in SSMS via Access. This is no problem.
    This instruction is wrong : Set Get_Recordset_Customer = .Execute , because this lines works in adp but not in accdb.


    That's why I created a query in my Access Database with the same name as the Stored Procedure in SSMS and then my code works. So no connection is made with SSMS and I can't find why. I have already tried many scenarios but none of them have succeeded.
    My code is a call to the Stored Procedure in SSMS with a parameter and places it (always 1 record) in the record set of a subForm in Access :
    Set Me! FrmAnalyse_Analyse_List.Form.Recordset = Get_Recordset_Customer(Me! CustomerID)
    What am I doing wrong.
    Tx

  2. #2
    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 don't use ADO much but I think your connection variable needs to be set to a connection string pointing to the SQL Server instance.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Misjel is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Belgium
    Posts
    9
    Hello pbaldy,
    I am a beginner. Is there another way to get this connection?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your tables are probably linked using a similar connection. I use a constant:

    Public Const dbConnectionString As String = "DRIVER=SQL Server Native Client 10.0;SERVER=sqlsrv01;Trusted_Connection=Yes;DATABA SE=CabDispatch"

    Good resource:

    https://www.connectionstrings.com/
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 08-07-2018, 05:38 AM
  2. Stored Procedure in MS SQL Server from Access
    By jaryszek in forum Access
    Replies: 7
    Last Post: 01-17-2018, 05:58 AM
  3. Stored Procedure in MS Access 2007
    By sels1987 in forum Access
    Replies: 1
    Last Post: 05-13-2012, 12:23 PM
  4. Replies: 7
    Last Post: 01-19-2011, 10:39 AM
  5. Access 2007 doesn't show Stored Procedure option
    By DistillingAccess in forum Programming
    Replies: 0
    Last Post: 08-03-2010, 03:19 AM

Tags for this Thread

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