[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