Not being able to add to a recordset manually

[deadseasquirrels]

I posted about this awhile ago with the heading "trouble with what ADODB Recordsets supports" Basically I was trying to use the adAddNew and adDelete methods on a disconnected recordset, but nothing seemed to work. My next step was to not even connect a recordset to a database, but rather to just use it like a data type. I did the following:

Sub tester()
    Dim testRS As ADODB.Recordset
    
    Set testRS = New ADODB.Recordset
    testRS.LockType = adLockOptimistic
    testRS.CursorType = adOpenStatic
    MsgBox testRS.Supports(adAddNew)
End Sub

but this doesn't work either. This is a totally disconnected recordset, does anybody have any idea how I can use a recordset like a user defined data type?

 

[NateO]

Hello,

Like the following, perhaps?

Sub CreateIt_Early()
Dim rs As ADODB.Recordset
Set rs = New Recordset
With rs
    ' Add field definitions...
    .Fields.Append "ID", adInteger
    .Fields.Append "Value", adChar, 20
    'Open her up
    .Open , , adOpenStatic, adLockOptimistic
    'Add new record
    .AddNew
    'Add Values
    .Fields(0).Value = 3: .Fields(1).Value = "Tester"
    .AddNew
    'Add Values
    .Fields(0).Value = 33: .Fields(1).Value = "Tester2"
    'Update the record set
    .Update
    'Pass it
    .MoveFirst
    Range("a1").CopyFromRecordset rs
    .Close
End With
Set rs = Nothing
End Sub

Sub CreateIt_Late()
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
With rs
    ' Add field definitions...
    .Fields.Append "ID", 3
    .Fields.Append "Value", 129, 20
    'Open her up
    .Open , , 3, 3
    'Add new record
    .AddNew
    'Add Values
    .Fields(0).Value = 3: .Fields(1).Value = "Tester"
    .AddNew
    'Add Values
    .Fields(0).Value = 33: .Fields(1).Value = "Tester2"
    'Update the record set
    .Update
    'Pass it
    .MoveFirst
    Range("a1").CopyFromRecordset rs
    .Close
End With
Set rs = Nothing
End Sub

:wink:

 

[deadseasquirrels]

Nate O thanks, it works perfectly, I guess the function append was the secret. Two more question:

Can you explain the syntax of these lines:

    .AddNew
    'Add Values
    .Fields(0).Value = 3: .Fields(1).Value = "Tester"
    .AddNew
    'Add Values
    .Fields(0).Value = 33: .Fields(1).Value = "Tester2"

I know .addnew doesn't require parameters but what is the colon after .Fields(0).Value =3 mean, and why don't you have to move the cursor to the next spot to add the next database item. I am assuming using .addnew will move the cursor up one spot. But that colon is what's really confusing me.

is it possible to save this recordset in some sort of proprietary microsoft data file that can be opened up later again as a recordset? Hopefully saving it as this data type would be smaller and faster than just to offload it as a text file, which is my only solution at the moment. [/code]

 

[NateO]

Hello again,

Don't worry about the colon, it's a general symantic of VB[A] that I used to consolidate the code a touch. See the following:

http://msdn.microsoft.com/library/en-us/veendf98/html/defstatement.asp

I suspect that the behaviour of Addnew, as I believe Update is as well, will be different with DAO and ADO, ADO being a touch more clever about it (or high-level, pending your disposition on the great debate).

A proprietary Microsoft file type to hold a Recordset? I would say an MDB file or an XLS file? For the former I would be tempted to use DAO, perhaps... E.g.,

http://support.microsoft.com/kb/213820

But, generally speaking, I don't think it'll be any lighter than a flat text file. What might be of interest to you is using Random File Access, e.g.,

http://support.microsoft.com/kb/150700