MS Access ADP Project - Create LogIn Screen?

pskeens used Ask the Experts™
I am creating my first Access ADP with MS SQL server.  All the apps before that I have created used Linked tables and wanted to give this a shot for performance boost.  

I want to create a login screen when the application opens.  No problem in creating this BUT I need to know how or where to send the user name and password to.  

Previously when I did this, I created a users table (tbl_Users) and when each user logged in it appended a record to this table with the user credentials.  I used my own table for user privelages and management instead of MySQL user management.  I felt I had greater control of my users this way.  

THe question is should I create my own users table as previous applications OR use the built in MSSQL managment?  If the latter is best option how should I set up the LogIn screen?

Thanks in advance!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

probably others might disagree, but in all my years of developing in access i ALWAYS ended up using a single login (aka user) from my application to sql server, and managed every other priviledges internally in the app. of course this is not considered the SAFEST way, but then again, unless your app deals with sensitive data/operations and/or is used by substantially different kind of users (like managers and empolyees along with internet casual users...)

in the end it's much easier to manage

and while at it, dont expect a performance boost by simply changing to adp - it's not faster in any ineherent way. and again, in my experience, i found it limiting more than rewarding


I was told my many that ADP was better for network traffic because Access does not have to pull the entire data table then filter client side.  ADP was supposed to address the issue of performing query on server side.  Is this correct?

ADP is definately harder to program for!  I would love to go back to linked tables it is so much easier.  
again, from my personal experience (though quite extensive), i found advantages and ease of development in mdb/mde's far exceed adp's "direct" connection.
as for running queries/udf/sprocs on the server, this of course has very big advantages and can be accoplished by pass-through queries in access (i use querydef objects in vba to change the sql dynamically
This question seems based on a misunderstanding of the essential features of ADP/SQL Server and how it is desiged to be used.  The ADP login (to one SQL Server server) is implicitly established transparently by the user's single, domain login at system startup.  There can never be, and should never be a "login screen".  The logged in user either has permissions on a MSSQL entity, or does not.

This means that to do development you need test accounts for every class of user.  It also implies a lot of logging in and logging back out during development (or multiple computers or instances of computers).

Aside from the inconvenience of not having local tables, IF there is a real need and opportunity to have SQL Server permissions be administrated in a framework of Domain authentication/security, I would say ADP is the "only way to fly."

Unfortunately, MS hasn't done a great job at selling/explaining the totally different scenarios that MDB/MDE/ADP architectures (and their more recent access version equivalents) each imply.

With the removal of "access security" from the .accdb file format and beyond, we are being pushed in this direction whether we like it or not.  I want to believe this is a good move for MS.  But as a developer who also works for small businesses, I feel like I have no good options when I need some kind of user security, but I can't work with domain administration for one reason or another.

I would say if the user's only concern is performance (not security design/mgmt. or single-login), then ADP doesn't necessarily improve performance in any way that couldn't also be achieved in traditional mdb/accdb development.  On the other hand, keeping everything on the server will generally lead to more efficient designs for lots of reasons.  Having said that, there is a lot of craft to making SQL Server more efficient and a bad design can hurt too.

Finally, I also can't help but mention that using the Access client to author entities in SQL Server (a basic feature in ADP) often has unintended consequences in the SQL code.  I have spent many, many hours re-writing T-SQL that was created by the Access tools.  The way they do joins and don't make elegant use of aliases is criminal.  It seems like common sense that we should only author views in the SQL Server tools and totally ignore the Access authoring tools.

The question also mentions "MySQL" as well as MSSQL; I am assuming that was a type-o and you know that ADP can only work with SQL Server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial