Recordset in VB6.0

Question

Hi, I'm retrieving Data from a Database in a record set using VB6... so while retrieving data using Select in SQL I added a column called Comments along wit it so that in the recordset, all the columns of the table+'Comments' column would be present... I don't want to(and also I cannot) update any contents in the database as i'm only 'fetching' data frm the database now...

Now when i pass the fetched data for validation, I want to fill the 'comments' Column with the respective errors if the particular row in the recordSet is erroneous).... When i do that i get an error saying "I'm not authorized to do that!!(to update the 'Comments' Column"....

Now My Question is "In what way i can solve this problem???".. I tried to replicate this recordset and there by now filling the 'comments'column (in the replicated one) which in turn showed the same error... It seems this could be because the duplicate one(recordSet) just retains the properties of the original one...

Can u any1 help how to solve this???? Any ways to replicate the recordset(without inheriting its properties something)??????

Answer 1

Not exactly what you are looking for, but this is how I do it:

1. Create a class to encapsulate the recordset ('Customer' class for 'Customer' table)
2. Add your Comment property to the class and not to recordset
3. Add a Validate method to your class. Have it write to your Comment property (I use an Errors Collection)
4. Read the recordset
5. Parse it into a "new Customer"
6. Validate
7. Check the Comment property (or the Errors Collection)

Answer 2

You can use the MSDataShape with its SHAPE..APPEND syntax to append a new Field to an ADO Recordset. Here's a quick example using Jet (a.k.a. MS Access):

Sub ShapeAppendField()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim jeng
    Set jeng = CreateObject("JRO.JetEngine")
    jeng.RefreshCache .ActiveConnection

    Set .ActiveConnection = Nothing
  End With

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;" & _
        "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    .CursorLocation = 3
    .Open

    .Execute _
    "CREATE TABLE Test (" & _
    "existing_col INTEGER NOT NULL);"
    .Execute _
    "INSERT INTO Test (existing_col)" & _
    " VALUES (1);"

    Dim rs
    Set rs = CreateObject("ADODB.Recordset")
    With rs
      .CursorType = 2
      .LockType = 4
      .Source = _
          "SHAPE {" & _
          " SELECT existing_col" & _
          " FROM Test" & _
          "} APPEND NEW adInteger AS new_col"

      Set .ActiveConnection = con
      .Open
      Set .ActiveConnection = Nothing

      .Fields("new_col").value = 55
      MsgBox .GetString
      .Close

    End With
  End With
End Sub

Answer 3

I think you are just asking how to do a disconnected recordset. For that you just change the cursor location of the recordset.

Dim rstTest as ADODB.RecordSet
Set rstTest = New ADODB.RecordSet
With rstTest
   .CursorLocation = adUseClient
   .Open "your sql here", your_connection_object, adOpenStatic, adLockBatchOptimistic, adCmdText
   ' now disconnect it and force the local copy
   .ActiveConnection = Nothing
End With