Hi,

   I am trying to retrieve data from a SQL Server stored precedure, and I want to pass two parameters to it, and then dump the data into a local Access table as needed. I have the following code, and the sp works fine, but how would you suggest getting the data? Is this code otherwise correct? The Access database is not connected to the SQL Server as either an adp or with linked sp.

Thanks in advance.


Dim cn As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim prmStart As ADODB.Parameter
Dim prmEnd As ADODB.Parameter
Dim dtStart As Date
Dim dtEnd As Date

'There will be code to make sure a selection has been made here:
dtStart = Me.cboStartDate
dtEnd = Me.cboEndDate

Set cn = New ADODB.Connection
'maybe a with block her for client side cursor?
cn.Open "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=AAA;UID=username;PWD=password"

Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn

cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "sp_ContribRep"

Set prmStart = cmdCommand.CreateParameter("@startdate", adDate, adParamInput)
Set prmEnd = cmdCommand.CreateParameter("@enddate", adDate, adParamInput)

prmStart.Value = dtStart
prmEnd.Value = dtEnd
cmdCommand.Parameters.Append prmStart
cmdCommand.Parameters.Append prmEnd

cmdCommand.Execute
'where to next?


mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

mmcdonal

The code looks good to me.

You should be able to assign the results of executing the command object to a recordset, i.e.:

    Set rstResult=cmdCommand.Execute

You can then loop through the recordset in the usual manner and transfer the records to a local table.

Braxis

Thanks, so that would look like this?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim prmStart As ADODB.Parameter
Dim prmEnd As ADODB.Parameter

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=saw2k3sr01;Initial Catalog=AAA;UID=ft_test;PWD=test_ft1"

Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn

cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "sp_StoredProcedureName" 'what is the stored procedure name you are creating?

Set prmStart = cmdCommand.CreateParameter("@startdate", adDate, adParamInput)
Set prmEnd = cmdCommand.CreateParameter("@enddate", adDate, adParamInput)

prmStart.Value = dtStart
prmEnd.Value = dtEnd
cmdCommand.Parameters.Append prmStart
cmdCommand.Parameters.Append prmEnd

Set rs = cmdCommand.Execute

mmcdonal

I also found this answer in the Access 2007 VBA Programmer's Reference just now on wrox.books24x7.com. Dang, I should use that more effectively. Thanks loads for you help.

mmcdonal