export text and create file for each record?

  • Thread starter Thread starter Gandalf Morgck
  • Start date Start date
G

Gandalf Morgck

Hello specialists!

Google und Co. unfortunately, could not help me up to now, so maybe
anybody here can help?.
I store product data in a small Access DB to for export to another application .
This functions very well so far. Now I'd like to add some feature, that creates
an extra text-file for each record, when exporting the Access DB to my .csv
(data is exported by TransferText to .csv)

For Example:
Article abc
article xyz
....

After exporting, the export.csv contains all data for each record in one line.
parallely in the directory " C:\irgendwas \ " the files abc.txt and xyz.txt were
created. These .txt files also contain field values from the Access DB

For example:

Price=1,50
Supplieer=Müllerhuber
etc.

(quite similar to "ordinary" .ini file)

Question: Is that possible with Access? If yes - how? If not - how I could manage it otherwise?

Tia & Cheers,
Gun.

PS: School English sucks ;-)
 
You can use the Print # statement to write text to a file.

Something like the following untested air-code should work:

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim intFile As Integer
Dim intLoop As Integer
Dim strFile As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT * FROM MyTable")

Do While Not rsCurr.EOF
strFile = "C:\irgendwas\" & rsCurr!Article & ".txt"
intFile = FreeFile()
Open strFile For Output As #intFile
For intLoop = 1 To rsCurr.Fields.Count
Print #intFile, rsCurr.Fields(intLoop).Name & "=" & _
rsCurr.Fields(intLoop).Value
Next intLoop
Close #intFile
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

This assumes that you're trying to do this for a table named MyTable, and
that there's a field in that table named Article which you're going to use
as the name for each file.

Also, it uses DAO. If you're using Access 2000 or 2002, a reference isn't
set by default to DAO, so you'll have to add one. With any code module open,
select Tools | References from the menu bar, scroll through the list of
available references until you find the one for Microsoft DAO 3.6 Object
Library, and select it.
 
Back
Top