Geek asks and answers

In memory, a standalone, disabled ADO record set

I run this code in my subform when my form loads and I don't get any error messages or code breaks. My debug.print shows that Recordset rs is filled with 2131 records, as it should be, but my form shows a single line of C # Name? in every field. The properties of the control source on my controls certainly correspond to the field names that I listed above. RS is a form level variable, and I do not close it or set it until the form closes.

Any idea what I'm doing wrong?

Set rs = New ADODB.Recordset
rs.Fields.Append "TimesUsed", adInteger
rs.Fields.Append "strWorkType", adVarWChar, 150
rs.Fields.Append "DateLastUsed", adDate
rs.Fields.Append "SelectedYN", adBoolean
Set rs.ActiveConnection = Nothing
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open

Dim sSQL As String
sSQL = "MyComplicated SQL Statement Ommitted from this SO Question"

Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
If Not (r.EOF And r.BOF) Then
    r.MoveFirst
    Dim fld
    Do Until r.EOF = True
        rs.AddNew
        For Each fld In r.Fields
            rs(fld.Name) = r(fld.Name).value
        Next
        rs.Update
        r.MoveNext
    Loop
End If
r.Close
Set r = Nothing
Debug.Print rs.RecordCount '2131 records
Set Me.Recordset = rs

OK, so I just read this on MSDN :

, , .

(: .)

+5
3

, - LockType adLockPessimistic adLockOptimisic. adLockReadOnly , - adLockBatchOptimistic , .

, , , . , - , , - / , 3270 (- ). .

, :

Dim rs As ADODB.Recordset 'Form Level variable

Private Sub Form_Load()
    Set rs = New ADODB.Recordset
    rs.Fields.Append "ID", adInteger
    'Set rs.ActiveConnection = Nothing 'Not Required
    'rs.CursorType = adOpenKeyset 'Not Required
    'rs.CursorLocation = adUseClient 'Not Required
    rs.LockType = adLockPessimistic 'May also use adLockOptimistic
    rs.Open

    Dim i as Integer

    For i = 1 To 10
        rs.AddNew
        rs("ID").Value = i
        rs.Update
    Next i

    Set Me.Recordset = rs
End Sub

, ( ) . . , ADO. , - /, ( ). ( ), , . -, - , , 3270, , / . , .

, , "temp" Access, , .

+4

, ?

, adFldKeyColumn Attrib . FieldAttributeEnum.

( ), SQL, . , ... .

rs.Fields.Append "pkey", adInteger, , adFldKeyColumn

, Database Danny Lesandrini: ADO

+7

Note. I was able to get everything to work correctly with the insertion of new records using the example shown above in Create ADO records in memory Then change the following form code ... 'Note. The trick was to use rstADO.MoveFirst and rstADO.MoveLast after rstADO.Update

Option Compare Database
Dim rstADO As ADODB.Recordset
Dim lngRecordID As Long

Private Sub Form_BeforeInsert(Cancel As Integer)

    lngRecordID = lngRecordID + 1
    rstADO.AddNew
    rstADO("EmployeeID").value = lngRecordID
    rstADO.Update
    rstADO.MoveFirst
    rstADO.MoveLast

End Sub

Private Sub Form_Load()

    Dim fld As ADODB.Field

    Set rstADO = New ADODB.Recordset
    With rstADO
        .Fields.Append "EmployeeID", adInteger, , adFldKeyColumn
        .Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
        .Fields.Append "LastName", adVarChar, 20, adFldMayBeNull
        .Fields.Append "Email", adVarChar, 64, adFldMayBeNull
        .Fields.Append "Include", adInteger, , adFldMayBeNull
        .Fields.Append "Selected", adBoolean, , adFldMayBeNull

        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
    End With
    Set Me.Recordset = rstADO

End Sub

Private Sub Form_Unload(Cancel As Integer)

    Set rstADO = Nothing

End Sub
+4
source

More articles:


All Articles