Executing a stored procedure from Microsoft Access

[traveller4]

Could someone please help. I am looking for a connection string and execution code for executing a stored procedure on SQL Server from Microsoft Access linked database.

Thanks in advance

Micheal

 

[RobertT687]

Here's one (but certainly not the only) way:
Public Sub ExecSp()
Dim MyQuery As QueryDef
Dim CONNECTstr As String
Dim SqlStr As String

Set MyQuery = CurrentDb.CreateQueryDef("&quot

CONNECTstr = "ODBC;DSN=yourDSName;UID=yourUserId;PWD='yourpassword';LANGUAGE=us_english;DATABASE=yourdatabasename"
'
SqlStr = "EXECUTE yourdatabasename..YourStoredProcedureName"
'
With MyQuery
.Connect = CONNECTstr ' Must be the first parameter to signal ACCESS this is a PASS-THRU query
.SQL = SqlStr ' The string you just built
.ODBCTimeout = 0 ' No Timeouts
.ReturnsRecords = False ' This sp returns nothing
End With
MyQuery.Execute
End Sub

 

[traveller4]

Robert

This works great, Thanks

Could the Trusted connnection be substituted for UserID, PWD

Micheal

 

[SQLSister]

Set up a passthrough query. In the View, Properties tab you put in an OBDC connection string something like this:
ODBC;Description=DSI Inventory;DRIVER=SQL Server;SERVER=DEV02;UID=test;PWD=test;DATABASE=dsi
The query itself would be something like:
Execute USP_CustRptSectionsIUO '00004041802', 1

To change the parameters, we do it through the code on the form or in a module, thus rewriting the definition of the query like:

Dim db As Database
Dim qry As QueryDef
Dim strSQL As String
Dim numProposalNumber As String
Dim qrySections As QueryDef
Dim intReportNumber As Integer

Set db = CurrentDb()
If FormLoaded("frmClientReports&quot Then
intReportNumber = Forms!frmClientReports!cboReportList
Else
intReportNumber = 1
End If
numProposalNumber = Forms!frmProposal!subfrmProposalGeneral!ProposalNumber
strSQL = "Execute USP_CustRptSectionsIUO '" & numProposalNumber & "', " & intReportNumber & ""
Set qrySections = db.QueryDefs("ViewRptSectionsIUO&quot
qrySections.sql = strSQL

Hopethis helps.