Password across multiple access systems

[hollingd]

Good Afternoon,

I have been given an access database system which runs using about 7 access databases, linked tables etc, quite complex. When a new user installs the system from a DVD the user needs to provide a password. This password is filtered to all 7 access databases so they all use the same username and password.

The problem I am having is when the user runs this application again they are prompted for their password. Everytime they click on an option which is another access database they are again asked for their password. I am trying to figure out a way once the user has logged in once on start up no matter which option they select and no matter how many time they select it while the main appliction is running they are not asked for their password. Is this possible or totally outside the scope of Access databases.

 

[The_Doc_Man]

What you have described is standard behavior for Access using Workgroup security. It will ask you for user/pwd every time you open a new MDB file, either through a window or via internal (direct) open. It even does this when doing a Word MailMerge that calls an Access DB for the data to be merged.

If you don't want it to work this way, you might have a serious problem. This problem stems from a simple, all-too-common situation: Whoever designed the system didn't understand the way Office Workgroup security works and made an assumption. You know about "Assume" don't you? Makes an Ass out of U and ME.

Anyway, this is a complex problem. You see, at the point that Access is opening up a file, it notes that the MDB is secured and therefore asks for a password. If you don't want to ask for another pwd with every file open, you have to unsecure the individual MDB files. BUT... that leaves you with an unsecured database that folks could directly open without going through the top level of your application.

The only "real" way I see to fix this would be a redesign of the overall app to combine seven DB files into one. If everything is in the master DB file, there will be no problem in opening secondary files; there won't be any. No extra .MDB open, no new password requirement. Simple as that.

But from the sound of it, this is not the answer you will want to hear because you aren't the DB designer and have no control over the situation. Still, you could give the designers some feedback. Other than size issues, there is no particular problem in merging seven disparate databases into one.

There is also the Front-End/Back-End concept, which is one way to make a given data set fit into a table structure. Using this technique, you have the FE file for all queries, reports, forms, macros, and modules. Technically, you can store constant tables in the FE (tables used ONLY for lookup purposes, e.g.) and just not link the application to the BE for those tables. For anything that isn't constant, you put the DB in the back-end file and store your variable data there.

Note that some folks do not like to put constant tables in the FE. Nor am I saying they are wrong to dislike that practice. It is a matter of maintenance or update probability of the "constant" tables and depends on your exact situation as to whether that would even work correctly for you. It might not. If EVERYTHING fits in the BE file even allowing for planned growth, then certainly you can place all tables in the BE together.

 

[hollingd]

Thank you for your reply that has helped like you wouldn't believe. I am quite concerned thought with the size of the application. Many moons ago I remember read something about the integrity of MS Access and the amount of information i can handle. Not too mention the naming conventions that it prefers.

Whoever designed this database I have now got ownership of needed shooting I think as there is no consistency in the naming of tables, forms etc. And the amount of information in some of the tables reaches to about 110,000 records.

Access 2000 might have overcome these problems that I have heard of in the past, which would be a relief however, as much as I like Access I can't help but think this application should be in Oracle.