In Memory Recordset Without Creating Database
 

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

In Memory Recordset Without Creating Database
 

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/...php?t=envbadac

--
Regards,
Tom Ogilvy

"Alok Joshi" wrote in message
...
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

In Memory Recordset Without Creating Database
 

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

"Tom Ogilvy" wrote in message ...
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/...php?t=envbadac

--
Regards,
Tom Ogilvy

"Alok Joshi" wrote in message
...
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

In Memory Recordset Without Creating Database
 

"Tom Ogilvy" wrote ...

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/...php?t=envbadac

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

http://groups.google.com/groups?selm...g .google.com

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.

--