Solved

Convert access 2010 database to SQL Server

Posted on 2014-12-16
4
292 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:SimonAdept
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:
SimonAdept 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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now