[AccessD] Linked to SQL Server in a domain

The problem is that the owner has her sister working there as her access person.  Her sister has 
taken Access classes and even a SQL Server class (quite expensive apparently) and is quite 
comfortable doing much of the day to day stuff in Access.  Of course this implies linked tables so 
that her queries and stuff works.

My perspective on this is that Microsoft is pushing SQL Server Express as a BE for Access.  As such 
one should not be required to be a SQL Server guru in order to use SQL Server as the data store for 
Access or the whole point of Access / SQL server goes down the drain.

John W. Colby
www.ColbyConsulting.com


Eric Barro wrote:
> John,
> 
> I recommend that you look into moving away from ODBC at this point. I've had
> a similar experience in the past converting from Access to a SQL datasource
> and I was using ODBC through DSN connections. There would be timeout issues
> and locking issues and it was not working so I changed the datasource to use
> ADO recordsets.
> 
> Eric 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, May 22, 2009 5: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
>