addid workgroups to a network access database
-
hello all,
I have a question, I already added workgroups to my access database however now I would like to everyone to see this security. how do I add that system file to the network?. what i did now was make a copy of my access database and put it my desktop so then when my workgroup was created it was created in my desktop. When I get the share drive with the network database it should create this for all my users to see this security? or do I have to go indivually to everyone's computer to add something?
-
Each user will need to connect to the workgroup file.
Caveat #1: If you simply have each user join the workgroup file, then that security will apply to every Access database they open. You can get around this by creating a shortcut for them, that includes a reference to the workgroup file in the command line. Then, it will only use the workgroup when the database is opened from that shortcut.
I am presuming, since you are posting here, that this is an Access front-end to a SQL Server back-end. As such, you will actually want the Access portion to sit on each user's computer anyway. Trust me, you do NOT want your users running the Access database from a network share.
My recommendation is to create a .zip file (or installation file, if you're feeling fancy) that includes the database, the workgroup file, and a shortcut that explicitly opens the database with the workgroup file. Have each user put those files on their machine.
The upside to this approach is performance. The downside is that it's trickier to keep everyone's security file up to date. I recommend adding a bit of code to the main form of your database that will check to see if the security file is the most recent version.
-
thank you for your detailed answer, yes my database is an access frontend and and sql server backend, now with what you said that you don't want users to be using the database from the network share, how exactly do you do this? by just making copies of the databases in their local drives and then creating a script for the end of the day to make a copy of all the changes to the main network database? is it a copy of the database or a replica?
thanks so much for helping me.
-
Just put a copy of the .mdb file on their PC. The links to the SQL Server should still work. So, all the data should be flowing exactly the same. Do you actually store any volatile data in the .mdb file? If so, you may want to also create a separate .mdb back end to store on the network.
One of the basics of creating stable Access databases on a network is division of labor. Anything that is volatile data (i.e., data that is updated frequently, and by multiple users) should go in a back end on a network share. Anything that is not volatile data (e.g., configuration data, forms, reports, modules) should go in a front end on the user's PC. If you try to run an Access database from a network share, you have to send the entirety of each form and report across the network. This not only degrades performance, but makes database corruption very likely.
-
ok so I think I understand now what you mean, now when a user does change volatile data in their local database since the tables are linked to the sql server then it should update the server tables? right?.
This is the scenario I understand you mean..... make a copy of the mdb access database and then put a different copy to each of my users. Now after I add the workgroups in each users pc, then some users will be able to add/change volatile data to some tables or through forms, how does this reflect in the network share backend database? when they make changes or update anything would'nt it get updated in the sql server backend automatically?
Jessica.
-
Correct. Any data changes that take place in the front end affect the back end automatically through the linked tables.
As a note, if the only thing you're trying to control is the security of the data, SQL Server security is better, easier, and more customizable than Access security. You can set the SQL Server to use security based on the user's Windows Authentication, meaning that they don't have to log in to the database. Which also means that you won't have to distribute and update workgroup files. Access security can only dictate at the table level, but SQL Server security can actually work at the column level (e.g., you could let people see the name and phone number from the Employees table, but not the social security number and salary).
Just something else to think about.
-
yeah that is what I wanted to do, I added users for the sql server database where my tables are located and I added permissions, but I need to add security to the forms, queries, and some of the reports that my users use ( all of these are still in access) eventually I will migrate the queries into views but not right now. so they told me I needed to do workgroups for that, is that correct?
-
Essentially correct. For what you want to do, workgroups will be the best way to go.
-
thank you so much , you cleared up a lot of doubts i had...
-
No problem!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply