Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: ADO
Expertise: Beginner
Sep 21, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Problems with VB6, Access 2000 and ADO

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 appropriate permissions 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.Connection
With cnnDB
 .Provider = "Microsoft.Jet.OLEDB.4.0"
 .Properties("Jet OLEDB:Database Password") = _
 .Properties("Jet OLEDDB:System Database") = _
 .Open ConnectionString:="Path", _
            UserID:="yourUserID", _
End With
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date