One database versus multiple databases

[jhanson2]

Good afternoon,
I have started a new job for which I've been asked to develop Access databases for 3 different research studies. Can anyone advise me as to the pros and cons between creating 1 back-end database shared by all 3 studies, versus creating 3 separate back-end databases, one for each study?

None of the data between the studies will be shared, none of the research assistants (other than myself) need to access each other's data. The front end databases across the studies, though, would look very similar, with a couple of exceptions (e.g., one study only collects data from parents at baseline, another collects data from parents and children at baseline).

I just thought it could be easier for me to create just 1 database so I can be more efficient (e.g., in the background I can look across the studies to see if the research assistants are entering data correctly, I can avoid taking time to create all the drop-down boxes etc). As I'm aware, I can create several different front ends and tailor what the forms for each of these looks like to suit the subtle differences between the studies. Although, one issue I can foresee is the possibility of the front end databases running slowly since several people across the studies will be inputting data at the same time.

Any advice is welcome!

[June7]

I would prefer to maintain 1 frontend linked to 1 backend and manage forms and reports for the subtle variations.

[jhanson2]

Thanks for the speedy reply June7! One possible issue with 1 front-end is that the research assistants for each study only have ethics approval to access their specific study's data. By having everyone working from 1 frontend, wouldn't they have access to each other's data?

Sorry for any daft questions -- my Access knowledge is fairly basic!

[June7]

Yes, that is the case. Database design with a user login could limit this but any competent Access user knows how to get around these limitations. Especially if everyone knows where the backend is located and has permission to the folder.

Maybe you should consider SQL Server Express. More security features.

[jhanson2]

Thanks again, June7! Let's say I do go ahead with Access -- would you be able to advise me on how to set up user logins?

[June7]

It is extremely common topic in forum. Suggest you do a search. Attempt something that fits your situation. Post thread with specific question if you encounter issue.