I wrote the following VBA code in Excel 2000 to create a connection to an Access 2000 with workgroup security in WindowsXP. Everything was working properly until we moved to Windows7 The database is still in Access2000.
It seems that the password value is eliminated from the ADODB connection string after opening a connection to the database. The first debug.print statement of the code below returns a string that contains the password and the user name but the second debug.print statement only shows the value of the User ID parameter. As a result when I try to create a recordset I get an error message that I do not have permission to the data.
Following the exact same procedure from Excel 2000 in Windows XP the ADODB connection string was not eliminating the password value and I was able to open a recordset.
Any suggestions?
Public Function sDbConnection() As String
Dim sString As String
Dim sConnection As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
' Provide path to the system Db
conn.Properties("Jet OLEDB:System database") = "S:\UTL\RscMgmt\SECURE.MDW"
' Username and password to the secured access database
conn.Properties("Password") = "MyPassword"
conn.Properties("User Id") = "MyUserID"
Debug.Print conn.ConnectionString
' Open a connection to the Access 2000 db and return the connection string
conn.Open "Data Source=" & "S:\UTL\RscMgmt\GasPortfolio 2000.mdb" & ";"
sConnection = conn.ConnectionString
Debug.Print sConnection
sDbConnection = sConnection
End Function
