Adding password to split database with multiple users

Question

Hello,

I created an Access 2010 database and have split it. I know I will need to re-link the tables after adding a password to the front and back ends. This database will have multiple users.

My question is: Will adding a password disrupt them from being able to add records simultaneously from their respective front ends? I ask because when you go to add a password it mentions record level locking will not be enabled.

Thanks in advance for any info and help!

Answer 1

mikewf,

Yes you will be able to leave the one copy on the shared drive. The way the new encryption works it cause problems with rowlock so they had to make it so it disables. The legacy will allow you to keep rowlock on and leave the front end in one spot.

Answer 2

Okay so let's say I have the database made and I'm ready for them to start using it. (not split at this point and using legacy encryption). What should my next steps be? Do I just put the database as a whole in one spot on the shared drive? 

Please excuse my ignorance in this particular area of Access. The help is very appreciated.

Answer 3

You should never share a front-end.

Answer 4

Hey mikewf, 

That really depends on your front end usage method. Do you have the front-end on the server or did you provide copies for the people using it? 

If there is just one front-end and located on the server then yes this will effect you. The page lock will make it so others can open the database but NOT the same form. 

If you send the copies of the front-end out to the users then this will not effect them entering at the same time. 

Making the copies of the front end and sending them out is very easy. The problem is in this case you might be able to lose control of the data. At that point two people edit the same record at the same time which course will not be a good thing. Also, if you make updates you will have to send it out to everyone! I do not recommend this method unless you code some things to make it safer and easier to deploy. 

I have two suggestions for you I guess.

1 - First, i don't know if it is an option but you could use sql server for the backend database. The express version is free and holds 3 to 4 times the amount of data access could alone. 

2 - If you do not like the idea of SQL Server (Far more secure by the way) then you can lower the encryption. You would go to file, options, Client settings. At the bottom of client settings you will see "Use legacy encryption (good for reverse compatibility and multi-user databases". This will lower your encryption but you will be able to keep the rowlock in place. The legacy encryption does not have that limit. 

If you are truly worried about security though the best thing you can do is to use SQL Server over Access's back-end. Fact is even with encryption I would be able to hack it wide open in minutes if I got my hands on it. 

I hope that hoped you. Let me know if you have questions and good luck! 

Dave

Hey Dave,

I forgot to ask one question that just hit me. With lowering the encryption to legacy (unfortunately SQL wasn't an option), do front ends still need to be sent out, or is it OK as one front end on a shared drive with legacy encryption (to keep the rowlock)?

Answer 5

With regards to creating a login form, take a look at: 

http://www.devhut.net/2012/12/21/ms-access-login-form/

Then, with regards to security, password protect your back-end and then review the following article to secure your front end.

http://www.devhut.net/2016/09/01/securing-your-ms-access-database-front-end/

The above security is good for most scenarios, but if your company is truly worried then SQL Server is your only choice.