PQ Systems Knowledge Base

Problem:

Ref #757, Last Revision 02-14-2003.

A Microsoft Access database can be password protected using one of the following methods:

·        Database Password – A password is applied to the entire MDB file.  The password must be supplied to open the database.

·        Workgroup or User-Level Security – Users and Groups are defined in a security database (*.MDW file).  Both a User and Password must be supplied to open the database.

If your Microsoft Access database uses either of these methods, you may get errors when trying to define the chart definition or chart the data.

An error that is similar to one of these is a clue to this problem (although other errors not mentioned here may also occur):

PqCrDef.OpenDatabase Cannot open workgroup file -or-

PqCrDef.OpenDatabase JET ODBC driver Cannot open workgroup file

Or any OpenDatabase error which mentions "workgroup", "user", "security", "password" or "privileges"

Solution:

Use the ADO provider as shown below. If these steps do not solve your problem, contact PQ Systems technical support (contact information is at the bottom of this page).

For an Access MDB protected with a “Database Password”:

Using the "ODBC Driver for Access – Standard security."

1. In CHARTrunner define a new chart.
2. On the Data Source tab, select "ADO; OLE DB provider."
3. In the ADO provider drop down list, select "ODBC Driver for Access – Standard security."
4. The connection string should look like this:
   
   Driver={Microsoft Access Driver (*.mdb)}; Dbq=<database>; Uid=Admin; Pwd={{PASSWORD}};
5. Replace <database> with the full path to the .MDB file.
6. Replace {{PASSWORD}} with the database password, or leave {{PASSWORD}} as shown in the connection string and enter the database password in the Password field above.

Using the "OLE DB Provider for Microsoft Jet - Database password."

1. In CHARTrunner define a new chart.
2. On the Data Source tab, select "ADO; OLE DB provider."
3. In the ADO provider drop down list, select "OLE DB Provider for Microsoft Jet - Database password."
4. The connection string should look like this:
   
   Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<database>; Jet OLEDB:Database Password={{PASSWORD}};
5. Replace <database> with the full path to the .MDB file.
6. Replace {{PASSWORD}} with the database password, or leave {{PASSWORD}} as shown in the connection string and enter the database password in the Password field above.

For an Access MDB protected with “Workgroup Security”:

Using the "ODBC Driver for Access – Workgroup security."

1. In CHARTrunner define a new chart.
2. On the Data Source tab, select "ADO; OLE DB provider."
3. In the ADO provider drop down list, select " ODBC Driver for Access - Workgroup security."
4. The connection string should look like this:
   
   Driver={Microsoft Access Driver (*.mdb)}; Dbq=<database>; SystemDB=<system database>; Uid={{USER}}; Pwd={{PASSWORD}};
5. Replace <database> with the full path to the .MDB file.
6. Replace <system database> with the full path to the Access security database (.MDW file).
7. Replace {{USER}} with the user name, or leave {{ USER }} as shown in the connection string and enter the user name in the User field above.
8. Replace {{PASSWORD}} with the database password, or leave {{PASSWORD}} as shown in the connection string and enter the database password in the Password field above.

Using the "OLE DB Provider for Microsoft Jet - Workgroup security."

1. In CHARTrunner define a new chart.
2. On the Data Source tab, select "ADO; OLE DB provider."
3. In the ADO provider drop down list, select "OLE DB Provider for Microsoft Jet - Workgroup security."
4. The connection string should look like this:
   
   Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<database>; Jet OLEDB:System Database=<system database>; User Id={{USER}}; Password={{PASSWORD}};
5. Replace <database> with the full path to the .MDB file.
6. Replace <system database> with the full path to the Access security database (.MDW file).
7. Replace {{USER}} with the user name, or leave {{ USER }} as shown in the connection string and enter the user name in the User field above.
8. Replace {{PASSWORD}} with the database password, or leave {{PASSWORD}} as shown in the connection string and enter the database password in the Password field above.