Convert access 2010 database to SQL Server

I have a database that I want to convert to SQL server.  I am using Access 2010 and used the Upsizing Wizard but I don't know if I used the right settings.  Can anyone tell me the correct way to do this?  I don't know if I should leave it connected to the access database in case I need to make changes??  I want to be able to setup users and limit their access to certain parts.  I know how to create logins but how do I incorporate them into the database so that when the application is opened it will prompt them for username and password??

Any help will be appreciated.

Thanks
LVL 1
lrollinsIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
Yes, you would leave it connected to SQL server unless you have a replacement front end user interface. If you use the upsizing wizard and accept all the default it will create linked tables to replace your existing Access tables (which it will rename, adding a '_local' suffix).

If you have a table of user credentials it will be become one of the SQL linked tables and you can still query it when authenticating your users.

You may wish to keep some of your local tables for performance reasons (e.g. to load lookup tables into the front end at startup).

Once you have created the SQL backend, you can setup logins and permissions there in a more sophisticated way than you could in access, including column-specific permissions. You can also create roles and then assign users and permissions to the role.
0
lrollinsIT ManagerAuthor Commented:
Thanks.  I figured that's what I should do but I guess my next thing is how do I make the front end application prompt the user for their username and password.
0
SimonCommented:
If you're on a domain, you should use integrated security to control access to the backend data. This is the best way if possible in your environment, so that the users domain account and security group membership controls their level of access. You then get the benefits of AD authentication with GPOs for password policies etc.

If you want a second level of access control (to open and view the front end interface) you just need a popup username/password form in your front end that compares the values entered against a table containing that data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
To have people log into the app, create a table and two forms.  One for maintenance and one for login.

If you want to use one set of credentials to log into SQL Server, you can save the userID and password in the table link.  This is not secure since the links are in plain text and if you open the MSysObjects table, you will be able to view the login credentials.  If you want each person to log in separately (usually preferred by the DBA), then after the user logs in using your form, you will need to relink the tables using his userID and password.

We need more information regarding how you intend to use the login before going into more detail.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.