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("tblPerio
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
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("tblPerio
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
Jeffrey Coachman
Did you try it?
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.
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.
ASKER
*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"?
*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"?
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:
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
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 filesThen 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
ASKER
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? :)
....What is the best approach? :)
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
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
ASKER
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
TEST-471-Non-HIX-for-MLR.accdb
With nearly two MILLION records in one data set, nearly reaches the maximum 2 gb file size for MS AccessThen 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)
ASKER
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.
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.
Still not clear what you want the result to be for the sample db you posted...
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("qryTe
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")
Dim qdf As DAO.QueryDef
Set qdf = CurrentDB.QueryDefs("qryTe
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")
ASKER
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
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
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
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
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Please close. Thanks.
ASKER
Please close. Thanks.