Solved

Convert access 2010 database to SQL Server

Posted on 2014-12-16
4
312 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 36

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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