Solved

How to obtain SQL Statement from recordset

Posted on 2011-03-18
12
363 Views
Last Modified: 2012-05-11
Hi Guys, this has got to be simple but it's alluded me so far.
How do I get the SQL statement from a recordset object?

I'm wanting to set it to a variable but I don't know what the property name is for the SQL Text....

dim strSQL as string
strSQL = Me.RecordsetClone.sqltext

Thanks in advance,

DataFlowJoe
0
Comment
Question by:dataflowjoe
  • 5
  • 5
  • 2
12 Comments
 
LVL 75
ID: 35169200
From the RecordsetClone ?

Don't think you can.  But ... in general you can do this:

Dim strSQL As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("SomeSavedQueryName")
strSQL = qdf.sql
MsgBox strSQL

mx
0
 
LVL 75
ID: 35169231
The RecordsetClone may just be the clone of a Table - that is the Recordsource.

mx
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169308
The recordsetclone that I'm trying to obtain the sql statement from is in a subform which is also filtered by the Link Master Field.
If I could get the sql text from the recordsetclone I could then pass this into a report by using the onopen event to give me the same listing of records which could be printed out.
It would be great if I could set the recordsetclone object to a variable and pass this into the report recordsource but I know this can't be done. However if I had the SQL statement then this can be passed into the report through the onopen report event.

0
 
LVL 75
ID: 35169322
What is the Record Source of the Form ?

mx
0
 
LVL 33

Expert Comment

by:Norie
ID: 35169358
Why do you need the SQL?

Can't you set the report up to use a parameter query that takes values from the form/sub-form?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169381
The recordsource is dynamically created depending on what options the user clicks. The options filter an activity list based on 2 date parameters and then this is further filtered automatically by virtue of the Link Master Field. The recordsourceclone object contains the data I need but I can't get at the SQL statement.
I can loop through the recordsourceclone and manipulate the data programatically. I thought it would be a simple matter to extract the SQL statement from it.... obviously not!

Thanks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169405
Why do you need the SQL?

Can't you set the report up to use a parameter query that takes values from the form/sub-form?

Mmmh good point, is there a way of assigning the report recordsource to be the same as the subform?
0
 
LVL 75
ID: 35169419
The only reason to use the RecordsetClone would be if you have further filtered the subform by using the built in Right Click options - Filter By, Filter Excluding ... etc, which would be reflected in the RecordsetClone.  I do this all the time.  However, if this is the case, you can use the same general Recordsource for the Report ... then set the Report to the Filter property of the Subform ... basically.

   
    Dim sRpt As String
    Dim rpt  As Report
    Dim sFilter As String
     sFilter = Me.fsub1.Form.Filter    ' example = setting from Main form
    sRpt = "YourReportName"
   
    DoCmd.OpenReport sRpt, acViewPreview
    Set rpt = Reports(sRpt)
    DoEvents
    With rpt
         .Visible = False
         
         .Filter = sFilter
         .FilterOn = True
         .Visible = True
    End With
   
    Set rpt = Nothing

0
 
LVL 33

Expert Comment

by:Norie
ID: 35169428
Are you saying the recordsource for the subform is dynamically created?

If it is why can't you dynamically create the recordsource for the report?

Mind you I dont' quite see why you would be dynamically creating the recordsource for the subform - perhaps I'm missing something.
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169490
The subform is automatically filtered by the Master Link Field I was hoping to pass that into the report as well. Basically whatever the subform recordsetclone is, that's what i need for the report recordsource. Hence the thought that there maybe an SQLText property associated with the recordsetclone or even simply assign the recordsetclone to the recordsource of the report like the following...

Private Sub Report_Open(Cancel As Integer)
On Error Resume Next
    Me.RecordSource = Forms![frmUsers]![frmCompanyActivities].Form.recordsetclone
End Sub
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35176304
The reason this does not work ...

Me.RecordSource = Forms![frmUsers]![frmCompanyActivities].Form.recordsetclone

is because the RecordSource property is a String value ... query/table name or an SQL statement.
RecordsetClone is an Object, and does not happen to have an SQL property ... as does say the QueryDef object.

I would suggest trying what I posted @ http:#a35169419  ... which I have used in many places.

mx

0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 35335771
Not quite what I was looking for but it pointed me in the right direction
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change AD password via MS Access DB 2 20
Simple Excel re-arrange data.  (I can work in access too). 8 50
Export Query data to excel file 14 37
Query design issue 2 24
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 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

12 Experts available now in Live!

Get 1:1 Help Now