Export (looped) Sql to Text Files

Yesterday, you provided generic VBA code for MS Access to export multiple tables to text files.  I'm now putting a twist on it and hope to output a looped SQL select query to pass multiple data sets to text files.  Please review, assist, and guide for successful output beginning with the code below.  Does this work?  Will it pass "strSql" to export it as ExportFile?

Public Function MLR_Out()

Dim Db As DAO.Database
Dim rstTables As DAO.Recordset
Dim strSql As String
    Set Db = CurrentDb
    Set rstTables = _
      Db.OpenRecordset("tblPeriods", dbOpenTable)
Dim ExportFile As String
   
    ExportFile = "C:\Path\filename.txt"
rstTables.MoveFirst
Do Until rstTables.EOF
     strSql = "SELECT * FROM Tblname " & rstTables.Fields(0) & ";"
   
DoCmd.RunSQL strSql
  DoCmd.TransferText acExportDelimited, "Export Spec", strSql, ExportFile

rstTables.MoveNext
Loop

End Function
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Does this work?
Did you try it?
0
Jeffrey CoachmanMIS LiasonCommented:
Yesterday, you provided generic VBA code for MS Access to export multiple tables to text files.
Who is the "You" here?
Please post a link to the previous question.
0
PatHartmanCommented:
No.  You can't use an SQL string in this expression.  It must be a saved querydef.  Since your query changes structurally each time, you can't use an argument.  You must save the querydef.  Just use a single querydef.  Name it QD_temp or something.  Use that in the TransferText.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CFMIFinancial Systems AnalystAuthor Commented:
*Jeff - My query works (the Debug.Print strSql will translate from the watch window into a regular query) BUT 'DoCmd.RunSQL strSql' renders a Runtime error '2342':  A RunSQL action requires an argument consisting of an SQL statement."  Please assist.
*Pat.  This is also where I need guidance.  Please show me how the QD_temp variable works / is stated.  Do I "Set QD_temp = strSQL"?
0
Jeffrey CoachmanMIS LiasonCommented:
I'm still confused
Are you saying that this is the only way to accomplish your ultimate goal? (looping)
Or are you asking if another approach may be better.

When you say:
pass multiple data sets to text files
Then you say you need to loop the same dataset multiple times to do this...

That to me says that perhaps you need one query with multiple OR criteria.
So instead of each loop iteration getting another dataset, ...why not use one query with multiple ORs
For example, in your loop scenario, ...you loop once to get all of USA,  Then the next loop gets all Canada, then the next loop does all of Mexico.
When not use one query of:
SELECT *
FROM YourTable
WHERE Country="USA" OR "Canada" OR "Mexico"
To get them all?

Am I missing something?

This is why sometimes your question is better framed as a question.
For example:
"This is what I want to do, ...what is the best approach"

Rather than a directive:
"I have decided to use a loop for this, ...help me make it so..."

JeffCoachman
1
CFMIFinancial Systems AnalystAuthor Commented:
This is what I want to do: I have 36 months of data residing in 36 tables.  I have to filter each data set and so that's the purpose of the use of query.  I then need to extract the filtered data to delimited text files.  Some of the data sets are well over a million records (and approaching two) with maybe twenty-five/thirty fields.  Others might be as small as 500,000 records.

....What is the best approach?  :)
0
Jeffrey CoachmanMIS LiasonCommented:
Just know that the data "probably" should not have never been stored in this manner, ...
And going forward, you should combine this data int one table, ...Then you should only need one query.

At the most basic level you can create a union query to combine all the tables
Or create a union query, then make that into your main table going forward.

But we are getting ahead of ourselves... First can you post a small sample of this database?
Perhaps with just three of the 36 tables...
This way you can explain exactly what ultimately needs to be done.

JeffCoachman
0
CFMIFinancial Systems AnalystAuthor Commented:
With nearly two MILLION records in one data set, nearly reaches the maximum 2 gb file size for MS Access so the ideas of "a UNION query" or "combining multiple months into one" would be ungainly.  Instead, I'm linking the large tables from Access data files separated by month. (They are local tables in the attached TEST file but are linked in the production file.)  There were 36 of these but now I see there are 72.  Please see attached file and 'Function MLR_Out' inside it.  The file only includes half of what I'll end up doing but it's a reasonable sample.
TEST-471-Non-HIX-for-MLR.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
With nearly two MILLION records in one data set, nearly reaches the maximum 2 gb file size for MS  Access
Then this is part of he issue as well.
Any looping solution would also eat up a fair amount of the DB size to run t completion.

Forgetting any code for now.
can you tell me in simple terms what you would be getting form each table?
In other words, can you post the exact results you are looking for? (based on this sample data)
0
CFMIFinancial Systems AnalystAuthor Commented:
Good question actually.  The SQL in the VBA kind of included more than we'll need.  (We really might only need GRGR_ID, BLAC_POSTING_DT, LOBD_ID, GL Market, GREL_CJA_CODE, Net.)  That said however, when I run a query with the filters in place on several of the elements, it returns summary data fairly quickly (30 seconds or so).  

My thought was to 1) run the query for month 1, 2) transfer text - month 1, loop.  So then do the same two steps for months 2-72.  Since the query runs quickly and the data extract should be for only one set at a time, DB size wouldn't be too consuming.  Maybe I'm wrong.  Thanks.
0
Jeffrey CoachmanMIS LiasonCommented:
Still not clear what you want the result to be for the sample db you posted...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Pat mentioned, you cannot directly export an SQL string. Instead, create a new query (name it something like qryTemp), and then modify that query as needed before you export:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDB.QueryDefs("qryTemp")
 
