This is the code I used:
Code:
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sSQL As String
Dim PK As Long
Dim fs
Dim fOuput
Dim sOutputFile As String
Dim i As Integer
Dim sOutputString As String
Dim j As Integer
Set db = CurrentDb
Set fs = CreateObject("scripting.filesystemobject")
sOutputFile = CurrentProject.Path & "\TestOutput.csv"
Set foutput = fs.createtextfile(sOutputFile)
Set rst1 = db.OpenRecordset("tblexportstep1")
rst1.MoveFirst
Do While rst1.EOF <> True
sOutputString = ""
PK = rst1.Fields("Step1_ID")
sSQL = "SELECT * FROM tblExportStep2 WHERE ([Step2_ID] = " & PK & ")"
Set rst2 = db.OpenRecordset(sSQL)
For i = 0 To rst1.Fields.Count - 1
sOutputString = sOutputString & rst1.Fields(i) & ","
Next i
If rst2.RecordCount <> 0 Then
For j = 1 To rst2.Fields.Count - 1
sOutputString = sOutputString & rst2.Fields(j) & ","
Next j
Else
sOutputString = sOutputString & ",,,"
End If
foutput.writeline sOutputString
rst1.MoveNext
Loop
foutput.Close
Set fs = Nothing
rst1.Close
Set db = Nothing
For each successive recordset (each of your remaining 8 tables) you'll have to set up a recordset.
This assumes:
1. At least one of your tables has ALL possible primary keys in it. If that isn't your situation you'll have to build a query that does show every possible PK
2. The PK for each table will be in the first field of each table
3. There may be missing data on any of the subsidiary tables, in which case you have to build in the appropriate number of commas into your statement, one for each field other than the PK of the table.
4. You will be dumping the text file to the same folder the database resides, this can be changed of course, just modify the code