Custom ADO Recordsets

The main reason to use your own ADO recordset is in situations where you already have the recordset available, and want to save some time by not creating a new one. You may also want to implement security schemes, customize the recordset properties, or use stored procedures.

To use your own ADO recordset, simply assign it to the Recordset property before you render the report. For example:

Example Title	Copy Code
Private Sub CreateReport() ' create connection and recordsource strings Dim sConn$, sRS$ sConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ "Persist Security Info=False;Initial Catalog=pubs" sRS = "byroyalty(40)" ' << stored procedure ' create ADO recordset object Dim rs As New ADODB.Recordset rs.Open "byroyalty(40)", sConn, adOpenKeyset ' load report vsr.Load "RepDef.xml", "My Report" ' render report vsr.Datasource.Recordset = rs vsr.Render vp ' done rs.Close Set rs = Nothing End Sub

Example Title

Copy Code

Private Sub CreateReport()

 

  ' create connection and recordsource strings

  Dim sConn$, sRS$

  sConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _

          "Persist Security Info=False;Initial Catalog=pubs"

  sRS = "byroyalty(40)" ' << stored procedure

 

  ' create ADO recordset object

  Dim rs As New ADODB.Recordset

  rs.Open "byroyalty(40)", sConn, adOpenKeyset

 

  ' load report

  vsr.Load "RepDef.xml", "My Report"

 

  ' render report

  vsr.Datasource.Recordset = rs

  vsr.Render vp

 

  ' done

  rs.Close

  Set rs = Nothing

 

End Sub

The code above opens a recordset using the "byroyalty" stored procedure in the "pubs" database that ships with SQLServer. Note that you could open this recordset using the ConnectionString and RecordSource properties directly.

Note that you can also create and populate custom ADO recordsets on the fly, using code. For example:

Example Title	Copy Code
Private Function MakeADORS() As ADODB.Recordset ' create empty recordset Dim rs As New ADODB.Recordset ' add some fields to it rs.Fields.Append "ID", adInteger rs.Fields.Append "FirstName", adBSTR, 100 rs.Fields.Append "LastName", adBSTR, 100 ' populate new recordset Dim i% rs.Open For i = 0 To 1000 rs.AddNew rs.Fields("ID") = I rs.Fields("FirstName") = "John " & I rs.Fields("LastName") = "Doe " & I Next ' done, return it Set MakeADORS = rs End Sub

Example Title

Copy Code

Private Function MakeADORS() As ADODB.Recordset

 

  ' create empty recordset

  Dim rs As New ADODB.Recordset

 

  ' add some fields to it

  rs.Fields.Append "ID", adInteger

  rs.Fields.Append "FirstName", adBSTR, 100

  rs.Fields.Append "LastName", adBSTR, 100

 

  ' populate new recordset

  Dim i%

  rs.Open

  For i = 0 To 1000

    rs.AddNew

    rs.Fields("ID") = I

    rs.Fields("FirstName") = "John " & I

    rs.Fields("LastName") = "Doe " & I

  Next

 

  ' done, return it

  Set MakeADORS = rs

 

End Sub