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

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

Answer 2

Not a problem at all. You are taking the time to learn it so never say sorry for asking.

I have food for thought to give to get straight to the point, here is how you will need to go about this on a basic level:

1. PLEASE SAVE A COPY!!! Put one on your desktop or wherever for very safe keeping.
2. Next with the other copy go to Tools tab, click Access Database in the Move Data group. This will start the wizard that you will want to use until you get some more understanding of it. 
3. Go ahead and split the database. It as you to specify a name, file type, and a location for your back end to go. 

Then you are done...technically. Being a shared Application at that point people love to play this things that they should not. Access is a big one for that. You really need to lock it down the best you can. Did you use, or do you use VBA coding? If so I will share some tips on locking it down. 

Keep us posted and remember...PLEASE MAKE A COPY BEFORE YOU PROCEED!

Answer 3

Greatly appreciated! SQL wasn't an option because I have to work with a company issued laptop and it won't allow me to download the express version without administrator login and password. I work for a pretty good sized company, so getting that approved is a nightmare in itself, so I let that rest right there. So unfortunately, stuck with just Access. 

I'm going to fine tune a few things on the forms then I'll try to implement these and do a test run.

Answer 4

Well, to satisfy the department managers requirements there has to be at least some level of security on it since it will house somewhat sensitive data (QC stuff). Only want a few certain people to be able to view/add/get reports. I attempted to create a login form with user id and password, but for the life of me could not get the code to work correctly.

Answer 5

I think you are referring to the Database Password. No need for that, unless you really, really know what you're doing. What are you trying to accomplish anyway? Maybe some of the articles at www.accesssecurityblog.com would help.