Temp recordsets and SQL statements

[connexion]

Is there anyway of using an SQL statement to build a recordset in memory and then work with it, THEN basing another SQL statement on the temporary recordset?

I always seem to get the message “cannot find the query or table”. Does an SQL statement have to be based on a query/table?
OR could I base an SQL statement on a recordset held in memory to add to, then manipulate, then display this recordset in a ListBox.

I don’t know much about arrays, but could you build an array and then base on SQL statement on that, or am I barking up the wrong tree, to find out that it simply isn’t possible to base SQL statements on anything other than query’s or tables?

I do have this working at the moment by but i use an SQL statement to build a local table, then i have a second SQL statement based on that table.

I’d just like the increased speed of doing the whole thing in memory as well as reducing the need to compact and number of tables in the back end database.

Thanks

Vince

 

[Mile-O]

What you want is not possible but you can turn the first SQL string into a QueryDef and then base a Recordset off that?

 

[connexion]

Sounds goo to me Phil.
I'll give that a bash!

Thanks
Vince

 

[dcx693]

Does an SQL statement have to be based on a query/table? OR could I base an SQL statement on a recordset held in memory to add to, then manipulate, then display this recordset in a ListBox.

You can base a listbox recordset on a recordset held in memory - but that recordset must be based on a table/query in your database AND you must be using Access 2002 or higher.

Here's some sample code:

Private Sub cmdFillListBox_Click()
Dim strSQL As String
Dim rst As ADODB.Recordset

    strSQL = "SELECT Last,First FROM tblNames ORDER BY Last;"
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    rst.Open strSQL, CurrentProject.Connection, , , adCmdText
    
    Set Me.lstNames.Recordset = rst
    
    rst.Close
    Set rst = Nothing

End Sub

If you're using an earlier version of Access, your options are:

• save the recordset to a QueryDef and use that to fill the listbox
• use a SQL string to create a value list and set that as the rowsource for the listbox (but you're limited to 2,048 characters in Access 2000 and prior)
• use a custom list-filling callback function

 

[connexion]

That's the bit i've been trying to get round.

I am trying to base a listbox, on a recordset, based on a recordset.

I am using Access 2000 and reckon that pen and paper can't be beat! lol

Thanks
Vince