Link to home
Start Free TrialLog in
Avatar of ozphil
ozphil

asked on

Converting recordset to query or table without pain

I'm great at creating recordsets with Access VB which contain just the data I need.

I'm an absolute dummy when it comes to turning this recordset into a query or table that I can display.

I would like a control like VB's data control where you can  assign a recordset to the data control and hey presto there's your data on the screen. But I can't for the life of me find similar functionality in MS Access.

Now I know how to do it the hard way so unless you have a general purpose library function I don't want to know about pain.

Has anyone done this. I've tried many different methods and they're all ugly.
Avatar of ozphil
ozphil

ASKER

Edited text of question
If I'm understanding you correctly, Your objective being able to assign a recordset to an object variable, and view that variable.

I use the following code-behind-form to create the view

   strSQL = "SELECT * FROM tblTable1"
   strsql = strsql & "WHERE " & me.filter ' the form's filter
   Set q = db.QueryDefs("myqry")
   q.SQL = strSQL
   DoCmd.OpenQuery "myqry", , acReadOnly

A where statement is tough to build because string field variables require quotes ie. "Where Mystring = " & chr$(34) & f!stringfld & chr$(34)

The BuildCriteria method is generally handy for building a filter which in turn can be made into a where statement for a query.
Avatar of ozphil

ASKER

This is not quite the answer im looking for.

Beginning with a recordset object (dynaset), can you easily convert it to a viewable query.

Assume youre given a dynaset dn but youre not given the filters etc upon which it was derived.With visual basic you can just assign it to a data control recordset property and you get a display.

Can you do this with Ms Access, starting with a recordset object but no  knowledge of the SQL behind it.

Hi ozphil,

You might take a look at the name property of the recordset

?rst.Properties("name")

which gives you its underlying sql (up to 256 characters). It would be possible I think to set the recordsource of a form to this property dynamically

forms!categories.recordsource=rst.Properties("Name")

Also there is the copyquerydef method which returns the querydef used to create the recordset (if one was used). This is less useful because it errors if a querydef wasn't used.



dim f as form, q as querydef
The form could assume the recordsource of a query's sql.
f.recordsource = q.sql
or if the query were a saved query
f.recordsource = "mysavedqueryname"
Avatar of ozphil

ASKER

Thanks guys.

Helicopter is understanding the question and providing the appropriate response.

I thought copyquerydef looked promising but it seems pretty nebulous to me, being based on a query in the first place. Now a dn.copyRecordsetToSQL method would be very very nice.

I shall look into the name property of the recordsource.

Thanks.
Avatar of ozphil

ASKER

I think Helicopter's answer is nearly the answer but not quite. A parameter query still appears as a parameter query. Oh well can't have everything our way.

Helicopter could you please submit as answer.
ASKER CERTIFIED SOLUTION
Avatar of Helicopter
Helicopter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial