Microsoft Jet databases can be secured in one of
two ways: with either share-level security or user-level security. With
share-level security, the database is secured with a password. Anyone attempting
to open the database must specify the correct database password. With
user-level security, each user is assigned a user name and password to open the
database. Microsoft Jet uses a separate workgroup information file, typically
named "system.mdw" to store user information and passwords. See the
section, "Security" for more information about creating and using
secured Microsoft Jet databases.
Share-Level (Password Protected) Databases
The following listings demonstrate how to open a
Microsoft Jet database that has been secured at the share level.
DAO
Sub DAOOpenDBPasswordDatabase()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", _
False, False, ";pwd=password")
db.Close
End Sub
ADO
Sub ADOOpenDBPasswordDatabase()
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;" & _
"Jet OLEDB:Database Password=password;"
cnn.Close
End Sub
In DAO, the Connect
parameter of the OpenDatabase method sets the database
password when opening a database. With ADO, the Microsoft Jet Provider
connection property Jet OLEDB:Database Password sets
the password instead.
Opening a Database with User-Level Security
These next listings demonstrate how to open a
database that is secured at the user level using a workgroup information file
named "sysdb.mdw".
DAO
Sub DAOOpenSecuredDatabase()
Dim wks As DAO.Workspace
Dim db As DAO.Database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
db.Close
wks.Close
End Sub
ADO
Sub ADOOpenSecuredDatabase()
Dim cnn As New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Properties("Jet OLEDB:System database") = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"
cnn.Close
End Sub
In ADO, a Microsoft Jet provider-specific
connection property, Jet OLEDB:System database,
specifies the system database. This is equivalent to setting the DBEngine object's SystemDB property
before opening a database using DAO.
Notice that in this example, the Provider property is set as a property of the Connection object rather than as part of the ConnectionString argument to the Open
method. That is because before you can reference provider-specific properties
from the Connection object's Properties
collection, it is necessary to indicate which provider you are using. If
the first line of code had been omitted, error 3265 (adErrItemNotFound),
"ADO could not find the object in the collection corresponding to the name
or ordinal reference requested by the application." would have occurred when
trying to set the value for the Jet OLEDB:System database
property.
Note that in both DAO and ADO, setting the
system database may not be necessary. You may omit the code that sets the
system database if you want to use the current Microsoft Jet workgroup
information file as specified in the SystemDB key in the Microsoft Jet registry
entries. See the book Microsoft Jet Database Engine
Programmer’s Guide for more information about Microsoft Jet Security.
External Databases
The Microsoft Jet database engine can be used to
access other database files, spreadsheets, and textual data stored in tabular
format through installable ISAM drivers.
The following listings demonstrate how to open a
Microsoft Excel 2000 spreadsheet first using DAO, then using ADO and the
Microsoft Jet provider.
DAO
Sub DAOOpenISAMDatabase()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase(".\Sales.xls", _
False, False, "Excel 8.0;")
db.Close
End Sub
ADO
Sub ADOOpenISAMDatabase()
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\Sales.xls" & _
";Extended Properties=Excel 8.0;"
cnn.Close
End Sub
The DAO and ADO code for opening an external
database is similar. In both examples, the name of the external file
(Sales.xls) is used in place of a Microsoft Jet database file name. With both
DAO and ADO you must also specify the type of external database you are
opening, in this case, an Excel 2000 spreadsheet. With DAO, the database type
is specified in the Connect argument of the OpenDatabase method. The database type is specified in the Extended Properties property of the Connection
with ADO. The following table lists the strings to use to specify which ISAM to
open.
Database
|
String
|
dBASE III
|
dBASE III;
|
dBASE IV
|
dBASE IV;
|
dBASE 5
|
dBASE 5.0;
|
Paradox 3.x
|
Paradox 3.x;
|
Paradox 4.x
|
Paradox 4.x;
|
Paradox 5.x
|
Paradox 5.x;
|
Excel 3.0
|
Excel 3.0;
|
Excel 4.0
|
Excel 4.0;
|
Excel 5.0/Excel 95
|
Excel 5.0;
|
Excel 97
|
Excel 97;
|
Excel 2000
|
Excel 8.0;
|
HTML Import
|
HTML Import;
|
HTML Export
|
HTML Export;
|
Text
|
Text;
|
ODBC
|
ODBC;
DATABASE=database; UID=user; PWD=password; DSN=datasourcename; |
Note that if you are migrating from DAO 3.5 or
earlier with the FoxPro ISAM to ADO with the Microsoft Jet Provider, you will
need to use Visual FoxPro ODBC Driver as Microsoft Jet 4.0 does not support the
FoxPro ISAM.
The Current Microsoft Access Database
When you open Microsoft Access, you are opening
a Microsoft Jet database. When writing code within Access, you may often want
to use the same connection to Microsoft Jet as Access is using. To allow you to
do this, Microsoft Access 2000 exposes two mechanisms: CurrentDB()
and CurrentProject.Connection allow you to get a
DAO Database object and an ADO Connection
object, respectively, for the database Access currently has open.
The following listings demonstrate how to get a
reference to the database currently open in Microsoft Access.
DAO
Sub DAOGetCurrentDatabase()
Dim db As DAO.Database
Set db = CurrentDb()
End Sub
ADO
Sub ADOGetCurrentDatabase()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
End Sub
EmoticonEmoticon