How to create a recordset in memory

[GY2]

Currently I'm using a SELECT...INTO statement to create a new table upon
which a second query is run in order to return some rows to be used to
populate a bunch of textboxes. Afterwards I DELETE the new table I just
made. I would like to skip the steps of creating and deleting this table. I
could do that by using the second query to populate an array but I would
rather work with a recordset if possible.

How do I create an empty recordset to which I can add records (via INSERT
INTO I suppose), then query them, and then just destroy this recordset when
finished?

 

[Robert Morley]

You can only do this using ADO, and it won't let you do it via INSERT INTO,
you have to use rs.AddNew, rs.Update, etc.

Look in the ADO help for the Fields collection under the Append method, that
should get you started.



Rob

 

[GY2]

Thanks for the reply. I'm not too worried about how to manipulate the
recordset after I have created one. My question is how to create an empty
recordset in the first place. I am trying to find out if the CreateRecordset
method would do this for example.

 

[Robert Morley]

The CreateRecordset method is another way to do it, yes, though that uses
RDS, which isn't as commonly used as ADO. I don't really know all that much
about it, as I've never used RDS myself, but if you're interested in doing
it that way, I'm sure somebody here can tell you more about it.



Rob

 

[Ron Weiner]

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

' Create a Connection to the Data Source and Open it
Set cnn = New ADODB.Connection
cnn.Open _
"Provider='SQLOLEDB';Data Source=YourServer';" _
& "UID=Username;" _
& "PWD=password;" _
& "Initial Catalog='DatabaseName'"

' Create recordset and disconnect it.
Set rst = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Your Select Statement", cnn, adOpenStatic,
adLockBatchOptimistic
Set rs.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
' At this point you have an open recordset with data that you can
manulipate
' Which is completely disconnected from the back end database.

' Do whatever you want to do to the records here
rs.AddNew (field1, field2,...),(Value1, Value2,...)
rs.Update

' When done Close and Dispose
rs.Close
Set rs = Nothing

What you can't do is to bind this disconnected recordset to a form and have
to fom be updateable. If you do something like me.Recordsource = rs the
form will not allow you to edit the bound fields. Good luck with your
project.

 

[Robert Morley]

Ron's method is also a good way of creating a temporary recordset (with a
lot less effort than my suggestion), which I wasn't thinking of, though it
requires that you be able to create and populate all fields within the
context of a single select statement.

Now that I have my laptop on and beside me, I can give you an example of
creating a recordset completely by-hand with ADO:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
With .Fields
.Append "Field1", adSmallInt
.Append "Field2", adLongVarWChar, 5000, adFldIsNullable Or
adFldMayBeNull
End With
.CursorLocation = adUseClient
.Open
<Do Stuff with it>
.Close
End With

There are a variety of options you can set for each field...as I said, see
the .Fields.Append method help for details.


Rob

 

[Guest]

Application.dbEngine.Workspaces(0).BeginTrans

Application.CurrentDB.Execute ("select ... into")
....
Application.CurrentDB.Execute("delete ...")

Application .dbEngine.Workspaces(0).Rollback


Nothing is committed to disk until there is a CommitTrans.

How much of it stays in memory depends on your computer.

(david)