asked on

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

Simon

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.

lrollins

ASKER

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.

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial