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

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
 With rst
   'Write Field names to Output File, delimiting by '|'
   For intFldCtr = 0 To .Fields.Count - 1
     strBuild = strBuild & .Fields(intFldCtr).Name & " | "
     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 & " | "
       Print #1, Left$(strBuild, Len(strBuild) - 3)      'Each Record, remove ending ' | '
         strBuild = ""     'Must RESET for Next Record
 End With
 Set rst = Nothing


End Sub

Open in new window

I'm using Access 2013 and am a novice at coding VBA
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


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