[AccessD] Linked to SQL Server in a domain

Dan Waters dwaters at usinternet.com
Fri May 22 09:14:04 CDT 2009


Hi John,

To get up and running quickly, you can simply create ODBC table links to all
the SQL Server tables.  Once having done that, you can use all the DAO code
you already have.  I did some data transfer speed analysis at two customers.
On a LAN using ODBC table links, data transfer between an Access FE and
Access BE is about 10% faster than between an Access FE and a SQL Server BE.
(The Access FE does maintain an open connection to the Access BE.)  So,
users won't notice.  

Later on you can rewrite your Access FE to connect to the BE using OLEDB
connections - my experiments showed a 5X to 10X data transfer speed
improvement.  But if users on a LAN are happy with performance as is, it
doesn't make much sense to do some significant recoding for a higher speed
they won't notice.  If you do change to using OLEDB connections, your Access
FE can be an .mdb or a .adp - there are pros and cons to both.

I have a customer who uses my system with ODBC table links (Access FE/Access
BE) on a WAN and they are OK with it.  The WAN has good bandwidth, and they
aren't heavy users.

SQL Server does have a robust security layer.  You can apply granular
permissions to SS objects by User or by Windows Permissions Group.  Again to
get started quickly, get all your users into a single Group, select Windows
Authentication, and apply that Group to all the SS objects.  Of course you
can get more detailed later if you want to.  

I think the HIPAA requirement may or may not be valid for this company - it
depends on the actual data content that is being recorded.  And, I doubt if
HIPAA actually specifies encryption (but it might lead you there).  In
theory, your customer will be responsible for knowing how the HIPAA
requirements actually apply to them, and they are supposed to let you know
what you need to do.  However, because you know much more about actually
securing data in a database, you and your customer will need to get that
figured out together.

HTH!
Dan


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, May 22, 2009 7:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linked to SQL Server in a domain

Jim,

The client is a very small company (6 or so employees) that helps seniors
with their medical 
insurance plans, researching billing issues and so forth.  They wanted a new
network administrator 
(which I am definitely not) so they went out and hired a new company to do
this.

This company advised them that in order to be HIPAA compliant they needed to
move the data into SQL 
Server (for encryption reasons AFAICT).  It seems this company quoted a very
low ball price to 
convert the system to SQL Server, and in the end it APPEARS that all they
did was run the upgrade 
wizard.  AFAICS out in the SQL Server there are no views or stored
procedures at all, only the data. 
  Even there they appear to have screwed some stuff up.  The database, which
I did not write but 
which I maintained and added new functionality to for many years, was
working fine (as stated by the 
company owner) before the upgrade but has "a bunch of problems" since the
upgrade.

The owner is in the process of hiring a new network admin company and has
asked me to come back and 
straighten out the problems in the db.  To be honest I was just brought back
in a few days ago so I 
really don't know the details yet on the "bunch of problems".

In any event, the DB FE uses simple ODBC links to get at the data.  They
have very few employees and 
performance seems to be pretty adequate doing things this way.  I understand
(form a theoretical 
perspective) the concept of using ADO recordsets for the bound forms and as
data sources for the 
combos etc. hitting stored procedures out on the SQL Server but none of that
is presently used.

I am trying to come in and get some additional functionality happening
quickly, for example a fairly 
strong login to the database and possibly additional presentation layer
security.  I really don't 
want to get bogged down in trying to move the database away from ODBC at
this point, though I would 
love to take this opportunity to do this as things settle down.

John W. Colby
www.ColbyConsulting.com


Jim Lawrence wrote:
> Hi John:
> 
> I have been working with MS Access to ADO-OLE to MS SQL/Oracle DBs since
> 1997 and Access does not work with ODBC. There is a simple and stupid way
to
> up date an old Access MDB to a MS SQL BE using connection/links. 
> 
> This system actually works fine for delete, add and update... but as soon
as
> you start grabbing recordsets of data for reports, subforms or start
rolling
> out a new application to a remote desktops the whole system grinds to a
> halt. At that point you have reached the end of the usefulness of the ODBC
> connections.
> 
> Unfortunately there is no short cut, the client and you just have to bite
> the bullet and go straight ADO-OLE... It is not that difficult but the
nice
> gui interface within Access can not do it.
> 
> Jim

-- 
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