Append Records From Stored Procedure on Sql Serve to an Access Table

[jcmv007]

Using Windows XP SP1, Access 2002

I need to run from an Access DB a Stored Procedure found on SQL SERVER and append the results in a table in that same Access DB. The stored procedure needs to be supplied with the following parameters User, BeginYear, EndYear; these are determined by the user login in the Access DB.

 

[jcmv007]

Do I need to refrase question or give more details?

 

[PHV]

Have you tried a keyword search in this forum for stored procedure ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbajock]

Dim CUSTLIST As ADODB.Recordset ' define a ado recordset
Dim STCMD As ADODB.Command ' define a ado command object
Dim SQLcountry As ADODB.Parameter ' define a ado parameter


Set STCMD = New ADODB.Command 'set the reference to STCMD

STCMD.CommandText = "sp_Returncustomerbycountry" ' The procedure to execute
STCMD.CommandType = adCmdStoredProc ' set the command type

' Lets set up the parameters to use for this stored procedure call
' For this example , we will make one parameter , a input , of type advarchar
' that can be 15 characters long, the parameter internal name must match
' the stored procedure's varibles both in name and the type ,
' and also if you have multiple parameters , they must
' be defined in the same order, notice that the parameter name "country"
' matches the stored procedure variable.
SET SQLcountry = STCMD.CreateParameter("Country",advarChar,adParamInput,15)
STCMD.Parameters.Append SQLcountry
SQLcountry.Value = "USA" ' Set the input parameter value to USA

STCMD.ActiveConnection = SQLCON ' set the active connection to our global ADO connection

SET CUSTLIST = STCMD.Execute ' lets open a ADO recordset from the stored procedure results
IF NOT CUSTLIST.EOF THEN 'If we have some records , lets do something with them
' Lets just display all the customers names that were returned , and the country too
DO WHILE NOT CUSTLIST.EOF ' Lets loop till we get all of them
LIST1.ADDITEM CUSTLIST("Companyname") & CUSTLIST("Country")
CUSTLIST.MOVENEXT ' Move to the next record
LOOP
ELSE ' There were no records found
MSGBOX "No Records Found !"
END IF
'Now we are done with this record set lets close and destroy ..
CUSTLIST.CLOSE
SET CUSTLIST = NOTHING
SET STCMD = NOTHING

 

[jcmv007]

HEllo.

I did search but the code was a bit confusing. Even thou I worked it out to the following code, but I get the error "Operation is no allowed when the object is closed" if the following line of the code: MsgBox "Cantidad registros " & rstSource.RecordCount

Here is the code that I'm using:

