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 for the consult letter from 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.
Dim objWord As Object
Dim myDoc As Word.Document
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Documents.Open FileName:="c:\EMR\LetTmp.doc"
Set myDoc = Documents("C:\EMR\LetTmp.doc")
With myDoc.MailMerge
.OpenDataSource _
Name:="c:\EMR\Synapse.mdb", _
ReadOnly:=True, _
LinkToSource:=True, _
SQLStatement:="Select * from [qryConsult] where ([ConsultID]) = " & Forms![frmVisits]![CONSULTID]
.Destination = wdSendToNewDocument
.Execute Pause:=False
End With
myDoc.Close SaveChanges:=wdDoNotSaveChanges
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" field in 2003 and 2007 now, it will display plain text without paragraphs, but Mail Merge works fine.
Update: See posts later in this thread for how to add "Microsoft Rich Textbox Control" in MS Access 2003 and 2007.
To enable spell check for "consult" field in MS Word, you need to use {QUOTE {MERGEFIELD CONSULT} \* CharFormat }
If I can have previledge for attachments here, I can add some screenshots. The above is more for people with some experiences in MS Access and Word. For beginners who don't understand the above, feel free to ask or go over some tutorials, there are tons of them out there.
