Export records to text file?

  • Thread starter Thread starter Pecanfan
  • Start date Start date
P

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 comp.databases.ms-access.
 
I think it's easier to use Access command

docmd.Transfertext acExportDelim, "SpecificationName", "TableName",
"FileName", 1

Or just try to read help about this command.
 
You're headed in the right direction, and actually almost there:

Set rs = db.OpenRecordset("SELECT * FROM temp")

rs.MoveFirst

Open "c:\myfile.txt" For Append As #1

Do Until rst.EOF
Print #1, rs!Field1 & ", " & Format(rs!Field2, "0.000") & ", " _
& Format(Date, "mmddyyyy") & ", " & rs!Field3

rs.MoveNext
Loop

Close #1
MsgBox "Done!"

Using Print # instead of Write # stops the surrounding of each value with
double quotes and dates with # signs. It also stops automatic commas, so you
see how I put them in. Depending upon what is eventually reading your file,
use whichever works for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
You're headed in the right direction, and actually almost there:
Set rs = db.OpenRecordset("SELECT * FROM temp")

rs.MoveFirst

Open "c:\myfile.txt" For Append As #1

Do Until rst.EOF
Print #1, rs!Field1 & ", " & Format(rs!Field2, "0.000") & ", " _
& Format(Date, "mmddyyyy") & ", " & rs!Field3

rs.MoveNext
Loop

Close #1
MsgBox "Done!"
Click to expand...

That's grand - cheers! Out of a matter of interest, is the rs.MoveFirst bit
necessary? It results in an error if no records are selected. I've taken
it out and all seems fine.

Ta,

Andy
 
Pecanfan said:
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 comp.databases.ms-access.
Click to expand...
 
Back
Top