Solved

Converting recordset to query or table without pain

Posted on 1999-01-09
8
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 4

Author Comment

by:ozphil
ID: 1973152
Edited text of question
0
 
LVL 1

Expert Comment

by:billinb
ID: 1973153
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
ID: 1973154
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 8

Expert Comment

by:Helicopter
ID: 1973155
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
 
LVL 1

Expert Comment

by:billinb
ID: 1973156
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
ID: 1973157
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
ID: 1973158
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
ID: 1973159
OK...here it is. Maybe Access 2000 will deal with it. Who knows.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…

728 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