CODEWITHSUNDEEP
sql-servervbams-accessstored-proceduresms-access-2010
Draco
Draco

Reputation: 71

SQL Server return table to MS Access via stored procedure

I'm trying to return a table back to MS Access that has 50+ columns and the rows can vary from 0 to 5000. For each case there can be multiple records and vehicle type.

I can execute the stored procedure and it works fine. I'm having trouble getting the data to return back to MS Access.

Stored procedure code:

ALTER PROCEDURE [dbo].[pJDB_Export] 
    (@dteFrom int, 
     @dteTo int,
     @Veh nvarchar(80))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @stWhere VARCHAR(200)

    SELECT DISTINCT [Case] 
    INTO #tmp 
    FROM data 
    WHERE [VEHICLE TYPE] = @Veh 
      AND (CY BETWEEN @dteFrom AND @dteTo)

    SELECT DISTINCT * 
    FROM dbo.vdata_Export_V3_3_2 v
    INNER JOIN #tmp t ON v.[CASE] = t.[CASE]

MS Access code:

Function Exec_pJDB_export(sqlConn as string)
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command

    Dim iFrom, iTo As Integer
    Dim stv As String
    iFrom = 1999
    iTo = 2002
    stv = "1 TO 2 TON TRUCKS (COMMERCIAL)"
    Set conn = New ADODB.Connection

    conn.Open "DSN=Cars"
    conn.ConnectionString = sqlConn
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "pJDB_export"

    cmd.Parameters.Append cmd.CreateParameter("@dteFrom", adInteger, adParamInput, , iFrom)
    cmd.Parameters.Append cmd.CreateParameter("@dteTo", adInteger, adParamInput, , iTo)
    cmd.Parameters.Append cmd.CreateParameter("@vehicle", adVarChar, adParamInput, 80, stv)

    cmd.Execute
    conn.Close

End Function

Upvotes: 1

Views: 1749

Answers (1)

David Rushton
David Rushton

Reputation: 5040

The execute method returns a recordset object. It is this object that contains your records. Here is example:

Function Exec_pJDB_export(sqlConn As String)
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset               ' ADO recordset object, for accessing records.

    Dim iFrom, iTo As Integer
    Dim stv As String
    iFrom = 1999
    iTo = 2002
    stv = "1 TO 2 TON TRUCKS (COMMERCIAL)"
    Set conn = New ADODB.Connection

    conn.Open "DSN=Cars"
    conn.ConnectionString = sqlConn
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "pJDB_export"

    cmd.Parameters.Append cmd.CreateParameter("@dteFrom", adInteger, adParamInput, , iFrom)
    cmd.Parameters.Append cmd.CreateParameter("@dteTo", adInteger, adParamInput, , iTo)
    cmd.Parameters.Append cmd.CreateParameter("@vehicle", adVarChar, adParamInput, 80, stv)

    ' This line has changed.
    Set rs = cmd.Execute()


    ' Loops over the records.
    Do Until rs.EOF

        ' Display the contents of column one to the user.
        MsgBox rs.Fields(0).Value

        rs.MoveNext
    Loop



    rs.Close
    conn.Close
End Function

Quick overview:

  1. EOF stands for End of File. It is true when you have viewed all records.
  2. Don't forget to call MoveNext, or the do loop will continue forever!

The line rs.Fields(0).Value can be changed to rs.Fields("YourFieldName").Value, if you prefer. I find it easier to refer to fields by name, rather than position.

Upvotes: 1

Related Questions