Running a stored procedure from Access Front end

[lone_rider15]

Hello everyone,

I have an Access Application with SQL server as back end. I am trying to call a stored procedure from Access. Below is my code.

Private Sub AssignReview()
    Dim rst As Object
    Set rst = Me.RecordsetClone
    Me.Requery
    If rst.EOF Or rst.RecordCount = 0 Then
        DoCmd.SetWarnings False
        With CurrentDb.QueryDefs("qryMyPass")
            .SQL = "EXEC spAddressMissingQue'" & [Forms]![frmLogin]![txtUserId] & "'"
            .Execute
        End With
        DoCmd.SetWarnings True
        Me.Requery
    End If
End Sub

"qryMyPass" pass through query runs fine. "spAddressMissingQue" runs perfectly in SQL. The code fails and highlights in ".Execute".
Hope someone can help me with this.

 

[JHB]

Do you not get an error message?
Are you sure that [txtUserId] is a text field type in your table?
If it is number then:

..issingQue" & [Forms]![frmLogin]![txtUserId]

 

[lone_rider15]

Do you not get an error message?
Are you sure that [txtUserId] is a text field type in your table?
If it is number then:

..issingQue" & [Forms]![frmLogin]![txtUserId]

The error message is -

Run-time error '3146':
ODBC --call failed.
Then I click "Debug" and ".Execute" gets highlighted.
txtUserId is a text field type in my table.

Thanks for looking into this.

 

[Frothingslosh]

Your generated SQL is missing a space after spAddressMissingQue.

Let's say the UserID in question is abc123. The SQL statement you're sending is:

EXEC spAddressMissingQueabc123

It's failing at .Execute because SQL server is returning an error due to the invalid stored procedure name.

As a rule, any time you're having an issue with constructed SQL statements, even at a remove or two, you should debug.print the completed SQL - that will typically allow you to catch things like missing spaces immediately.

 

[lone_rider15]

I have added the space.

        With CurrentDb.QueryDefs("qryMyPass")
            .SQL = "EXEC spAddressMissingQue '" & [Forms]![frmLogin]![txtUserId] & "'"
            .Execute
        End With

But still no luck. Same error.

 

[Minty]

You missed the bit about it being a number in post 2 - keep the space in but remove the quotes; (assuming txtUserID is a number)

Code:
        With CurrentDb.QueryDefs("qryMyPass")
            .SQL = "EXEC spAddressMissingQue " & [Forms]![frmLogin]![txtUserId] & ""
            .Execute
        End With

 

[static]

I'd check that returnsrecords is false.

 

[Frothingslosh]

I'd check that returnsrecords is false.

I always forget that part.

 

[Rx_]

Sorry for the late response. A Table Valued Function on SQL Server Stored Procedure runs a lengthy query and repopulates a table. The table is used by Access Lined Tables to create reports or other functions.
Before creating the Budget report, run the SP.

Private Sub cmdRunReports_Click()
    DoCmd.Hourglass True
        SP_RefreshSQLserver
        DoEvents
        lblStatus.Caption = "Please wait while reports run"
        CmdOpenExcelTemplateCreateBudgetReport
        DoCmd.Hourglass False
End Sub

In a public module:

Public Sub SP_RefreshsQLserver()
Dim qdef As DAO.QueryDef
' Runs stored procedure to update data for sort order and record inclusion
    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.Connect = CurrentDb.TableDefs("[dbo_VW_BudgetMockupTabBus_Full Year]").Connect
    'qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect  ' be sure to include the dbo. in front of SP and tables
    qdef.SQL = "EXEC SP_FillBudgetMockup"
    qdef.ReturnsRecords = False
    qdef.Execute
    'Debug.Print Err.Description
End Sub

Now, a function is used as there are so many TBL SP used

Public Sub SP_RefreshsQLserver1(SPName As String, LinkedTableName As String)
Dim qdef As DAO.QueryDef
' Runs stored procedure to update data for sort order and record inclusion
    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.Connect = CurrentDb.TableDefs(LinkedTableName).Connect
    qdef.SQL = "EXEC " & SPName
    qdef.ReturnsRecords = False
    qdef.Execute
    'Debug.Print Err.Description
End Sub

called with
SP_RefreshsQLserver1 "dbo.SP_AlterViews_EBaseUpload_ForIncludedOrgs", "dbo_VW_EBaseUploadBusinessNameofTBV"

 

[Frothingslosh]

Rx - wrong thread?

 

[Rx_]

Probably...
It is a SP run from an Access front-end
We are in an environment with combined slow network, sharing an extremely under-resourced virtual server and running large T-SQL queries to return a recordset that is can be reused for several MSAccess Linked Table calls. The Query was running around 20 seconds per call (for multiple calls).
Instead, a Table Valued Function is called by Access to repopulate a SQL table that Linked Table by Access. The execution time to repopulate the table is shorter, and the multiple data retrievals for massive report generation is gained.
The SP is run at the beginning of the application opening to refresh the table on SQL once.
Should someone need to call a SQL Server SP for a TLF on SQL, this works great.
Or, perhaps there should be another place to discuss this more unique SP.
- cheers