Solved

How to obtain SQL Statement from recordset

Posted on 2011-03-18
12
352 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

760 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

22 Experts available now in Live!

Get 1:1 Help Now