Export records to text file?

[Pecanfan]

Hi,

I'm trying to come up with a way of generating 'warning' e-mails based on
specific criteria within an Access (2003) database. I've decided to do this
by exporting specific information to a text file which then gets parsed by a
simple shell program and processed by a 3rd party SMTP engine (Blat).

With my VERY limited knowledge of VB and Access coding, this is what I've
come up with so far:-
Private Sub Command0_Click()

Dim aSQL, bSQL, drop As String
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb

drop = "DROP TABLE temp"
aSQL = "SELECT mytable.stuff INTO temp FROM mytable WHERE mytable.flag=0"
bSQL = "UPDATE mytable SET mytable.flag = 1 WHERE mytable.flag=0"

' Copy flagged records into temporary table
db.Execute aSQL

' Reset flag
db.Execute bSQL

' Export contents of temporary table to text file
Set rs = db.OpenRecordset("SELECT * FROM temp")
Open "c:\myfile.txt" For Append As #1
Write #1, Somehow put some records here!!!
Close #1

' Get rid of temporary table
db.Execute drop

End Sub
Can anyone tell me if I'm vaguely heading in the right direction and also
offer some advice in terms of actually writing the recordset into the text
file?

Many thanks in advance!

Andy

P.S. If this isn't the best NG for this please can someone point me in the
right direction - I've also posted to microsoft.public.access.

[Douglas J. Steele]

Using the TransferText method would be simpler than using VBA file output
commands.

I'm not really sure why you're using a temporary table, though. Couldn't you
simply create a query

SELECT mytable.stuff FROM mytable WHERE mytable.flag=0

and export that query?

If you decide to use the VBA file output (and there's really nothing wrong
with it), I would strongly suggest not simply using #1 as the file tag,
especially if other applications are going to be running at the same time.
Look up the FreeFile function in the Help file.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Pecanfan" <pe******@no.spam> wrote in message
news:11***************@smtp-1.griffin.com...

Hi,

I'm trying to come up with a way of generating 'warning' e-mails based on
specific criteria within an Access (2003) database. I've decided to do this by exporting specific information to a text file which then gets parsed by a simple shell program and processed by a 3rd party SMTP engine (Blat).

With my VERY limited knowledge of VB and Access coding, this is what I've
come up with so far:-
Private Sub Command0_Click()

Dim aSQL, bSQL, drop As String
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb

drop = "DROP TABLE temp"
aSQL = "SELECT mytable.stuff INTO temp FROM mytable WHERE mytable.flag=0"
bSQL = "UPDATE mytable SET mytable.flag = 1 WHERE mytable.flag=0"

' Copy flagged records into temporary table
db.Execute aSQL

' Reset flag
db.Execute bSQL

' Export contents of temporary table to text file
Set rs = db.OpenRecordset("SELECT * FROM temp")
Open "c:\myfile.txt" For Append As #1
Write #1, Somehow put some records here!!!
Close #1

' Get rid of temporary table
db.Execute drop

End Sub
Can anyone tell me if I'm vaguely heading in the right direction and also
offer some advice in terms of actually writing the recordset into the text
file?

Many thanks in advance!

Andy

P.S. If this isn't the best NG for this please can someone point me in the
right direction - I've also posted to microsoft.public.access.

[Pecanfan]

> Using the TransferText method would be simpler than using VBA file output

commands.
I did consider this but I couldn't find a way of appending to the text file
using this method. Am I missing something here?
I'm not really sure why you're using a temporary table, though. Couldn't you simply create a query

I did have a good reason for this, but can't remember for the life of me
what it was. :-)

Cheers for the info!

Andy