Solved

Convert access 2010 database to SQL Server

Posted on 2014-12-16
4
311 Views
Last Modified: 2014-12-16
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
0
Comment
Question by:lrollins
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40503107
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
 
LVL 1

Author Comment

by:lrollins
ID: 40503145
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
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40503203
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40503287
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question