Hente recordset via stored procedure

do you mean a Stored Procedure in an SQL database or a query in the current dB?

a query in the current dB

If you mean a query then something like this should work

Dim rs As New ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
  set .ActiveConnection = CurrentProject.Connection
  .Open "SELECT * FROM TESTQuery"

  Do Until .EOF

    'Do what ever you want with each record

    .MoveNext
  Loop

End With
rs.Close

jeg ville bruge command objektet for at kunne overføre parameter i næste step

I'm not at all sure if you can use paramaters with on a query in ADO.

Can I see the SQL in the query "TESTQuery"?

I know you can use paramaters with DAO and queryDefs but this isnt the same as a stored procedure

Here is an example using a parameterised query but it still doeasnt use the command object

http://www.xefteri.com/articles/show.cfm?id=6

rs er slet ikke knyttet til cmd.

prøv:
set rs = cmd.execute

mvh Niels

eller:


Set GetRecordsetByStoredProcedure = cmd.Execute

så behøver du ikke dit rs-objekt

Hi Niels
I'm not sure what GetRecordsetByStoredProcedure is, can you give a bit more info?

Hi Terry - Godt nytår :)

Jeg tror at hans kode ligger i en function

function GetRecordsetByStoredProcedure() as recordset
......
end function

Og du kan godt bruge paramtre med command:

function GetRecordsetByStoredProcedure() as recordset
  Dim objPara As ADODB.Parameter
  'forgående kode
  Set objPara = cmd.CreateParameter("pId", adInteger, adParamInput, , 3)
  cmd.Parameters.Append objPara
  set CreateParameter = cmd.execute
end function

Tak for alle input. jeg er lidt forvirret nu. nih >> har du ikke et helt eksempel.

OK
en forsp ('Qry'):
  SELECT * FROM Tabel1 WHERE id=[pId];
pId er en parameter

et modul:
Function test(id As Integer) As ADODB.Recordset

    Dim cmd As New ADODB.Command
    Dim objPara As ADODB.Parameter

    Set objPara = cmd.CreateParameter("pId", adInteger, adParamInput, , id)

    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "Qry"
        .CommandType = adCmdStoredProc
        .Parameters.Append objPara
    End With
   
    Set test = cmd.Execute
End Function

Sub test1()
    Debug.Print test(2).Fields(1).Value
End Sub

Håber det hjælper lidt på forviringen :o)
Niels

det hjalp en hel del. Tak for hjælpen. Er alle glade hvis i deler point ?

If you use Niels solution then you should give him them all. Can we see the final code please?

glad for at kunne hjælpe :)

Så for nih alle points