[AccessD] SQL Server security

Windows Authentication should work, John.  That's what we did for our
clients at my last employer's.  You can certainly create specific
users and groups and roles on the server.  We handled most of the
specifics in code (.Net, natch) but we had only two groups for users,
readonly and readwrite, the latter of which included deletes, but our
code limited that capability.

Charlotte Foust

On Wed, Jan 5, 2011 at 10:00 AM, jwcolby <jwcolby at colbyconsulting.com> wrote:
> I am having performance issues in a largish Access application, a Disability
> Insurance Claim call center app.
>
> I have one particular table which is not huge in terms of field count but it
> does have a lot of records and most of the fields are indexed, and it has
> about 800K records in it.  This table holds "contact" info, as in phone
> calls that the users have.  They document every "contact" with every one,
> claimants, doctors, lawyers, etc. into a memo field and also date of call,
> ClaimID FK, employee id FK, contact type id FK etc.  Kind of a mini center
> of the universe for this application.
>
> The result is that people are storing new records in this table constantly
> throughout the day and we are getting a lot of "record locked..." issues
> caused by (AFAICT) the time it takes Jet to store the records and update all
> of the indexes, and probably the memo storage area of the mdb.
>
> Just to give a picture, this one table has been moved out to it's own mdb
> and that mdb is about 700 megabytes after a compact.  Most of the rest of
> the database (150 tables) is in another mdb and after compact that database
> is 800 megabytes, so this one table is close to as big as the rest of the
> db.
>
> I do not have experience in a transactional database using SQL Server, but I
> am thinking that SQL Server express 2005 will not have an issue keeping up
> with this kind of usage - 25 users adding records to this table all day
> without causing locking issues like I am seeing now.
>
> My issue at this point is that they use a network logon and force the users
> to change their password every 30 days.  Is SQL Server going to use that
> same network username / password database or does it use a list of usernames
> / passwords physically on the server itself?  IOW will Windows
> authentication work or will I need to go to SQL Server username / password?
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>