Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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.
0
ozphil
Asked:
ozphil
  • 4
  • 2
  • 2
1 Solution
 
ozphilAuthor Commented:
Edited text of question
0
 
billinbCommented:
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.
0
 
ozphilAuthor Commented:
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.

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
HelicopterCommented:
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.



0
 
billinbCommented:
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"
0
 
ozphilAuthor Commented:
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.
0
 
ozphilAuthor Commented:
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.
0
 
HelicopterCommented:
OK...here it is. Maybe Access 2000 will deal with it. Who knows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now