Link to home
Start Free TrialLog in
Avatar of isurgyn
isurgyn

asked on

Issues with TransferText method in Access 2007

Hi,

I am using Access 2007 to link to a medical device that runs proprietary software.  The device stores relevant medical detail in a csv file.  My code is designed to link to that csv file and allow the user to query the linked table, use the query to populate a subform and then populate fields on the main form when the user selects a record in the subform.

Fortunately, all of that works pretty much as stated.  

One wrinkle is that the csv file is frequently updated as patients have testing done during the day.  As a result, I believe that I need to unlink the csv file so that the medical device can write new data into it.  I then relink briefly to perform the functions described above and then delete the linked table to turn control back to the medical device.  

My challenge is two-fold.

1.  The current code works intermittently.  Sometimes the subform is populated, sometimes not.  However, in those times when it is not populated I can open the linked table and / or run the query that the subform is based upon and the table is complete and the query runs perfectly.  So, I believe that this intermittent operation may be due to a time lag.  Perhaps my code is running the query and opening the subform before the new linked table is fully created?
2.  Every time the linked table is created the navigation pane opens.  I included code to close it again but it is still very aggravating.  Any way to fix that...

Is there an alternative method to accomplish this?  I can probably live with the navigation pane wonkyness...:(
 

    On Error GoTo ErrHandler

    Select Case Me![KeratometryLabel].Caption
   
    Case "Show Keratometry:"
   
        DoCmd.DeleteObject acTable, "tblLinktoPentacam" '<===should be unnecessary but
                     'ensures that the TransferText will create the correctly named table in case
                     ' the table hasn't been deleted otherwise the query will access the old table
       
        DoCmd.TransferText acLinkDelim, "Summary Import Specification", _
        "tblLinktoPentacam", "R:\SUMMARY.csv", True
       
        DoCmd.SelectObject acTable, "tblLinktoPentacam", True
        DoCmd.RunCommand acCmdWindowHide
       
        Me.frmPentacamKPachymetry.Requery
        Me.frmPentacamKPachymetry.Visible = True
        Me![KeratometryLabel].Caption = "Hide Keratometry:"
        'button caption is set to Hide Keratometry in btn event
       
    Case "Hide Keratometry:"
   
        DoCmd.DeleteObject acTable, "tblLinktoPentacam"

        Me.frmPentacamKPachymetry.Visible = False
        Me![KeratometryLabel].Caption = "Show Keratometry:"
        'button caption is set to Show Keratometry in btn event
             
    End Select
   
    Exit Sub
   
ErrHandler:

    'Ignores DeleteObject command if the tblLinktoPentacam doesn't exist
    If Err.Number = 7874 Then Resume Next

    'Traps error if the Pentacam and CSV file are offline
    If Err.Number = 3044 Then
        MsgBox "The Pentacam does not appear to be turned on, is not connected to the network or you need to map a network drive on this computer to connect to it."
        Exit Sub
    End If
   
    'Traps error if the frmPentacamKPachymetry has the focus
    If Err.Number = 2165 Then
        MsgBox "Please select another control and then Select 'Hide Keratometry'"
        Exit Sub
    End If

    MsgBox "Err = " & Err.Number & " Error Description = " & Err.Description
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Please check to see if there is a way to connect to the Medical Device's "proprietary software" directly.
...Connecting to a text file seems a bit primitive...

Most "devices" will scan into some sort of "database" (or some internal data structure).
So it is odd that your device will *only* save its data to an external (unsecured, unencrypted) text file.
What happens if the file is deleted by accident, ...?
How would you know if the file was edited manually?
You need to be *sure* of these things, as I have never heard of a modern device storing data in this fashion... (unsecured text file)
Please contact the manufacturer of this device and tell them what your goals are.
Perhaps they can suggest (or create) something a bit more reliable.

Surely you can link, break the link, and then re-link, (Import/Delete, re-import)...but this will be hard to manage, as it is not clear how you will know when the device will need to access the text file.


JeffCoachman
Avatar of isurgyn
isurgyn

ASKER

Hi Jeff,

I started on that path first as it is the ideal.  However, medical device developers are typically more interested in protecting their algorithms than providing data.  To interface with an EMR you can print off a paper image of the study and scan it into the patient record or they provide a DICOM or jpg image of that same view.  Kind of like having your accountant send you a picture of a spreadsheet instead of the spreadsheet itself.  Even that is done completely stupidly.  Can't even name the jpg file in a manner that provides a unique identifier or use a naming format that can be inserted into a sql table.  If your name is John Smith or Shane O'Malley we've got a problem.

That said, you are correct that they do not use a csv file for storing the data.   I'm not entirely certain why they provide a series of csv files that store various aspects of the data but my best guess is that it is for ease of performing scientific studies.  Makes it easy to export the data and the user can then compare patient cohorts etc.  There is nothing about any of this in any of the user manuals or in technical monographs.

As for the real data, it might be helpful to know more about how the device works.  Basically, the device takes 25 High definition camera images of the cornea, stitches them together and then provides analysis of these images such a tissue thickness, shape etc.

There is one mdb datafile on the system that has two tables.  The first table named tblPatients is pretty basic and contains patient names, external id, internal id, date of birth, a few comment fields and a "filename" field. The "filename" appears to be the name of a file but cannot have any data about specific exams as there is only one filename per patient.  The filenames consist of several letters of the patient last name and first name and the extension appears to be 3 random letters.  These files might be txt files but probably don't have much relevance.  I haven't attempted to open one but might be interesting but likely won't help this problem.

The second table is the examination table called tblExaminations.   This should be the paydirt and where the exam data should be stored.  Obviously has the internal patient id as primary key, exam id, a few other misc fields like exam date and then one field called "BitmapFile".  Otherwise, there is no real data in this table.  However, all of the filenames in the BitmapFile field have a .SPR extention.  This suggests to me that they are a MS FoxPro database format but that is a guess.

My best guess is that they store the raw images and then use their algorithm to reconstruct the analysis when the user wants to view a specific image rather than store fixed numbers in a database file.  Why they would mix mdb and foxpro together doesn't make much sense either.

So, I don't think there is a way to access the 'data' other than the csv files as I am not certain that it is stored in a table but rather dynamically derived from the raw images whenever a specific view combination is selected by the user.  Alternatively, the data may be stored in these .spr files but I have no idea how to open them other than to try renaming one as a bitmap or txt file and see what I can find.

So, unless I can figure out what a .spr file does and if it contains data I think I am out of luck.  The manufacturer is in Germany.  All repairs etc must be done by shipping the system back to Germany.  All we have in the US is salesmen.  The party line is print and scan or use dicom or jpg images.  For reference, dicom images do contain data but nothing more useful than patient ID number.  If you want data then you can enlarge the dicom or jpg image and use zonal OCR to guess at the numbers, convert them to data and store them into your database.

The good news is that at least with this device it is networkable.  Most cannot even be connected to a network.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of isurgyn

ASKER

That is correct. It likely doesn't make sense to store this type of data in a database format. The device takes 25 High Def photos and from those creates a 3-D wire-mold model of the entire anterior segment of the eye.  So there is going to be some data interpolation to accomplish that.  There are also thousands of derived metrics that a clinician might want to view.

For example, if I wanted to know the thickness of the cornea the device purports to provide 20,000 thickness values across the cornea. To store those values in a database would require at least 60,000 fields per record (thickness value plus X and Y coordinates). So to store these and thousands of additional data on local radius of curvature, tissue density etc would require 10's of thousands of fields per record.

So my guess is that the mdb file simply has the file name for the .spr file that consists of the data that describes the 3D wire-mold. When the user loads a specific view the internal algorithm performs the needed calculations from the 3D model and places the derived data view into the GUI.

The import / export functionality on the device doesn't export data in a csv or other  databas format. It is used to export the 3D info onto a USB so I can send the image to a friend across the country who can then import that data file into their identical device and view the images just as if the study had been performed on that device.
First I would like to take the time to thank you for providing such detailed information.
;-)

