Entering a Username & Password within a Function

[BJByrne]

Hi there,

I want to open a database from a macro in another database and I have it working, except that we have usernames and passwords to log on, so when the macro is run is automatically asks for a username and password.

I've tried entering the conditions as follows:

'Open the External Database in Access Window
appAccess.OpenCurrentDatabase strDBPath, False, bstrPassword

providing it with the database path (as String), that I don't need to enter the database exclusively, and finally providing it with my password (as String). However, it doesn't pick up the password. However, once I key it in manually, it will continue running correctly till completion.

I would appreciate if somebody could let me know is it possible to automate this, so that I could open the database etc. without having to manually enter the password?

Thanks in advance.

[ADezii]

Hi there,

I want to open a database from a macro in another database and I have it working, except that we have usernames and passwords to log on, so when the macro is run is automatically asks for a username and password.

I've tried entering the conditions as follows:

'Open the External Database in Access Window
appAccess.OpenCurrentDatabase strDBPath, False, bstrPassword

providing it with the database path (as String), that I don't need to enter the database exclusively, and finally providing it with my password (as String). However, it doesn't pick up the password. However, once I key it in manually, it will continue running correctly till completion.

I would appreciate if somebody could let me know is it possible to automate this, so that I could open the database etc. without having to manually enter the password?

Thanks in advance.

The Password that you are referring to is the Database Password, not the Password associated with the UserName/Password combination seen in Access security Models. That is why entering it as an Argument in the OpenCurrentDatabase() Method had no effect.

[BJByrne]

Thanks for this. However, I still would appreciate if somebody could let me know if it's possible to automatically override the username and password of the database I am opening, or to code the username and password, so the function can be run automatically?

Thanks.

[ADezii]

Thanks for this. However, I still would appreciate if somebody could let me know if it's possible to automatically override the username and password of the database I am opening, or to code the username and password, so the function can be run automatically?

Thanks.

There are several Methods of automating the process of entering a Database's Password but the User Name and Password is another problem. You could try SendKeys by placing certain Keystrokes in the buffer, then inserting them at the proper time, but this would be a little hairy to say the least. I'll look into it further for you.

[Lysander]

There are several Methods of automating the process of entering a Database's Password but the User Name and Password is another problem. You could try SendKeys by placing certain Keystrokes in the buffer, then inserting them at the proper time, but this would be a little hairy to say the least. I'll look into it further for you.

If both databases are using the same MDW and you are moving from one to the other with the same username, you should not need to specify passwords as you are already logged on to the MDW


If you are using different MDW's or different usernames and passwords you could try looking at the help for

DBEngine.OpenDatabase

because I see that lets you specify connect info such as MDW, username, password.
I use DBEngine.CreateWorkspace a lot to change to a hidden secure user for certain functions, and I think the OpenDatabase method is the way to go.

I can't be more help as I have not needed to do this myself.

[ADezii]

If both databases are using the same MDW and you are moving from one to the other with the same username, you should not need to specify passwords as you are already logged on to the MDW


If you are using different MDW's or different usernames and passwords you could try looking at the help for

DBEngine.OpenDatabase

because I see that lets you specify connect info such as MDW, username, password.
I use DBEngine.CreateWorkspace a lot to change to a hidden secure user for certain functions, and I think the OpenDatabase method is the way to go.

I can't be more help as I have not needed to do this myself.

I think I found one solution to your dilemma, and it involves specifying the User ID and Passwords as properties of the ADODB Connection Object. The following code will open the Test.mdb Database in the C:\Test Directory, then loop through all Records in tblEmployees printing the First and Last Names. I've tested it on an Unsecured Database and it works fine. Make the necessary substitutions, and attempt to open your External, Secured, Database and see what happens. Please get back to me on this one, I'm curious myself as to the outcome. Good Luck!

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset, strPathToExternalDB As String
 
strPathToExternalDB = "C:\Test\Test.mdb"
 
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
 
cnn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cnn.ConnectionString = "Data Source = " & strPathToExternalDB
cnn.Properties("User ID").Value = "Admin"       'unsecured database
cnn.Properties("Password").Value = ""           'unsecured database
  cnn.Open
 
With rst
  .Source = "tblEmployees"
  .ActiveConnection = cnn
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
    .Open
End With
 
rst.MoveFirst
Do While Not rst.EOF
  'do whatever processing you need to do here
  Debug.Print rst![FirstName] & " " & rst![LastName]
    rst.MoveNext
Loop
 
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
 

[BJByrne]

Hi there,

Well after much playing around, I'm afraid I cannot get passed the error message at cnn.open:

Run-time Error ....
Cannot Start your application. The workgroup information file is missing or opened exclusively by another user.

But thanks a million for all your help with this matter, it's much appreciated and if you do have any other suggestions let me know.

Thanks.

[ADezii]

Hi there,

Well after much playing around, I'm afraid I cannot get passed the error message at cnn.open:

Run-time Error ....
Cannot Start your application. The workgroup information file is missing or opened exclusively by another user.

But thanks a million for all your help with this matter, it's much appreciated and if you do have any other suggestions let me know.

Thanks.

Cannot understand why you getting this Error Message if the Database is not opened Exclusively by another User. When I get the chance, I'll try the code on a secured Database.

[ADezii]

Hi there,

Well after much playing around, I'm afraid I cannot get passed the error message at cnn.open:

Run-time Error ....
Cannot Start your application. The workgroup information file is missing or opened exclusively by another user.

But thanks a million for all your help with this matter, it's much appreciated and if you do have any other suggestions let me know.

Thanks.

I am happy to report that I finally solved the problem. You must explicitly specify the location of the System Database in the "Jet OLEDB:System database" Property of the Connection Object (just add Line #14). The following code will Open the Northwind.mdb Database in the C:\Dell\ Directory using the C:\Dell\ADH.MDW System Database. The User ID and Password are programmatically supplied. Good luck - any other questions feel free to ask. I going to relax now, since this one really taxed my brain:

Private Sub Command7_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset, strPathToExternalDB As String
 
strPathToExternalDB = "C:\Dell\Northwind.mdb"
 
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
 
cnn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cnn.ConnectionString = "Data Source = " & strPathToExternalDB
cnn.Properties("User ID").Value = "Paul"       'secured database
cnn.Properties("Password").Value = "Paul"      'secured database
cnn.Properties("Jet OLEDB:System database") = "C:\Dell\ADH.MDW"
  cnn.Open
 
With rst
  .Source = "Employees"
  .ActiveConnection = cnn
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
    .Open
End With
 
rst.MoveFirst
Do While Not rst.EOF
  'do whatever processing you need to do here
  Debug.Print rst![FirstName] & " " & rst![LastName]
    rst.MoveNext
Loop
 
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub