Actions on Startup

[jlwark]

I don't use Access very much, but I am currently required to write a program [or database function] that outputs the results of a query into a text file that is tab-delimited. Then some scripts go to work on the file. I was wondering how to get this to work so that I just open the database and I then have these query results saved in a specific directory.

Thank you in advance.

 

[min]

I am not sure if you can write to a txt file directly from Access. But here is a work around solution. Put this function into form or anywhere else and when it runs, it output your query into a word doc first, then save it as a tab delimited txt file.

-------------------------------------
Function CreateTxt()

Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim WordRng As Word.Range
Dim WordPar As Word.Paragraph
Set WordObj = CreateObject("Word.Application")

Dim rst As Recordset
Dim sSQL As String
Dim db As Database

Set db = CurrentDb()
sSQL = "Select * from MyTable"
Set rst = db.OpenRecordset(sSQL)

With WordObj
.Visible = False
.WindowState = wdWindowStateNormal
.Documents.Add
Set WordDoc = WordObj.ActiveDocument

Set WordRng = WordDoc.Range
With WordRng
.Font.Bold = True
.Font.Italic = True
.Font.Size = 16
Do While Not rst.EOF
.InsertAfter Trim(rst(0)) & vbTab & Trim(rst(1)) & vbTab & Trim(rst(2)) & vbTab & Trim(rst(3))
.InsertParagraphAfter
rst.MoveNext
Loop
End With
Set WordPar = WordRng.Paragraphs(3)

.ActiveDocument.SaveAs "c:\temp\test.txt", wdFormatDOSText
.Quit
End With

End Function

-----------------------

Good Luck!

Min

 

[min]

Or use this method if you already has a query created
----------------------
TransferText Method Example

The following example exports the data from the Microsoft Access table External Report to the delimited text file April.doc by using the specification Standard Output:

DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

-------------------------------------

 

[jlwark]

The Line
DoCmd.TransferText acExportDelim, "Standard Output",_
"External Report", "C:\Txtfiles\April.doc"

seems to be what I'm looking for, but like I said, I don't use Access very much. How do I implement that [as a macro or a module]? Is the entry "acExportDelim" the name of a query or a table.

The way I see it is
command
object for command [table or query]
print to standard output
capture it in a file called C:\Txtfiles\April.doc

I don't know what the "external report" is for or the "_".
Is there any other place that I can find information on the DoCmd.TransferText method?

 

[jlwark]

Well, I figured out how to do what I want using macros [transfertext macro] and it works great.

Now, if I could only get the Win98 task scheduler to run that database that contains those macros once each day, I'd be laughing.

Thanks guys.