Need to export select statement.......

  • Thread starter Thread starter Preston Pierce
  • Start date Start date
P

Preston Pierce

I need to export the select statement that is driving the
data for my window?...Please help!!
 
Preston said:
I need to export the select statement that is driving the
data for my window?...Please help!!
Click to expand...


Check Help on the TransferDatabase and TansferText methods.
 
I need to export the select statement that is driving the
data for my window?...Please help!!
Click to expand...

Could you explain what you mean by "driving the data for my window"?
Access uses Forms, Reports, Queries - is one of these "the window"?
 
-----Original Message-----


Could you explain what you mean by "driving the data for my window"?
Access uses Forms, Reports, Queries - is one of these "the window"?


.
Click to expand...

Sure, I have a form that has a select statement as its
recordsource and I need to export that record source.
 
The following should work for you.

Open the form in design view. View the properties of the
form and click on the data tab, then click in the field
that says "Record Source". If the SQL statement is
displayed there directly, you can just copy it. More
likely there is a table or query listed. If so, click
the button with three dots to the right of the field and
it will open the record source in query design view.
Then, go to View|SQl View on the menu to see the SQL
text. Highlight the text and press Ctrl+C to copy it.
Paste it wherever you want.

-Ted
 
Sure, I have a form that has a select statement as its
recordsource and I need to export that record source.
Click to expand...

thanks. It sounded like you wanted to export the SQL of the select
statement itself!

Simply open the Form in design view; view its Properties; and click
the ... icon of the RecordSource property. Save this as a stored Query
(if it isn't saved already). Then use File... Export to export the
query.
 
I am sorry I suppose I did not explain well enough. I need
to export through code...
 
I am sorry I suppose I did not explain well enough. I need
to export through code...
 
I am sorry I suppose I did not explain well enough. I need
to export through code...
Click to expand...

Ok... once you save the Recordsource SQL as a Query (manually, or
using the CreateQuerydef method), use the TransferText or
TransferSpreadsheet or TransferDatabase method to export it (depending
on what you're exporting it to).
 
Oh, Ok, that is a little different.

The best I can tell, it would depend a little on whether
you want general code that will work for any form, or for
a specific case, and also whether your form(s) generally
have an SQL statement as the recordsource or a saved
query name (I generally used saved queries in hopes that
they will then be optimized by Access).

I think for the first case (direct SQL statement) you
could probably just capture and export the value of:
Me.recordsource

In the other case, the only way that I saw to do it in
looking at it quickly is to use:

CurrentDb.QueryDefs(Me.RecordSource).SQL

Of course, if the code is running from a module outside
of the form you would have to substitute the form name
for the term "Me".

Also, if you wanted to write a module to loop through all
forms you could loop through the forms collection.

If you wanted code that would work for either type of
recordsource above, and even for those that a single
table as the source, you could probably first get the
me.recordsource value, then evaluate it to see what it
starts with. You could see if it is SQL text if it
starts with SELECT, but to differentiate tables vs
queries would depend on whether you have set prefixes for
them. If not, there would probably be various other ways
you could differentiate (for sure you could check to see
if they are in the QueryDefs or TableDefs collections as
a last resort).

Hopefully this will help give you some ideas.

-Ted
 
Back
Top