I would first remove any code not absolutely necessary for the functionality you need here
What event is your code actually running on?
...perhaps all you need is a strategically placed "DoEvents" command
Avatar of isurgyn

ASKER

I like your idea of changing the  

DoCmd.TransferText acLinkDelim
To
DoCmd.TransferText acImportDelim

That way I don't need to create a new table which will solve the wonky navigation pane issue. It will likely also fix the time delay problem as importing data into a table should run more quickly than linking to it.  And if needed a few doevents should work.

I am out of the office for a few days so won't be able to test this until next week.

Thank you!
Avatar of isurgyn

ASKER

I also just thought about the option of creating an ADO recordset instead of a table since I only need the data for about 30 seconds.   The first row in the csv file has column headers so I think the code should look something like this:

Dim conn As New ADODB.Connection
Dim rstSummary As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String

path = "R:\"
 
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& path & ";Extensions=asc,csv,tab,txt;HDR=YES;Persist Security Info=False"
   
   rstSummary.Open "Select * From SUMMARY.csv", _
       conn, adOpenStatic, adLockReadOnly, adCmdText

Do While Not rstSummary.EOF
      MsgBox rstSummary(0)   'Find the records with the matching PatientID
      rstSummary.MoveNext
Loop

That should eliminate the need to create a table, delete records and then repopulate it.  Once I navigate through the csv data using a filter, I would set the datasource for the subform to the rst.Summary recordset so there wouldn't be any lag in the data being available when the subform is made visible.  

