[AccessD] New database design for MS SQL

MartyConnelly martyconnelly at shaw.ca
Fri Apr 6 19:42:44 CDT 2007


In Canada with Web based Apps on health and legal sites, you will
have to soon consider using two tier authentication for security to 
conform with.
The Personal Information Protection and Electronic Documents Act,
also known by the awkward acronym PIPEDA, which came into full effect on 
Jan. 2004.
There have been several sites in Ontario that have been compromised when 
password only.
Nurses were leaving userids etc on postit notes and patients were 
looking through
STD reporting sites that were supposed to be doctor only access.
So it is biometrics or dongles or USB keys (some with cyclical keys are 
into the $20 range).
Forget fingerprint devices, Discovery channel showed a method to defeat 
this 6 months ago.
By the way this will probably start to apply to HIPPA and European Data 
Privacy Acts.
This isn't being enforced yet but will be soon.


Jim Dettman wrote:

>Charlotte,
>
><< It offers so much more flexibility than our beloved Access can right
>now.>>
>
>  That's what I've been thinking.  I am worried that they are going to want
>a web interface for their nurses that are remote and possibly at some point
>give Doctors and Attorneys access to the system.  
>
>Jim.  
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
>Sent: Friday, April 06, 2007 1:11 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] New database design for MS SQL
>
>I don't know VFP, but I'd say go with the full 3-tier approach.  It
>offers so much more flexibility than our beloved Access can right now.
>Since Access itself is moving toward being a front end, the next
>versions may see it gain a lot of those capabilities, but for now they
>aren't there.  The .Net framework hasn't yet found its way into Access.
>
>Charlotte Foust
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
>Sent: Friday, April 06, 2007 10:01 AM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] New database design for MS SQL
>
>Charlotte,
>
>  This is a lot like what I work with in VFP and a framework called VMP.
>The Framework lets you create fully 3-tier apps breaking out the
>Business Rules and Data Services classes into a Business Object.  The BO
>can be compiled separately into a COM object, so I can write a
>presentation tier in just about anything (Access, VFP, VB, etc).  
>
>  The Data Services like you describe below hides the BE data store and
>provides cascaded updates/deletes, constraints, etc.
>
>  I'm about to undertake a major development project and am going back
>and forth between doing this in VFP/VMP as a full 3-tier app or sticking
>with the tried and true Access approach (it would be my first full
>3-tier app with VFP/VMP).
>
>  I've got to talk to the client a few more times to get a sense of what
>direction they'll be heading in the future, but I'm leaning towards VFP
>at the moment.
>
>Jim 
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
>Foust
>Sent: Friday, April 06, 2007 12:05 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] New database design for MS SQL
>
>Our executable installs on the client at this point in its existence,
>and our clients are *frequently* thick!  We haven't split the business
>logic off to the server because there isn't always a server.  When I
>mentioned Access, I was talking about the data store, not the front end.
>Our XML files have nothing to do with Access.  FE is VS.Net but the app
>is designed to work locally (say, on a rig) or with the data store in
>SQL Server on a server (usually an office installation).  The
>application has to be flexible enough to run either way and to be
>capable of switching between the two, if need be (they tend to move
>their licenses around!).  Rather than creating different versions of the
>app for the varying environments, we simply built one that would work
>across the board.  That may change someday, but we're still in the
>process of migrating our apps and our clients into the new century. LOL
>
>A dataset is a disconnected datastore, so it doesn't care where the BE
>is or what engine it runs in.  A _recordset_ is like a query, a flat
>list of rows, but a dataset represents the separate sets of rows from
>one or more tables.  A TYPED Dataset (as opposed to untyped) is a kind
>of class and models the details of the data structure in code.  You can
>create them from scratch or using the built in tools to "generate" one.
>They then become objects in the project that our entity classes wrap,
>exposing only the programming methods and events we want to make
>available.  Drop an entity on a form, and you have automatically
>connected the form to the datastore the entity represents, so you can
>program to its columns, methods, and properties as if it were a
>recordsource in an Access form.
>
>Charlotte Foust
>
> 
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
>Sent: Friday, April 06, 2007 3:34 AM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] New database design for MS SQL
>
>Hi Charlotte:
>
>I will comment inline
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
>Foust
>Sent: Thursday, April 05, 2007 2:46 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] New database design for MS SQL
>
>No white papers.  We started rolling our own and called in a consulting
>firm to review the fledgling solution and point out deficiencies.  They
>did admit when they came back for a later review that they'd never seen
>a Win-based .Net app this large and complex, but we aren't ready (maybe
>I should say our clients aren't ready) to convert totally to web-based
>yet.
>
>Comment: Sounds very impressive.  
>
>We're using either SQL 2005 or Access 2000 format BE because some of our
>clients are on drilling rigs in the middle of the ocean and don't have
>SQL Server handy.  The embedded SQL for the provider adapters is stored
>in XML files and we read it in using a streamreader and pass it back to
>the calling data provider routine.  The only business logic on the
>server is in cascading updates and deletes, etc., nothing else except
>relationships at this point because we still have to handle both SQL
>Server and Access back ends.  The business logic is in its own assembly
>that's part of the overall solution.  Not sure what you mean by a
>desktop design.
>
>Comment: 'Desktop design' is my description of an application that has
>it core functionality supported at the client end; or win-based or thick
>client (this could also be describing the actual client?) ...As opposed
>to thin client application where most of my work seems to be migrating.
> 
>We create and supply software to the drilling industry--oil, gas,
>geothermal--so a well is a hole in the ground plus everything associated
>with that.  A wellbore, is the hole itself, which may be the original
>hole in the ground or a sidetrack drilled as an offshoot of another
>wellbore.
>
>Comment: A very good explanation of your business. I would be interested
>in how the data tier actually works or maybe just an overview ...without
>divulging any trade secrets of course... like how the data tier layer
>abstracts the actual data structures. Is your office using the XML
>option in .Net datasets, through classes to connect via an OLE (third
>party?) to the actual tables being either MDB or MS SQL? (Handling XML
>in MS SQL 2005 is built-in but I was unaware that Access had the same
>capabilities.) I am not interested in specifics just the concepts...
>Maybe a bird's-eye view?
> 
>Charlotte Foust
>
>MTIA
>
>Jim
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list