call a stored procedure..?

[Kujen]

Hi all,

How can I retrive records from a SQL stored procedure in a Access form?
I have this code:
=========================
'Sets values
Set cnSQL = New ADODB.Connection
Set cmd = New ADODB.Command

'Opens a connection to the SQL server
cnSQL.Open _
"Provider= sqloledb;Driver=SQL Server;" & _
"Server=Labserver2;" & _
"Database=Kunddata;UID=sa;PWD=MyPwd"

With cmd

.ActiveConnection = cnSQL
.CommandText = "sp_Mosaic_Analys"
.CommandType = adCmdStoredProc
.ReturnsRecords = True
.Execute

'Closes and resets the recordset
cnSQL.Close
Set cmd = Nothing
=======================
But how can I get the result to a Access form?
Shouldn't it be somewhere the name of a pass-through query?

Kent J.

 

[genomon]

An easy way is to have the sp modify the contents of a linked SQL Server table, or link in a view.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[JoeAtWork]

Here's some code that sets the result of a stored procedure to a listbox. Possibly you could do the same to the form's recordset (though I haven't tried, so can't guarantee you it will work). It would pretty much have to be a read-only recordset, I would say.

Private Function Search_OrderList_SQL(Optional Criteria As String = "")
    Dim dteTEMP As Date
    Dim strRowSource As String
    Dim strSearchFor As String
    Dim WHERE As String
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim adoParam As ADODB.Parameter
    Dim rsList As ADODB.Recordset
    
On Error GoTo ErrHandler:
    
    HourglassMouse
    
    
    strSearchFor = Trim(Criteria)
    If Len(strSearchFor) = 0 Then
        strSearchFor = Trim(Nz(txtOrderSearch, ""))
    End If

    Set adoConn = New ADODB.Connection
    adoConn.Open GetSQL2005ConnString
    
    Set adoCmd = New ADODB.Command
    adoCmd.ActiveConnection = adoConn
    
    adoCmd.CommandText = "GetInventoryList"
    adoCmd.CommandType = adCmdStoredProc

    adoCmd.Parameters("@ProductSearch").Value = strSearchFor
    adoCmd.Parameters("@SupplierID").Value = Nz(cboOrderSupplier, 0)
    adoCmd.Parameters("@ShowBelow").Value = Nz(chkBelow, False)
    adoCmd.Parameters("@ShowOpen").Value = Nz(chkOpen, False)
    
    Set rsList = New ADODB.Recordset
    
    rsList.CursorLocation = adUseClient
    
    rsList.Open adoCmd, , , adLockReadOnly
    
    Set Me.lstInvOrder.Recordset = rsList
    
    adoConn.Close
    Set adoConn = Nothing
    Set adoCmd = Nothing
    
    'Only allow the "Generate All Required Orders" to be clicked if
    'a single supplier's orders are being viewed
    cmdGenerateAllOrders.Enabled = (Nz(cboOrderSupplier, 0) <> 0)
    
    'Unselect all rows currently selected
    SelectAll False

ExitRoutine:
    DefaultMouse
    Exit Function
    
ErrHandler:
    ReportError Err, Err.Description, "While filtering"
    GoTo ExitRoutine
   
End Function

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Kujen]

Hi all,

This how I solved the problem:
==================================
Dim qdfPassthrough As DAO.QueryDef

Set qdfPassthrough = CurrentDb.QueryDefs("QueryGeneric")
qdfPassthrough.SQL = "exec MyStoredProc " & Parameter1
DoCmd.OpenQuery ("QueryGeneric")
====================================
Just four rows of code.

Kent J.

 

[Qik3Coder]

Kujen, where do you create the CurrentDB object? As well, where do you create the DoCmd object?

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[PHV]

CurrentDB and DoCmd are method/property of the Access.Application object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Qik3Coder]

I normally work with .Net code, so I am trying to piece together how to hook to an SQL database and run some dynamic select SQL.

What would i expect to be in the "QueryGeneric" object?

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[RoyVidar]

Qik3Coder:

Where do you wish the code to run?

To run it on the server, you may use either DAO or ADO.

For DAO, you need a querydef object of the Pass-Through kind or type (which is most likely what "QueryGeneric" is), then alter the SQL of it (qdfPassthrough.SQL = "exec MyStoredProc " & Parameter1).

If you use ADO, you can use a command object as demonstrated above - see perhaps also ChipH's faq faq709-1526 on how to avoid SQL Injection, too.

This thread thread181-1486224, gives some info/details on both methods, a bit of search should also reveal more.

Easier though, is to just link the tables, and allow the client to do the processing. As long as your indices are correct/good, the Jet engine will retrieve only the necessary records to perform the operations, also for dynamic SQL.

Might also bee that asking these questions in a separate thread would have been better?

Roy-Vidar