Do Until rstTables.EOF
  qdf.SQL = "SELECT * FROM [" & rstTables("YourFieldName") & "]"
  DoCmd.TransferText acExportDelimited, "Export Spec", "qryTemp", ExportFile
  rstTables.MoveNext
Loop

Obviously, you'd have to use the right Table and Field names, and you'd also have to use the right Spec name.

Also, if you want filtered data, you need to define that in the SQL. For example, if you want to only show record in January of 2015:

qdf.SQL = "SELECT * FROM [" & rstTables("YourFieldName") & "] WHERE YourDateField>=#01-01-2015# AND YourDateField<#02-01-2015#"
0
CFMIFinancial Systems AnalystAuthor Commented:
Thank you for your patience.  I'm frustrated because I can't fully see what points to what.  I'm seeing from your "qryTemp" that I will need to export it. rather than exporting 'qdf.SQL'. . . or maybe I'm misunderstanding that too.

All I want is to output a filter query on data from three years of tables.  I'm using the 'tblPeriods' because there will be different table names on which I'll eventually query and so am taking it one table name plus period at a time.  So the FROM part of the SQL will be "[blac_subgroup " & rstTables.Fields(0) & "]"  wherein 'blac_subgroup' is the base table name plus period dates listed in rstTables.

As you can see in the attached test db (in which I've updated and tried to follow your 'qdf.SQL' routine), I'm still confused.  I've remmed out the 'DoCmd.TransferText' to focus only on getting the SQL right.  It's failing more than ever.  

Now I'm getting a "Runtime error '3265':  Item not found in this collection."  

When I go back to review your direction, I get lost.  Sorry.  For you convenience, I've included in the attached, not only the screwed up VBA code, but also a sample query based on an undated table.  If there were the 36 filtered queries based on each of their respective tables and output to file, then I'd be in business.
TEST2-471-Non-HIX-for-MLR.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
OK,...then to avid confusion, I will step aside.
My line of questioning was to ask why multiple lops would be needed in the first place.

So if anther Expert can get you what you need based on your original request, ...then go with that.

JeffCoachman
0
CFMIFinancial Systems AnalystAuthor Commented:
Jeff,  I know you've been the only really helpful person.  Pat started down a path with which I'm not familiar but hasn't participated but once.   The confusion pertained to Scott's interpretation of Pat's point.  I'm just not understanding it.

I thought I needed a loop because I've got one query but for multiple source tables.  

All I'm trying to do is to output the data.  The result for the sample db posted was included in the TEST2 query.
0
Jeffrey CoachmanMIS LiasonCommented:
OK,...But Pat and Scott are two of the highest ranking Experts this year.
So you owe it to them to see if they can get our question resolved as posted.

It looks like Scott posted a fairly solid solution...
It should be easy to implement and verify.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The more detail you give us, the better our answers will be. The reason we're all over the board is that your data is stored in a very non-normalized way, with individual tables being used to store "chunks" of data. Jeff was suggesting that you query out your data, and was assuming it was stored properly in a single table, and that you could query that table. Your data is not stored properly, so that's most likely why the suggestions were confusing to you. I realize you may not have any control over the way your data is sourced, but if it's unconventional you should let us know up front.

You're actually pretty close. To get things right, first do this:

Create a new Query in your database. Name it "qryTemp". You don't even need to add tables to it - it just needs to be there.

Now change this:

Dim qdf    As DAO.QueryDef
Set qdf = Db.QueryDefs("qryTemp")

Now, just after your "qdf.SQL= blah blah" line, do this:

DoCmd.TransferText acExportDelim, , "qryTemp", ExportFile

Note that your SQL was incorrect when I first tried it - your FROM syntax was incorrect, and I had to clean up a few missing brackets. I'll leave that for you to get straight, since I'm not sure exactly what you're after with the SQL.

Also, I realize this is a work in progress, but I assume you want to export each table to it's own file(?). If so, then build your ExportFile variable like this:

ExportFile = ExportFile & "\" & rstTables("YYMM_Period") & ".txt"

You would do this INSIDE the loop, just before you run that TransferText command.

I'd also encourage you to use syntax like this:

[blac_subgroup " & rst("YYMM_Period") & "]"

instead of referring to fields by their ordinal position. The example above clearly shows which field you're using, whereas rst.Fields(0) does not.

Finally, if you have troubles with the SQL, then it's a good idea to export the SQL to the immediate window during execution of your module. You can do this by including this line after setting the SQL property of your qdf:

Debug.Print qdf.SQL

You'll get an entry in the Immediate window each time that line is hit. You can then copy that line and paste it into the SQL view of a query. Access will let you know if there's a problem when you try to switch over to datasheet or design view.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFMIFinancial Systems AnalystAuthor Commented:
Scott,  When you say, "Create a new Query in your database. Name it "qryTemp". You don't even need to add tables to it - it just needs to be there," I lose it because in my experience a query must have an object (table or query) within it.  In GUI, for example a new query can't be saved without something within.  Please explain.

I did what you described.  I tested and know the rstTables("YYMM_Period") works great.  Thank you.  After adding what I think you mean - other than that new query - I put a break in the SQL and got a "Compile error: Variable not defined."  

Still struggling.  Please assist.
0
CFMIFinancial Systems AnalystAuthor Commented:
Please close.  Thanks.
0
CFMIFinancial Systems AnalystAuthor Commented:
Please close.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.