 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2006How to create a recordset in memory
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? 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 > 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 [quoted text clipped - 6 lines] > INTO I suppose), then query them, and then just destroy this recordset > when finished? 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. > 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. [quoted text clipped - 14 lines] >> INTO I suppose), then query them, and then just destroy this recordset >> when finished? 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 > 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 [quoted text clipped - 19 lines] >>> INSERT INTO I suppose), then query them, and then just destroy this >>> recordset when finished? 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.  SignatureRon W www.WorksRite.com > 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 [quoted text clipped - 6 lines] > INTO I suppose), then query them, and then just destroy this recordset when > finished? 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 > Dim cnn As ADODB.Connection > Dim rs As ADODB.Recordset [quoted text clipped - 45 lines] > when >> finished? 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) > 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 [quoted text clipped - 6 lines] > INTO I suppose), then query them, and then just destroy this recordset when > finished? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.