Filtering on multiple tables/queries then extract to csv

[GraemeG]

Hi,



I have 10 tables all with the same amount of records (will eventually be 12000+) based on a KeyReference but with different fieldsets.

I need to be able to set a filter, using the KeyRefernce, on all the tables at once to show the one particular record in each of the tables and then extract the tables to CSV files on a server address. (Prefereably all done in the background from a command button so the user does not see this happening once they have selected the record they want)

Is this possible?

It would be very much appreciated it someone has the answer and method for this.

Thanks

[rpeare]

Yes, this is possible. If what you say is accurate in that each table will have a KeyReference, and that is the same across your 10 tables and that there's a 1 to 1 relationship between all the tables you can just build a query linking all the data through the KeyReference (as long as it doesn't exceed 255 columns) then export the query as a whole to a csv on a button click (assuming you want all your fields listed laterally, if you want each record from each table on a new line that's a different problem, but still doable).

[Robeen]

If I understand correctly:

1. In the OnClick Event of a CommandButton on a Form, gather the value[s] that will be used to create your filter & store in variable[s].
2. In your VBA for the command button - Create an SQL string for an Insert Query using the variables from Step 1 as criteria to get the record[s] you want from each of the Tables.
3. In the VBA - use a DoCmd.RunSQL to write the current line to a table that you will create to hold data you want to export.
This will mean modifying the SQL for each table you're querying - and then running the query with the new SQL each time.
I know it sounds tedious - but once you've got it running, it will be seamless.
4. Export the table to which you have been writing your filtered data from each table.

Here's a simple example of some code I've used that might help you get started:

Dim StudentName, StrSQL As String
Me.cmbStudentName.SetFocus
StudentName = Me.cmbStudentName.Value
StrSQL = "INSERT INTO Table1 (StudentName) "
StrSQL = StrSQL & "VALUES (" & "'" & StudentName & "'" & "); "
MsgBox StrSQL
DoCmd.RunSQL StrSQL

[ssanfu]

Another method would be pure VBA:

Open a CSV file for writing
Open a filtered recordset based on table 1
Write the values using Write #
Close the recordset
Open the next recordset based on table 2
Write the values using Write #
Close the recordset
...
Open the next recordset based on table 10
Write the values using WriteLn #
Close the recordset
Close the CSV file

This way, you have complete control of what and how data is written to the text file.

The number of fields won't matter since only one table (recordset) is being used at a time. If the CSV file gets too big, MS NotePad won't open it, but another text editor like NotePad+ will.

[GraemeG]

Yes, this is possible. If what you say is accurate in that each table will have a KeyReference, and that is the same across your 10 tables and that there's a 1 to 1 relationship between all the tables you can just build a query linking all the data through the KeyReference (as long as it doesn't exceed 255 columns) then export the query as a whole to a csv on a button click (assuming you want all your fields listed laterally, if you want each record from each table on a new line that's a different problem, but still doable).

Thanks but it exceeds 500columns

[GraemeG]

Yes, this is possible. If what you say is accurate in that each table will have a KeyReference, and that is the same across your 10 tables and that there's a 1 to 1 relationship between all the tables you can just build a query linking all the data through the KeyReference (as long as it doesn't exceed 255 columns) then export the query as a whole to a csv on a button click (assuming you want all your fields listed laterally, if you want each record from each table on a new line that's a different problem, but still doable).

Another method would be pure VBA:

Open a CSV file for writing
Open a filtered recordset based on table 1
Write the values using Write #
Close the recordset
Open the next recordset based on table 2
Write the values using Write #
Close the recordset
...
Open the next recordset based on table 10
Write the values using WriteLn #
Close the recordset
Close the CSV file

This way, you have complete control of what and how data is written to the text file.

The number of fields won't matter since only one table (recordset) is being used at a time. If the CSV file gets too big, MS NotePad won't open it, but another text editor like NotePad+ will.

Hi this sounds like what I am after but not sure on how this is all auto on vba?
Thanks

[rpeare]

This is the code I used:

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

[ssanfu]

Thanks but it exceeds 500columns

Does that mean the output file (CSV) would be one line with 500 columns?
Or 50 columns by 10 rows?

How many fields are there in each table?
What do you want the output file to look like?

From your post #1

to show the one particular record in each of the tables and then extract the tables to CSV files

I don't quite understand... search for one primary key across 10 tables and write only those records to a CSV file? Or write all records to a CSV file?

If rpeare's code isn't quite what you need, my idea for the code was a little different... post back.
One way or another, it can be done..