[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Convert access 2010 database to SQL Server

Posted on 2014-12-16
4
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 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