In Memory Recordset Without Creating Database

A

Alok Joshi

Hi
I just want to create a Recordset from the rows in an excel sheet but
without creating a database on the disk. Is this possible either in DAO or
ADO?
I want to do this so that I can query the data more easily.
Alok
 
A

Alok Joshi

Thank you very much. I hold you in great esteem and regard for helping
in these newsgroups with your vast knoledge and expertise.
 
J

Jamie Collins

Tom Ogilvy said:
It would be, but apparently there is a memory leak when doing this with ADO
(I don't know about DAO, but that might work).

For ADO, you can do a SavecopyAs and then access that workbook if all you
are doing is read only type queries.

See Mr. Erlandsen's site for sample code for ADO and DAO:

http://www.erlandsendata.no/english/index.php?t=envbadac
Click to expand...

There doesn't appear to be code for the workaround at this famous
site, so try this:

http://groups.google.com/[email protected]

Another option is to fabricate a disconnected recordset i.e. create
your own fields and data e.g.

Option Explicit
Sub test()
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
With rs

' Client-side cursor required for
' disconnected recordset
.CursorLocation = 3 ' adUseClient

' Cursor will always be Static for
' client-side recordset
.CursorType = 3 ' adOpenStatic
.LockType = 4 ' adLockBatchOptimistic

' Add field
.Fields.Append _
"Heinz_varieties", 3 ' (adInteger)

.Open

' Manually add data
.AddNew "Heinz_varieties", 57
.UpdateBatch

End With
End Sub

Jamie.

--
 
Top