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.
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.
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.
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.
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.
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.recordsou rce=rst.Pr operties(" 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.
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.recordsou
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"
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"
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.
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.
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.
Helicopter could you please submit as answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER