In our company, passwords expire every 90 days (NO EXCEPTIONS) for DB2. This means that all my linked tables can't be accessed. There are many other users using the DB's and when I have to reset my password, I get locked out a lot because I can't seem to re-link the tables fast enough. It also seems that "refreshing" the tables from the Linked Table Manager doesn't work very well.
There is code available to programmatically link your front end and back end. I can't remember where it is but it will be one with one of the major MS Access free stuff providers.
However from reading your question I am not sure if that will be much help to you, as you obviously have a problem with windows security. As a general rule, I have noticed that most access programmers (me included) avoid the windows password system like the plague! However you may find someone with the relevant information on this forum.
The alternative is to browse some of the other forums, like the VB6 ones as there may be something there to help you with your password problems.
It has nothing to do with Windows and the passwords to log on to a network. This has to do with DB2 and the passwords for those...they expire every 90 days. It's very much a pain to re-link the tables in all the Access DB's that I have. And if someone accesses (3 times in a row) one of those linked tables that I haven't relinked, I get locked out of DB2. This happened 4 times yesterday .
So, you can see why I want a "quick and smooth" way of doing this.
Even if the user could relink the front end to the backend, how will they know what the new password is? Do you want the average user having to enter the password to relink the tables? I am not sure if you can automate what you want to do.
I know nothing about DB2 but I suggest that you open your db with a main menu or splash form. In the OnOpen event you need to check if the user has access to the location of the backend [or DB2 tables]. If the user does not then you need to prevent them from going any further with your db so that they can not open a form that is linked to one of the unaccessable DB2 tables.
Something like this will get you started...
Code:
If Dir("\\Server\Partition\Directory1", vbDirectory) = "" Then
MsgBox "Your PC is not connected to the network, the #$%& DB2 passwords have changed again or you are not authorized to access the \\Server\Partition\Directory1\ directory!" & vbCrLf & vbLf & "Please reboot your computer and ensure that you have logged into the network before opening the xxx program." & vbCrLf & vbLf & "This program will close when you click the OK button.", vbCritical, "Database Connection Error"
DoCmd.Quit acQuitSaveNone
Else
'MsgBox "OK - Network access to \\Server\Partition\Directory1\ is verified"
End If
Continue searching for the relink code for it is out there. I have seen it but never needed to use it so I never copied it.
The users of the Access DB don't have access to DB2...only I do. So, they have to use my username and password. I don't want to prompt them for a password because I can't let them know what my password is.
The code might work if there weren't multiple users. Each time it checks to authenticate the password is one point against me when it doesn't authenticate. 3rd time is a charm and I get locked out. So, unfortunately, I don't really see that code working for me.
I'm not sure I know what you mean by "network structure." The databases is just DB2. If you're talking about the Access DB's, they're all on a network drive. Inside, there are tables that are linked to DB2 tables. As far as communication due to password change...LOL....well, it doesn't really matter what I tell that group. They're going to try to use the database and lock me out anyway. They're just office workers and don't really understand "databases" and such. I tell them I need to do something in there and I'll let them know when I'm done...they complain and are back in the DB in 5 minutes.
I used to get caught by this also but usually because I never used the mainframe and so simply forgot to go in and change my password. I would suggest removing all the current links and then relinking them without storing the user name and password. Then add a table to your database that contains your username, password, and the last changed date. In your startup form, open a connection to DB2 using your userid and password that you get from the local table. Your code can warn the user to contact you for an update if the date in the table is more than 85 days old.
Here's an example of how to open an Oracle connection:
Code:
Public Function ConnectToOracleBatch()
Dim WgtDB As DAO.Database
Dim strConnect As String
Set WgtDB = CurrentDb()
strConnect = "ODBC;UID=WGT;PWD=weight;DSN=weight;Database=WGT"
Set WgtDB = OpenDatabase("", False, False, strConnect)
strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database =farms"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close
End Function
Just change the connection string to connect to DB2. If you don't know what the string should look like, open the MSysObjects table (you will probably need to unhide system objects first) and look at the connection strings in the DB2 tables. Copy one of the strings and use it.
Just an additional thought.
What about setting your connection to exclusive in the process?
I don't know how this will work with everyone using the same login...
Or simply have two accounts, give them one, and the other you can use exclusively for this purpose.
I used to get caught by this also but usually because I never used the mainframe and so simply forgot to go in and change my password. I would suggest removing all the current links and then relinking them without storing the user name and password. Then add a table to your database that contains your username, password, and the last changed date. In your startup form, open a connection to DB2 using your userid and password that you get from the local table. Your code can warn the user to contact you for an update if the date in the table is more than 85 days old.
Here's an example of how to open an Oracle connection:
Code:
Public Function ConnectToOracleBatch()
Dim WgtDB As DAO.Database
Dim strConnect As String
Set WgtDB = CurrentDb()
strConnect = "ODBC;UID=WGT;PWD=weight;DSN=weight;Database=WGT"
Set WgtDB = OpenDatabase("", False, False, strConnect)
strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database =farms"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close
End Function
Just change the connection string to connect to DB2. If you don't know what the string should look like, open the MSysObjects table (you will probably need to unhide system objects first) and look at the connection strings in the DB2 tables. Copy one of the strings and use it.
Good idea...but I don't want anyone going "behind the scenes" and looking at the table and finding out what my password is.
WindSailor said:
Just an additional thought.
What about setting your connection to exclusive in the process?
I don't know how this will work with everyone using the same login...
Or simply have two accounts, give them one, and the other you can use exclusively for this purpose.
That might work if this was a smaller company...but we have lots and lots or red and yellow tape. There are rules, guidelines...blah blah blah. I wish I could get a dept userid and password that never expired just for this purpose...but that violates security and an audit would surely get us for that.
They can see your userID and password now. They only have to open the MSysObjects table. Everything is in plain text. At least with my suggestion you can make it more difficult. You can define the table as a hidden user table and you can use the password mask on the userID and password fields so they are hidden by asterisks. These are not foolproof options, but they will slow down someone unfamiliar with the technique. First they would have to figure out that there was a hidden table, unhide it, open it in design view, and turn off the password mask. That's a lot for a casual user to get past.
Why do you let them go "behind the scenes" anyway. Even if you don't use full Access security, you should at least have startup options that make it difficult to get to the database container. In any event, you can hard-code your userID and password but that isn't secure either if you let them into the database!
but that violates security and an audit would surely get us for that
What to you think your present method does? I'm sure that there are rules against people using other people's userIDs. You are violating them!
Your DBA is simply being lazy if he doesn't want everyone to have their own access. A good solution that prevents the users from going outside the box and doing it themselves is to have the DBA assign individual userIDs but to give them all the same password such as "j82uBBx89w". Have the user log in to your database with their userID and you use the code I gave you to match the entered userID with the hidden permanent password. That gives the DBA the ability to always know who is accessing data and because the user doesn't know his password, he can't just create his own database to link to DB2 and circumvent your application.
Fair enough, but I don't claim to know everything about Access. Just trying to better my knowledge.
Your DBA is simply being lazy if he doesn't want everyone to have their own access. A good solution that prevents the users from going outside the box and doing it themselves is to have the DBA assign individual userIDs but to give them all the same password such as "j82uBBx89w". Have the user log in to your database with their userID and you use the code I gave you to match the entered userID with the hidden permanent password. That gives the DBA the ability to always know who is accessing data and because the user doesn't know his password, he can't just create his own database to link to DB2 and circumvent your application.
It's not that they are being lazy...the people in question are not allowed any type of access to the system. It's that simple. Access is technically not even a supported product for business. But due to all the red tape and how long IS takes to get things done, we've had to come up with work-arounds...and unfortunately, those work arounds have been in place for some time now.
I'll try the method of hiding the userid and password in a table. I hope I can get it to work...
Okay, I'm not understanding how I pull the userid and password from the table in the connection string...below is what I have.
Code:
Public Function ConnectToDB2()
Dim DB2Connect As DAO.Database
Dim strConnect As String
Set DB2Connect = CurrentDb()
strConnect = "DSN=DB2Q;UID=[COLOR=Red]blah[/COLOR];PWD=[COLOR=Red]blah1[/COLOR];MODE=SHARE;DBALIAS=DB2Q;DISABLEKEYSETCURSOR=1;DISABLEUNICODE=1;IGNOREWARNINGS=1;"
Set DB2Connect = OpenDatabase("", False, False, strConnect)
strConnect = "DSN=DB2Q;UID=jhrm008;PWD=thedude5;MODE=SHARE;DBALIAS=DB2Q;DISABLEKEYSETCURSOR=1;DISABLEUNICODE=1;IGNOREWARNINGS=1;"
Set DB2Connect = OpenDatabase("", False, False, strConnect)
DB2Connect.Close
End Function
Use Access help to get syntax help for functions such as DLookup. When searching for help on VBA language elements, you MUST open the VBA window and search for help from there unless you are using A97 which is the last Access version where help was whole.
The basic syntax is:
DLookup("the field you want returned", "the query or table you want to search", "selection criteria to chose the correct record")
.
