Solved

Converting recordset to query or table without pain

Posted on 1999-01-09
8
213 Views
Last Modified: 2012-05-04
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
Comment
Question by:ozphil
  • 4
  • 2
  • 2
8 Comments
 
LVL 4

Author Comment

by:ozphil
Comment Utility
Edited text of question
0
 
LVL 1

Expert Comment

by:billinb
Comment Utility
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
 
LVL 4

Author Comment

by:ozphil
Comment Utility
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
 
LVL 8

Expert Comment

by:Helicopter
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Expert Comment

by:billinb
Comment Utility
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
 
LVL 4

Author Comment

by:ozphil
Comment Utility
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
 
LVL 4

Author Comment

by:ozphil
Comment Utility
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
 
LVL 8

Accepted Solution

by:
Helicopter earned 50 total points
Comment Utility
OK...here it is. Maybe Access 2000 will deal with it. Who knows.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now