I am doing a project where VB6 talks to Access 2000 thru ADO. I am using a login dialog to obtain the username, password & DSN (ODBC Data source name) to connect with the Access 2000 database. The code for this works fine when using it to connect with the Access 2000 database.
Since I needed to implement the security at database level, I used the “User Level Security Wizard” of Access to “secure” the database (it creates a workgroup file containing the user logins, and you can add new users and define the passwords). The moment I try to connect with this “secured” database, I get (ODBC driver ???) errors like
“You do not have the necessary permissions to use the ‘(unknown)’ object. Have the system administrator or the person who created this object establish the appropriatepermissions for you ” and the usual “Driver’s SQLSetConnectAttr failed.”
Should I be specifying the workgroup file in my connection string? I tried the help topics but there was no info on this??? I tried with both ADO 2.0 and 2.1. The error is the same.
You have two options. First using ODBC and second using OLE DB. Since you say you are using an ODBC DSN, you can specify the System Database in the DSN. If you edit the DSN you will see a section for specifying the system database of the connection.
The second method is to use the Properties collection of the connection object. Using the Microsoft Jet 4.0 OLE DB Provider you can specify the following workgroup database associated with your Access mdb. The following code will do the trick:
Dim cnnDB As ADODB.Connection Set cnnDB = New ADODB.ConnectionWith cnnDB .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Jet OLEDB:Database Password") = _ "yourDatabasePassword" .Properties("Jet OLEDDB:System Database") = _ "pathToSystemDB" .Open ConnectionString:="Path", _ UserID:="yourUserID", _ Password:="yourPassword"End With