[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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