export records to txt file conditional on two fields

[Thepnr]

MS Access 2003

Hello All

I'm new to databases and have a problem that I'd appreciate your help on. I have a large database table with results of matches between players in chess tournaments over a number of years. I would like to split this table and export to individual delimited text files as follows.

There are 5 fields for each record, namely:

ID_1 (player 1)
ID_2 (player 2)
Name (Tournament name)
Type (Type of game, e.g blitz, bullet, rapid and normal.)
Date (Date of tournament in the format yy/mm/dd).

I would like to read the table and export ID_1 and ID_2 i.e. fields 1 and 2 to a text file for individual tournaments. So all records with the same tournament name AND date are from a single tournament and are to be exported to a new file.

The newly created file is to be exported to a folder that is based on game type i.e. field 4, the game type is always the same for any individual tournament and their are 4 game types.

Finally the file name that the data is to be exported to is the date and name of the individual tournament i.e. field 5 and 3. For example:

09/01/15_Tournament Name (a tournament played on 15th Jan 2009)

On a successful run I would now have 4 folders in the specified path named blitz, bullet, rapid and normal. Each folder containing files named as in the format above, and each file having the results of matches in the form of players ID's.

Thank you for reading and I greatly appreciate any input you may have.

Alex

[CreganTur]

Welcome to the forum- always good to have new members!

The following code example shows how you can query results from a table using and ADO (ActiveX Data Objects) connection. It then takes the resultant recordset and exports it as a text file to a designated folder:

Sub GetRecordsAsString()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varRst As Variant
Dim fso As Object
Dim myFile As Object
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT EmployeeId, LastName & "", "" & FirstName AS FullName FROM Employees" _
, conn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rst.EOF Then
'Return all rows as a formatted string- tab delimeted
varRst = rst.GetString(adClipString, , vbTab, vbCrLf)
Debug.Print varRst
End If
'save recordset string to txt file
Set fso = CreateObject("Scripting.FileSystemObject")
Set myFile = fso.CreateTextFile(CurrentProject.Path _
& "\RstString.txt", True)
myFile.WriteLine varRst
myFile.Close
Set fso = Nothing
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub

The resultant text file is tab delimited,a s you can see by use of the vbTab constant.

You can make your SQL string dynamic by using VBA variables as values of the WHERE conditionals. Search the Access forum for other posts where I go into the specifics of this, as you have to concatenate the variables into the SQL strings in specific ways, using symbols to define data type for SQL.

HTH:thumb

[Thepnr]

CreganTur, thanks for the speedy response I will work through the file you supplied and try to adapt it to my needs. Will post the solution if I manage to accomplish it.

Expect to wait a while though as I'm an engineer not a programmer and it's a long time since I wrote BASIC and DOS batch files which is the limit of my programming knowledge. Got to start somewhere.

Cheers

[CreganTur]

I forgot to mention that you have to set a reference (Tools -> Reference) to Microsoft Scripting Runtime to make that code work. Without it, you'll get errors!

All you need to do is change the SQL string (SELECT EmployeeId, LastName & "", "" & FirstName AS FullName FROM Employees) that's part of rst.Open to one that pulls the records you want from your table.