Connecting to secure database

[RobJen]

I have been looking on the web for a while but none really answers my questions, I hope someone here can help me a bit in the right direction.

I am developing a database application at work. Instead of making 1 database with all in it, I decided to make it modular. In that case I do not need to update 1 file constantly but I can take parts of it and update them (e.a. I have a database called dbEmployees, dbLogistics, dbTraining and so on). Also the tables are stored in a different file and linked to the user interface so I wont lose the data in the tables. I also made a .mdw workgroup file to secure them.

What I wanted to do is:

Login (using .mdw file)
-> Usermenu (connect to different databases) / global functions and so on
--> Use the menu to connect to the different databases

I choose this approach also so I would not put too much stress on 1 file in particular.
-------------------------------------------
The thing is, I would like users to login only one time using the mdw file which connects them to say UserMenu.mdb.

From UserMenu.mdb you get a popup menu which allows you to select the other protected database (all protected with the same mdw file).

My question would be how do I connect to those databases without having to provide a username and password again?

Any thoughts on this? I would appreciate it very much.

Thanks in advance.

 

[ghudson]

All you have to do is create the table links from the db they are allowed to open to the other tables in the other databases. Then the user will have access to all of the objects that you give them permission to. Keep in mind that the user should never has access to the database window. You will have to provide the forms that they can access and the forms will determine what data [which tables] they can access.

 

[RobJen]

Yes that I know, I didnt protect the database with the tables in mdw but only with a db password. The thing is I keep the forms and reports in different databases because the other option is to have 1 database with maybe 50 forms and dozens of reports which tends to get messy.

I use a link to open the first database by providing /wrkgrp in the command line so it opens the first database by using the provided workgroup. From that one I would like to have the code to connect to other databases protected by the same workgroup file.


Now I know I can use OpenDatabase to open a database in VBA but that seems to work only if I dont have that db protected by a workgroup. I could ofcourse make several links and use /wrkgrp in the command line and call those links from VBA but in that case I would have to provide a username/pass again and again.

I hope I make sense.

 

[RobJen]

Never mind, I think I found the answer in the ms knowledge base, thank you anyway

 

[RobJen]

   Dim MyConn As New ADODB.Connection

   MyConn.Provider = "Microsoft.Jet.OLEDB.4.0"
   MyConn.Properties("Data Source") = "C:\...\JetSecurity.MDB"
   MyConn.Properties("Jet OLEDB:System database") = "C:\...\System.MDW"
   MyConn.Open UserID:="Admin", Password:="MyPwd"

Well not entirely, I know how to connect to a workgroup protected database, but it seems to me that I still need to provide the user/pass manually in the code. I know there is a variable for the current user (CurrentUser) but passing the password might be difficult, from what I learned there isnt a variable that has the password from the current user.

On this website I found the thread: http://www.access-programmers.co.uk/forums/showthread.php?t=92442&highlight=password+variable

Reading that, I would conclude there is no way in earth to tackle the problem without having the user provide the password over and over again...?! Ugh.

 

[ghudson]

I still do not understand what you are trying to do.

Do you want the users to open only one secured database that is linked to many secured databases but they all share the same workgroup file?

Do you want the users to open one secured database and use that one database to "open" the other secured databases?

Your request is odd and more details are needed.