MS Access help

[Duritz]

Hi all, need to extract large amounts of info from my database of results etc, heaps of records, as you know Aus racing prolific. So, here's my query:

I am using the export feature in Query in Access, to export a large quantity of info to a text file. Is there any way for Access to automatically break it up into smaller files instead of sending it to one large text file?

Preferably, I'd like it to be able to break it up into month by month files - there's three years worth of data (starting 2003) so it'd have a Jan 03, Feb 03 etc.

Is this possible, or is there any other way to get Access to export to a number of files, rather than just one?

Another way might be to limit it to say 5,000 records per text file perhaps?

[wesmip1]

Duritz,

You could pass a date range to the query and run it for any range you want.

Let me know if you need help.

[Duritz]

And then I'd have to run the query 38 or so times, with a different file name each time for the result, right?

[wesmip1]

Duritz,

Yes and No.

Yes it has to be run 38 times. No you don't have to run it. You could write a macro that does it for you (including naming the file). It would probably take a couple of hours to write up and test.

Good Luck.

[Duritz]

Thanks Wesmip.

[PunterPete]

Try this - Very simple text file creation.
Insert a button on a form and place the following code under "On Click":
Note I have written this very quickly and haven't tested it and you will need to insert your own query and make your own formula for d1 & d2.

Dim dbs As Database
Dim rst, horserst As Recordset
Dim strSQL, filename As String
Dim d1, d2 As Date
Dim i

For i = 1 To 38 ' or however many files you need

'd1 = Calculation to get lower date using i
'd2 = Calculation to get upper date using i

filename = "Info" & i & ".txt"
Open "C:\DIR\" & filename For Output As #1

Set dbs = CurrentDb
strSQL = "INSERT QUERY FOR ALL DATA HERE" ' eg "SELECT .....;"
Set rst = dbs.OpenRecordset(strSQL)

rst.MoveFirst
i = 0
Write #2, "COLTILE1"; "COLTITLE2"; "COLTITLE3"; et; etc

Do While Not rst.EOF
If rst.Fields(0) >= d1 And rst.Fields(0) <= d2 Then
Write #2, rst.Fields(0).Value;
Write #2, rst.Fields(1).Value;
Write #2, rst.Fields(2).Value;
Write #2, rst.Fields(3).Value;
Write #2, rst.Fields(4).Value;
Write #2, rst.Fields(5).Value;
'etc etc etc
End If
Loop
Close #2
rst.Close
Next i

[marcus25]

Set dbs = CurrentDb
strSQL = "INSERT QUERY FOR ALL DATA HERE" ' eg "SELECT .....;"
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
If rst.Fields(0) >= d1 And rst.Fields(0) <= d2 Then
Write #2, rst.Fields(0).Value;
Write #2, rst.Fields(1).Value;
Write #2, rst.Fields(2).Value;
Write #2, rst.Fields(3).Value;
Write #2, rst.Fields(4).Value;
Write #2, rst.Fields(5).Value;
'etc etc etc
End If
Loop
Close #2
rst.Close

Hi!
"rst.Fields(0) >= d1 And rst.Fields(0) <= d2" this could go straight into strSQL like "where the date is >= and date is <= " the date in the database, saves you going through the loop. SQL is at least a hundred times faster than a loop, depending on a few other things of course.
(<= be careful how you define d1 and d2 I would use < d2 because you might double up on the upper date).
cheers