February 16, 2015 at 1:55 pm
I have a front end in MS Access and backend as SQL Server 2008.
I want each user that in the front end have the ability to change data in sql server via the front end.
I have connected the tables in access has a odbc connection.
My question is does each user has to setup a odbc connection to sql server?
Do i have to setup each user in SQL Server as well?
February 16, 2015 at 2:42 pm
You should be able to relink the tables to SQL Server.
If you use integrated security in SQL Server (so Active Directory handles authentication), you would just have to create groups in AD, and assign permissions there to the objects the users will need to use.
You might want to look up some of Brian Kelly's articles on SQL Server security so you get the basics down and understand your options.
February 16, 2015 at 2:44 pm
The quick answer is yes, connection and users have to be set up, you might want to look into linking to AD if there are many users
:cool:y,
February 16, 2015 at 3:00 pm
Thank you both for your replies.
I was afraid of the solution might be to set up each user in SQL Server as there is quite a few.
The only other solution I see is to create a generic user name and password in SQL Server and ask each of the user's IT Team to setup new ODBC Connection with each user...although that might not be the best practice.
Did think of linking the front end to an access backend table and SQL Server will pick up the back end table each day to refresh tables...but that moots the point of having SQL Server.
If anyone else have any other solution...please do share them. Thanks! 🙂
February 16, 2015 at 3:21 pm
Come to think of it, you might want to look up Doug Steele's DSNless connection code for Access. (Not entirely sure it works for SQL Server, but I don't see why not). Here's the webpage with his code.
As for the users/groups/permissions thing, you would need the SQL Server Admin to set up the groups/permissions for you in SQL Server, so that your users can't change things they're not supposed to etc. (That's where Brian Kelly's articles come in handy - you get an idea of what you're getting into.)
HTH,
Pieter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply