[AccessD] New database design for MS SQL

Jim Dettman jimdettman at verizon.net
Fri Apr 6 14:51:21 CDT 2007


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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list