Using Microsoft Access and Word Mail Merge with Synapse

Discussion in 'Hardware and Software Tips and Suggestions' started by qilin, Jan 20, 2007.

  1. qilin

    qilin Member

    Microsoft Access database can be used as a frontend client connecting to Synapse-EMR Firebird server database. Steps to do this:

    1. Install Firebird program and Firebird ODBC driver on the client PC the same way as you install your Synapse-EMR server.
    2. Configure "Sysem DSN" in "Control panel - Administrative tools - ODBC Data Source Administrator", the same way as you configure your server. If MS Access in not on the server PC, the databae address need to changed accordingly,

    ip.address.goes.here:C:/path/to/remr.fdb
    or
    \\mule\d:\path\to\reme.fdb
    or
    \\ip.address.goes.here\d:\path\to\remr.fdb

    3. Create MS Access database, "get external data - link tables", select ODBC databases for files of type, select "remr" from your machine datasource.
    4. Create forms, queries and reports.

    One neat function you can use is to use MS Access and Word Mail Merge to generate customizable letters, etc. Steps for consult letter generation:

    1. Create a query "qryConsult"in MS Access, add tables "USERS", "CONSULTS", "STAFF"and "GPS"
    2.Join field [USERS].[UID] to [CONSULTS].[PATIENT], [USERS].[REFERER] to [GPS].[GID], [CONSULTS].[AUTHOR] to [STAFF].[SID], select fields needed forthe consult letterfrom each table.
    3. Create a MS Word template file, using "qryConsult" in your database file as your data source. Choose the fields and format the letter.
    4. In MS Access database file, create a form "frmVisits" to list the consults, add a button, in property window of this button - Event tab - on click- select [event procedure], use the following VBA codes. This assumes your database file is named "Synapse.mdb" and your Word file is named "LetTmp.doc" and both files are saved in C:\EMR folder. You may change the names and paths as you wish.
    5. Test and have fun.

    Notes: MS Access 2002 has "Microsoft Rich Textbox control", which displays Firebird BLOB file correctly but I haven't found a control in 2003 and 2007 that can do this. If you use MS Access 2002 and choose to use "Microsoft Rich Textbox control", you should lock the field and don't attempt to modify the data in BLOB field ("consult" field from "CONSULTS" table in this case)in MS Access, otherwise Access will add Rich Text codes and mess up your data. I am using plain text box for preview "consult" fieldin 2003 and 2007 now, it will display plain text without paragraphs, but Mail Merge works fine.
    Update: See
  2. Graham

    Graham Developer Staff Member

    I suspect that if you are using OpenOffice, you may be able to do something similar. This applies to both Linux and Windows users.


    Check out these instructions from the Firebird site on how to connect OpenOffice to Firebird using the JDBC drivers.

    If anyone gets this going .. please let us know.

  3. Graham

    Graham Developer Staff Member

    Just a quick reminder .. if you use ODBC to do reports etc, then the data is passing across the network in clear text ie. unencrypted. So, I suggest you don't use this method for remote Access reports unless you use a VPN. Opening port 3050 is also a risk for hackers to get to your database.

    Synapse client contacts Synapse EMR server thru an encrypted tunnel, and Synapse EMR server then communicates with the Firebird database using ODBC ie. an n-tier architecture.
  4. Jason

    Jason Developer / Handyman Staff Member

    How do i do that ?

    [*-)]

    Attached Files:

  5. Jason

    Jason Developer / Handyman Staff Member

    Do you mean "JOIN" the fields ? (not link)

    Attached Files:

  6. Jason

    Jason Developer / Handyman Staff Member

    [USERS].[REFER] -- do you mean [USERS].[REFERER] ? or [CONSULTS].[REFER] ?



  7. Jason

    Jason Developer / Handyman Staff Member

    I am trying to JOIN the fields in this Relationship Design Section.

    Attached Files:

  8. Jason

    Jason Developer / Handyman Staff Member

    I believe I have now allowed all Registered Members permission to attach files.

  9. qilin

    qilin Member

    Sorry Jason for the confusion. I edited and changed "Link" to "Join", it should be [USERS].[REFERER]. I wrote that in a hurry. You couldn't get any one of the files I sent you to work?

    Looks like you are doing it right. You can just drag one field from one table and drop to the other field in the other table to join them.

    And, now I see you use attachment, I thought insert picture was the only option. Will add some screenshots

    Attached Files:

  10. Jason

    Jason Developer / Handyman Staff Member

    well. If I just run your .mdb file ...

    And I search for a patient lastname ... i get this -- Private Sub LNamefilter_AfterUpdate()

    Dim strFilter As String
    If IsNull(Me.LNamefilter) Then
    Me.FilterOn = False
    Else
    strFilter = "SURNAME like ""*" & Me.LNamefilter & "*"""
    Me.Filter = strFilter
    Me.FilterOn = True

    Forms![frmPatients]![subfrmPtList].SetFocus
    Forms![frmPatients]![subfrmPtList].Form.Filter = strFilter
    Forms![frmPatients]![subfrmPtList].Form.FilterOn = True

    End If

    End Sub

    =-=-=-=-=-=-=-=-=-=-=-=-

    The yellow line gives me an error when I try to search via lastname.

    Attached Files:

  11. Jason

    Jason Developer / Handyman Staff Member

    Well I did only two things, and it seemed to work.

    (1) opened your file.

    (2) clicked the word icon.

    and it generated a Word Consult from the corresponding Synapse Consult.

    Attached Files:

  12. Jason

    Jason Developer / Handyman Staff Member

    If I keep generating different word documents, I eventually get this.

    Attached Files:

  13. Jason

    Jason Developer / Handyman Staff Member

    Once, I've been elsewhere in Access. How can I get back to this Patient List ?

    Attached Files:

  14. qilin

    qilin Member

    [quote user="Jason"]

    strFilter = "SURNAME like ""*" & Me.LNamefilter & "*"""
    Me.Filter = strFilter
    Me.FilterOn = True

    Forms![frmPatients]![subfrmPtList].SetFocus
    Forms![frmPatients]![subfrmPtList].Form.Filter = strFilter
    Forms![frmPatients]![subfrmPtList].Form.FilterOn = True

    [/quote]

    Change "SURNAME like....
    to:
    "USERS.SURNAME like
    I can put a button on all other forms to go back to the list view
  15. Jason

    Jason Developer / Handyman Staff Member

    Is there another way ? (Manually).
  16. qilin

    qilin Member

    I have found ways to use Microsoft Rich Textbox control in MS Access version 2003 and 2007 thanks to the help of my programmer friend. Blob field will have correct display in all these versions.

    Steps to do this in 2007:
    1. copy richtx32.ocx to windows\system32 folder if you don't have one.
    2. go to visual basic window, tools - references - browse button and add richtx32.ocx file
    3. open regedit, go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compability\{3B7C8860-D78F-101B-B9B5-04021C009402}
    double click "Compatibility Flags", change value to 1
    4. Close regedit and restart MS Access, you should be able to seethis control listed in "Insert ActiveX Control" from form design view. Add this control and link to "CONSULT" Blob field. Go back to form view. You should see all the text format corretly displayed.
    The above GUID for this control is the same for 2002 and 2003, I assume you should have the same key, If you can't find this register key, I will tell you how to look for it.

    For 2003, the file should already be in system32 folder. If it's not registered, you need to go to menu "tools - ActiveX Controls - Register button" to register it. Then follow the same Steps 3-4 as above.

    Attached Files:

  17. qilin

    qilin Member

    [quote user="Jason"]
    Is there another way ? (Manually).

    [/quote]

    Sure, Click the big Office button on the right upper corner, click "Access Options" on the bottom, under Current Database, check "Display Navigation Pane" under Navigation.

    You should have a navigation pane now on the left side of your window, you can navigate to different tables, forms etc.

    Attached Files:

  18. qilin

    qilin Member

    Screenshot for regedit mentioned above. Courtesy of my helpful programmer friend.

    Attached Files:

  19. qilin

    qilin Member

    Not sure why. Are you using this in office or home LAN or remotely? If this happened only remotely, I am not too concerned since I don't plan to use it remotely in the future for security reason as Graham pointed out why.
  20. Jason

    Jason Developer / Handyman Staff Member

    I did this a while back when I was having richtx32.ocx errors. Maybe I have the wrong version ?

    Done.

    Hexadecimal or Decimal ?


    What is the name, I don't see it in the List.



    [​IMG][​IMG]
    Lost me.


    Same for 2007.

    Maybe that is what I need to do, I can't find it in 2007.



    Rich Text Would be nice :)

    Attached Files:

Share This Page