Home Forums Discussions Problems AC to EZ Claim Export - Place of Service

Hi gkfahnbulleh:

Your points are well taken, but I don't discuss my perception of SQL vs. Jet lightly.

First, a little about me. I'm a full-time physician (oncologist, hematologist, internist) with a private practice in Arlington, Virginia. I have a partner and 4 employees. My office has 8 working computers (1 server and 7 nodes).

I'm an avid programmer, too, going back to 1990 when I first learned to program in Paradox for DOS, a very decent file-server relational database. In about a year I made my first EMR/PMS. In 1995 I ported it over to MS Access. Since then I've also programmed in the MS Word platform as well as Satellite Forms (for the Palm- I once had a Palm-based EMR too).

Oh, before we begin our discussion- some clarifications for the non-technical: "Access" means the program that usually is split into a front-end and a back-end. You can have an "Access" database with an SQL backend, and you can also have an "Access" database with a Jet 4 backend. What we'll be discussing here is the difference between Jet 4 and SQL as a backends.

>>> The AC database for each customer is only GROWING not shrinking. As users request more features more tables are going to be added further INCREASING the amount of data the system will have to store. Your suggestion seems to be to run it as is and HOPE you never reach that threshold. Hope is not a strategy when it comes to software design and architecture.

My patient database is now 18 years old and I've seen about 20000 new patients. My MS Access-based program is not unlike AC, except possibly that it's about twice it's size, with about 800 objects (forms, reports, tables). It's PMS I realized yesterday when I downloaded it for the first time is very similar to EZ Claims. I also have an oncology module which helps me with chemotherapy. My largest table, even after occasional compacting, is my accounts table, which holds approximately 71000 records. In the late 1990's my LAN ran with an 11 MBPS, from about 2000 thru the present day it's run at 100 MBPS, and I'm in the process of upgrading my LAN to a Gigabit LAN (1000 MBPS). At no time did my software run poorly, even at the lowly 11 MBPS LAN speed. A file server topology has served me well for a long time.

I'm still waiting for that "threshold", and my belief that the Jet 4 backend is more than enough for a small to medium sized office is not based on "hope". I'm more than ready, though, to go to SQL if and when I need to do so. I do feel that physicians are being led to believe that they have to buy a Maserati when a Honda will do- and many vendors play into that game due to their belief that-

1) physicians have large pockets full of cash, and more money can be made with an SQL backend, and
2) their sincere belief that they lose sales to other vendors who continue the fallacy that not having an SQL backend means that an EMR is second-rate, which is not true, and
3) the back end of an Access data base can be upgraded. MS Access also allows for the creation of an adp file to work with the back end of an Access data base upgraded to SQL. When using an ADP file, you select the server and data base to connect to, much like a SQL interface with either windows nt or other password identity methods. The ADP files follow the SQL structure a little differently than a normal mdb access data base.

>>> I understand the misgivings of those who have dealt with poor implementations in switching from Access to SQL Server: that was not because SQLServer is bad, but because the developers were not sufficiently versed in the new product and did not have the requisite skillset to make such a change.

Same goes with poorly written Jet 4 backends. A poorly written Access/Jet 4 file-server database leads to poorly performing software.

>>> Microsoft Jet is a file-sharing database system

True, but remember- in the days where there were only 11 MBPS LANs I once read in one MS Access book that Access would never run well on a P2P LAN. It did in my office, and well. Nowadays, with Megabit and Gigabit LANs the difference between a file-server and a client-server performance is nil in the small to medium sized office setting.

Also, you can also design look up tables to be local on the client to avoid network congestion as the client is not having to access the back end for look up information.

>>> Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these type of applications, the best solution is to switch to a true client/server-based database system such as Microsoft Data Engine (MSDE)[SQL Server Express] or Microsoft SQL Server.

Sure, an SQL is appropriate in an airport, in a bank, and in the NYSE. It's overkill in a small to medium sized medical office. MS SQL Server is expensive and will double or triple the cost of AC, both up front and in the cost of hiring IT professionals to manage it.

You can use MS SQL Server Express, but it has a governor to only allow 5 concurrent connections at a time, unlike Jet 4 which can be used in up to 20 consecutive connections without any significant degradation in performance, but you can push the limits (up to 255 connections actually) if you are a real cheap b*stard!

>>> If, for any reason, a process cannot be completed, the file can be left in an incomplete or a corrupted state. Two examples of when a process may not be completed is when a client is terminated unexpectedly or when a network connection to a server is dropped.

That rarely happens, and the only time that I had problems was with a LAN that had Windows 2000 mixed in with Windows ME. I used the latter as my "back end" which caused an undocumented Windows bug that would drop connections and led to a few corruption problems.

In the modern Win XP P2P LAN table corruption is a rare event, and when that occurs it's NOT a major problem (usually affects one record)... I see this occur maybe once every 2 to 3 years.

>>> stored procedures and triggers.

A MS Access form has numerous events associated with all of the form elements, and you can save SQL queries either as objects or in code, which can be called up at any time. These are the backbone to great Jet 4 development too...

I always save my SQL queries in my code so that when I convert my software to an executable mdb file, they are hidden.

>>> Lastly Charting and Graphing ARE NOT, repeat ARE NOT, an inherent function of the DATABASE engine.

The MS Office software comes with the MS Office Graphic Library activex control whose reference can be set, essentially making this an "inherent" feature. Pretty nifty, really. You can do that with any of the different MS Office softwares (s.a. Access, Word, PowerPoint, Excel, etc). The companies you mention make other activex controls that adds extra power, but this comes at a significant cost.

LASTLY...

Access still has it's place and there is always the big debate of whether Access or SQL is the right answer. The answer I think depends highly on the user and what they need to accomplish and how. For the small peer to peer office needing a mutli user program Access can still fit the bill quite well, as AC has done so well over the years . The queries are better placed on the client instead of the back end, but the options are there for either/and or both.

If you beef up the equipment used in a network, i.e. a server pc with at least 1-4 GB of ram, that also helps out in serving the data to the clients. Clients should also have beefed up ram, with at least 1 GB (I prefer 2-3 GB). If you have a rather large program, you can also split the back ends to have 2 instead of one, so again, many design options and choices.

This is an important topic in that for a small office to purchase the obviously super-robust entails that purchase of not only an expensive server, but also the up-front $1000+ SQL fee and also the technical expertise to maintain this complicated setup. The savings of a Jet 4.0 vs. an SQL is wide. An SQL can total ~ $15000 for a multi-processor RAID server with a multi-license SQL backend vs. a mere ~$3000 for a setup with 6 ebay barebone computers in a 1000 mbps LAN configuration. The Jet 4.0 backend is free, expandable to reasonable limits, and easy to maintain (and program).

The overall stability and speed will not differ much between the 2 setups in a small to medium office setting, but the Jet 4.0 customers will be happier since in these Medicare-cutting times they will have more cash in their pockets at the end of the day.