Problems with VB6, Access 2000 and ADO

Question:
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.

Answer:
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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS