redleatherbound (redleatherbound) wrote in databases,

  • Mood:

export question

Hello All...

Here's a doozy of a situation. I am working on an Access database of hospital patients that needs to export each record as a separate Text file so it can then be imported into a different system accessed hospital-wide. Sadly, the other system requires that it be individual files per record.

I have all the specs for the text file worked out, and the export specification, but having difficulty with the next step.

What I am trying to do is set up a VBA routine for each file to be named at the time of generation automatically via a timestamp and some unique identifier, either an autonumber or some combination of characters that will keep the files unique in the event that the export be run again (which will happen, and need to be addressed as separate entities).

Anyone have any suggestions?

Thanks in advance!
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

I would suggest making a separate directory for each output run so that your files don't all get mixed up together. If the name of each file still needs to be unique across the directories, a YYYY-MM-DD format is easy to read (no ambiguity over which is the month and which is the day) and sorts chronologically.

Apart from the date, the records you're working with should already have some unique identifier. Why not just use that?
That might be a possible option, but the desire is for the exports to be run automatically, behind-the-scenes, and the other system will tap into these files whenever they need to (that's not my area, so I don't know their plan).
Most likely, the other system needs a specific directory to look for these files, which is why putting the timestamp in the file name is the best solution.
At the moment we have about 2000 records or so that need to be exported as individual files.
Moving forward, the volume will probably be 4-5 patient cases per day, with occasional revisions (hence the timestamp in the filename).

While the records themselves contain the unique identifier, it would be "best" if the file name could pull that unique identifier as well.
I guess in my mind I see the file name reading as follows:
1234-2008-10-29-13-10-25 (autonum-yyyy-mm-dd-hh-mm-ss, or something like that).

Does this clarify the need? Is this possible? Any help on the TransferText VBA coding would be helpful.
I think something with a little more formatting, like "Record #1234, 2008-10-29 13-10-25.txt", might be a little easier to read, but both are legal filenames (unfortunately, colons in the time are out).

The code should probably be something along the lines of:

Public Sub ExportAll
   Dim rst As RecordSet

   Set rst = CurrentProject.Connection.Execute("Your query here")
   Do Until rst.eof
      Call ExportOne(rst)
End Sub

' Output the current record from the recordset.
Private Sub ExportOne(rst As RecordSet)
   Dim FileName As String, fileNum As Integer

   FileName = "Record #" & rst!id & ", " & Format$(Now, "YYYY-MM-DD HH24-MM-SS" & ".txt"
   fileNum = FreeFile

   Open FileName For Output As #fileNum 
   Print #fileNum, rst!Stuff
   Print #fileNum, rst!MoreStuff
   Close #fileNum
End Sub