Exporting Acces2K table (or query) to separate text files 
Author Message
 Exporting Acces2K table (or query) to separate text files

I am trying to write VBA code that selects records from a query using a value from another query, then exports each resultset to a fixed length text file.

I have already built the code to loop through the first query/table and return the correct parameter.  I have also built the export code (including export definitions) but I am having difficulty "opening" the query with a parameter.  (Once I get the query (with parameter) opened, I'm pretty sure I can export it just fine.)

qSchools:  discrictID   eFlag
           1            True
           2            True
           5            True
qExport:   districtID   name    address   amount  
Sub Export()
Dim rs1, rs2 As ADODB.Recordset

   Set rs1 = New ADODB.Recordset
   rs1.Open "qSchools", CurrentProject.Connection, adOpenKeyset

   While Not rs1.EOF

      '  The 1st iteration: rs1![districtID] = 1
      '  The 2nd iteration: rs1![districtID] = 2
      '  The 3rd iteration: rs1![districtID] = 5 ,  ...etc...

      '  I NEED CODE HERE to open "qExport"
         '  Where qExport.districtID = rs1![districtID]
         '  to prepare for exporting to file "g:\school" & rs1![District] & ".txt"
      '  I can't seem to declare and open another New ADODB.Recordset
      '  The following two lined of code won't work...

      Set rs2 = New ADODB.Recordset  '***This line errors!
      rs2.Open "qExport" Where qExport.districtID = rs1![districtID]

      DoCmd.TransferText acExportFixed, "Export_Spec", "qExport", & _
         "G:\School" & rs1![districtID] & ".txt", 0

End Sub

It seems that I can't delcare and open (and nest) another recordset within the the first recordset I've opened.

Any help would be greatly appreciated!

Tue, 10 May 2005 07:41:39 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Use parameter Query to export to multiple text files

2. Export Access97 query to a text file

3. Export query results to text file

4. Exporting tables to text files

5. Exporting TABLE into a text file

6. Export an Access table to a text (fixed width) file

7. export data from an Access table to a text file via vbScript

8. Help! Exporting table to Fixed-Width Text file

9. Best way to export table to delimited text files

10. Howto export tables in text delimited files ?

11. Export Access Table to Delimited Text File

12. Export database table to a text file


Powered by phpBB® Forum Software