Sending a Data Recordset in a Message

Data recordsets can be sent and retrieved using a combination of MSMQ and Microsoft Remote Data Service (RDS).

Note  RDS is used to transport Active Data Object recordsets from a server to a client computer. The resulting recordset is cached on the client computer and disconnected from the server.

    To send a data recordset in a message

1. Make sure RDS and Microsoft SQL Server are installed on your computer. (This example was tested using RDS 1.5 and SQL 6.5.)
2. Make sure SQL Server services has been stated.
3. Add references to the appropriate object libraries in your Microsoft Visual Basic project. This example was tested with the following libraries referenced.
   • Microsoft ActiveX Data Objects 1.5 Object Library
   • Microsoft Remote Data Service Object Library
4. Create a destination queue and open it with send access.

   qinfo.PathName = ".\RecordsetQueue2"
   qinfo.Label = "My Recordset Queue"
   On Error Resume Next               'Ignore if queue already exists.
   qinfo.Create
   On Error Goto 0                    'Reset error handler.
   Set qSend = qinfo.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
    

5. Create the recordset.

   sql = "select * from authors"
   Set rsSend = dataFactory.Query("driver={SQL Server};server=.;database=pubs;uid=sa;pwd=", sql)
    

6. Create and send a message. Set the body of the message to the ADO recordset.

   mSend.Label = "Testing Recordset"
   mSend.Body = rsSend
   mSend.Send qSend
   qSend.Close
    

7. Open the queue with receive access and retrieve the message.

   Set qReceive = qinfo.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
   Set mReceive = qReceive.Receive
    

8. Create a new recordset using the body of the retrieved message. The example below prints out the content of the recordset to confirm that the data was sent and retrieved.

   Set rsReceive = mReceive.Body
    

Example Code

The following example creates an ADO recordset, sends the message to a destination queue, and retrieves the message and places it in a new recordset.

Option Explicit

Dim rsSend As ADODB.Recordset
Dim rsReceive As ADODB.Recordset
Dim dataFactory As New RDSServer.DataFactory
Dim sql As String

Dim qinfo As New MSMQQueueInfo
Dim qSend As MSMQQueue
Dim qReceive As MSMQQueue
Dim mSend As New MSMQMessage
Dim mReceive As MSMQMessage


Private Sub Form_Click()
  
  '*******************************************************************
  ' Create a destination queue and open it with SEND access.
  '*******************************************************************
  qinfo.PathName = ".\RecordsetQueue2"
  qinfo.Label = "My Recordset Queue"
  On Error Resume Next                'Ignore if queue already exists.
  qinfo.Create
  On Error Goto 0                     'Reset error handler.
  Set qSend = qinfo.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
  
  '*******************************************************************
  ' Create an ADO recordset.
  '*******************************************************************
  sql = "select * from authors"
  Set rsSend = dataFactory.Query("driver={SQL Server};server=.;database=pubs;uid=sa;pwd=", sql)
  
  '*******************************************************************
  ' Create a message and send it to the destination queue. Set the 
  ' body of the message to the ADO recordset.
  '*******************************************************************
  mSend.Label = "Testing Recordset"
  mSend.Body = rsSend
  mSend.Send qSend
  qSend.Close
 
  '*******************************************************************
  ' Open the destination queue with RECEIVE access, and retrieve the
  ' message.
  '*******************************************************************
  Set qReceive = qinfo.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
  Set mReceive = qReceive.Receive
 
  '*******************************************************************
  ' Create a new recordset using the body of the retrieved message.
  '*******************************************************************
  Set rsReceive = mReceive.Body
  Debug.Print rsReceive(0)                   ' Print column 1
  Debug.Print rsReceive(1)                   ' Print column 2
  Debug.Print rsReceive(2)                   ' Print column 3
 
  rsReceive.MoveNext
  Debug.Print rsReceive(0)                   ' Print column 1
  Debug.Print rsReceive(1)                   ' Print column 2
  Debug.Print rsReceive(2)                   ' Print column 3
 
End Sub