Private Sub addDatosDeGastos()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim varParam As Variant
    Dim rstSource As ADODB.Recordset
    Dim rstDestino As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    
    cnn.Open "Provider=SQLOLEDB;Data Source=HACHE-APPS1;" & _
    "Database=REPLICAMAC;User Id=dbsa;Password=hache"
    
    Set cmd = New ADODB.Command
    
    cmd.ActiveConnection = cnn
    cmd.CommandText = "sp_DatosParaSistemaAPH" 'stored procedure name
    cmd.CommandType = adCmdStoredProc
    cmd.CommandTimeout = 2000
    'set stored procedure parameters
     'Set prm = Nothing
    
    'Me.txtUser
    Set prm = cmd.CreateParameter("User", adInteger, adParamInput, 4)
    cmd.Parameters.Append prm
    prm.Value = 598
    
    'Me.txtAnoD  Year From
    Set prm = cmd.CreateParameter("AnoDesde", adInteger, adParamInput, 4)
    cmd.Parameters.Append prm
    prm.Value = 2003
    
    ' Me.txtAnoH  Year To
    Set prm = cmd.CreateParameter("AnoHasta", adInteger, adParamInput, 4)
    cmd.Parameters.Append prm
    prm.Value = 2004

    
    'create a recordset
    Set rstSource = cmd.Execute()
    MsgBox "Cantidad registros " & rstSource.RecordCount
    Debug.Print rstSource.Fields(0)
    Debug.Print rstSource.Fields(1)
    
    'Open destination recordset
    Set rstDestino = New ADODB.Recordset
    rstDestino.Open "tblDatosDeGastos", CurrentProject.Connection, , , adCmdTable
    
    Do While Not rstSource.EOF
        rstDestino.AddNew
            rstDestino!UnidadNegocio = rstSource!UnidadNegocio
            rstDestino!GrupoCtas_ID = rstSource!GrupoCuentas_ID
            rstDestino!Cuenta_ID = rstSource!Cuenta_ID
            rstDestino!Cuenta = rstSource!Cuenta
            rstDestino!Dpto_ID = rstSource!Dpto_ID
            rstDestino!Centro = rstSource!Centro
            rstDestino!Departamento = rstSource!Departamento
            rstDestino!Ano = rstSource!Ano
            rstDestino!Mes = rstSource!Mes
            rstDestino!Monto = rstSource!Monto
            rstDestino!Origen = rstSource!Origen
        rstDestino.Update
        rstSource.MoveNext
    Loop
    
    ' rstSource.Close
    ' rstDestino.Close
    ' Set rstSource = Nothing
    ' Set rstDestino = Nothing
    Set prm = Nothing
    Set cmd = Nothing
    Set cnn = Nothing

End Sub

 

[cmmrfrds]

'create a recordset
Set rstSource = cmd.Execute()
MsgBox "Cantidad registros " & rstSource.RecordCount
Debug.Print rstSource.Fields(0)
Debug.Print rstSource.Fields(1)

Looking at the above code, here are some considerations. Whether the recordcount is populated depends on the cursor settings. Need Cursor location of client, Static and scrollable.

The code rstSource.Fields(0) will error if a recordset was not returned. Check for EOF.
If rstSource.EOF AND rstSource.BOF then
MsgBox "No Records returned"
Exit Sub or Function
End If

 

[jcmv007]

cmmrfrds
I get the error "Operation is no allowed when the object is closed" and the debug window points to the line of the code: If rstSource.EOF AND rstSource.BOF then

 

[cmmrfrds]

What this means is that the stored procedure did not return a recordset. I thought checking for the EOF would catch it.

I don't see where you made an instance of the recordset.
Set rstSource = New ADODB.Recordset
This probably causes it to be closed even without returning the recordset.

 

[jcmv007]

Thanks for all your help! Found in another post the answer to my problem! I had to set in the Stored Procedure 'SET NOCOUNTS ON' so the recordset could get populated.

 

[cmmrfrds]

The "set nocount on" stops informational messages from being sent back from the stored procedure as recordsets. So what was probably happening was that the recordset with the information you are looking for was not the first recordset sent back, baut was a message such as that a record was inserted or some other action in the stored procedure. If for some reason you need to read all the recordsets sent back from a stored procedure then the Open Method of the recordset object can do this with the NextRecordset Method.

 

[scking]

Just a couple observations. You always, always need to consider how to have your code breakproof; avoiding or handling all cases of potential errors.

1) Use error trapping. Set a breakpoint on GoTo Exit_Proc, skip to resume, and press F8 will bring you code pointer directly to the line that cased the error, unless you don't have error trapping on call procedures.
2) Use debugging to trap and troubleshoot in the immediate window
3) Always check for a valid recordset. 'If Not rst.EOF Then' or 'If Not rst.BOF And Not rst.EOF Then' and then process the code.

On Error GoTo HandleErr

... code
Exit_Proc:
Exit Function

HandleErr:
Select Case Err.Number
Case 6 ' overflow
MsgBox "Overflow error"
GoTo Exit_Proc
Case 7 to 95
' Whatever
GoTo Exit_Proc
Case Else
' Whatever again
GoTo Exit_Proc
End Select
GoTo Exit_Proc
Resume
Exit Function

---------------------
scking@arinc.com
---------------------