Not a big deal since I would kill the connection and set the rst to nothing but I am not certain how that would affect the device's ability to write to the csv file.

What do you think about this solution?
<What do you think about this solution? >
Being that this may never be "perfect", ...just try it.

If it performs better than what you had before, then roll with it.

Anything I could propose would just be some variation of your basic concept.
Avatar of isurgyn

ASKER

Ok I did try the ADO recordset idea.  Unfortunately, because there is no mechanism to tell the code where one field ends and another begins (ie. the semicolon is the delimiter in this particular instance) the code assigns one entire record as one field.  It would be possible to split it up but there are perhaps 30 or more data fields so that is just to much work to parse out.  It could be done but really not worth the effort.

So I decided to just use the TransferText method.  I still trigger the code from the event on a form label that makes the subform visible so added some DoEvents code to allow the table to fill with data before the form becomes visible.  Works great on both Windows XP and Windows 7 devices.

It would likely be ideal to create a small form that would be installed on the medical device so that the operator could upload the data to the linked table on the server whenever they complete a test process.  For my purposes that is more work than it is worth as the device is typically only used by the technician that is also pulling up the data record at a later date.

Anyways, for completeness I am posting the final working code:

    Dim i As Integer
   
    On Error GoTo ErrHandler

    Select Case Me![KeratometryLabel].Caption
   
    Case "Show Keratometry:"
     
        DoCmd.OpenQuery "qryEmptyLinktoPentacam"
       
        DoCmd.TransferText acImportDelim, "Summary Import Specification", _
        "tblLinktoPentacam", "R:\SUMMARY.csv", True  'this code imports the csv file
                   
        For i = 1 To 30
            DoEvents
        Next i
       
        Me.frmPentacamKPachymetry.Requery
        Me.frmPentacamKPachymetry.Visible = True
        Me![KeratometryLabel].Caption = "Hide Keratometry:"
        'button caption is set to Hide Keratometry in btn event
       
    Case "Hide Keratometry:"
   
        Me.frmPentacamKPachymetry.Visible = False
        Me![KeratometryLabel].Caption = "Show Keratometry:"
        'button caption is set to Show Keratometry in btn event
             
    End Select
   
    Exit Sub
   
ErrHandler:

    'Ignores DeleteObject command if the tblLinktoPentacam doesn't exist
    If Err.Number = 7874 Then Resume Next

    'Traps error if the Pentacam and CSV file are offline
    If Err.Number = 3044 Then
        MsgBox "The Pentacam does not appear to be turned on, is not connected to the network or you need to map a network drive on this computer to connect to it."
        Exit Sub
    End If
   
    'Traps error if the frmPentacamKPachymetry has the focus
    If Err.Number = 2165 Then
        MsgBox "Please select another control and then Select 'Hide Keratometry'"
        Exit Sub
    End If

    MsgBox "Err = " & Err.Number & " Error Description = " & Err.Description