Script to Print a DB Table Content to CSV Paues on Last Two Records Being Read

jtflex
jtflex used Ask the Experts™
I am trying to read a series of records in a Access DB table and export them to a csv file with "|" delimiters.  I search some sample scripts and it's mostly working but the issue I'm finding is that every time I execute the vba code, it reads and prints to the csv file up until the last two records.  The second to the last record is partially recorded to the csv file and then the subroutine pauses.  If I run the  sub routine again, I get a Run time error 55 (file is open)  but it also finishing up the writing of all the records.

Here is the coding I am currently using:

Note; the 1st part of the routine is to pint out the record headers.  The 2nd part prints out the record content.

Public Sub Print_2_CSV()


Dim MyDB As DAO.Database
 Dim rst As DAO.Recordset
 Dim intFldCtr As Integer
 
 Set MyDB = CurrentDb
 Set rst = MyDB.OpenRecordset("Case Information File", dbOpenSnapshot)
 
 Open "C:\Test\Case Information.txt" For Output As #1
 
 rst.MoveFirst
 
 With rst
   'Write Field names to Output File, delimiting by '|'
   For intFldCtr = 0 To .Fields.Count - 1
     strBuild = strBuild & .Fields(intFldCtr).Name & " | "
   Next
     Print #1, Left$(strBuild, Len(strBuild) - 3)    'Field Names, remove ending ' | '
       strBuild = ""     'Must RESET
 
   Do While Not .EOF     'Values in Fields delimited by '|'
     For intFldCtr = 0 To .Fields.Count - 1
       strBuild = strBuild & .Fields(intFldCtr).Value & " | "
     Next
       Print #1, Left$(strBuild, Len(strBuild) - 3)      'Each Record, remove ending ' | '
         strBuild = ""     'Must RESET for Next Record
           .MoveNext
   Loop
 End With
 
 rst.Close
 Set rst = Nothing

 

End Sub

Open in new window


I'm using Access 2013 and am a novice at coding VBA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Without having a sample i am only assuming but i see that you haven't closed the output file
Before End Sub
Close #1
End Sub

Open in new window

Author

Commented:
Perfect,
That was it.  And it makes sense why I was getting the error message.
Thank You!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial