Access provides many tools for managing databases which are not available from Visual Basic, such as forms, reports, etc. While it is possible to programmatically control Access from Visual Basic using DDE or OLE Automation, these are Access objects, not Jet objects. They are not part of the native interface available from Visual Basic.
Jet, on the other hand, provides a set of programmable objects which you can use to manage both the structure of a database and its data. While there are dozens of objects available in the Jet Data Access Object (DAO) library, you will most commonly be working with the Database object, Recordset objects, and QueryDef objects. You'll also need to understand the basics of using the DBEngine object and the Workspace object.
Data Access Objects
Although in most cases you will probably be using Microsoft Access to build your database structure, there may be times when you need to create, modify, or delete database objects in your code. For more information on modifying the database structure using DAO, you can read the Modifying Database Structure section.
The most common use for the DAO library, however, is to manage data. This is most often done using the Recordset object. With the Recordset object, you can insert, update, and delete records in the database. For more information on managing data with DAO, read the Managing Data section.
It should be noted that nearly all of the properties and methods available through DAO are also available using Structured Query Language (SQL). You can use SQL to create, modify, and delete objects and data in the database. In some cases, using DAO will be more straightforward than SQL and in other cases it is simpler to use SQL than DAO. The choice of using SQL or DAO is yours. You can use one or the other exclusively, or mix and match the two.
Let's first take a look at a simple example of opening an unsecured database.
Dim sDBName As String Dim ws As Workspace Dim db As Database ' GetDBName is some function that returns the full path to the .mdb file sDBName = GetDBName() ' set a reference to the default workspace Set ws = DBEngine.Workspaces(0) ' open the file Set db = ws.OpenDatabase(sDBName)If the database is secured, some additional steps are involved.
Let's take a look at an example.
Dim sSystemDBName As String Dim sDBName As String Dim sUserName As String Dim sPassword As String Dim ws As Workspace Dim db As Database ' GetSystemDBName is assumed to be a function that returns the full path of the system database sSystemDBName = GetSystemDBName() ' GetDBName is assumed to be a function that returns the full path of the file to open sDBName = GetDBName() ' GetAccountInfo is assumed to be a sub that returns the account and password in the paramter list GetAccountInfo sUserName, sPassword ' Assign the SystemDB property - this is the full path to the system.mdw file DBEngine.SystemDB = sSystemDBName ' Create a secure workspace Set ws = DBEngine.CreateWorkspace("foo", sUserName, sPassword) ' Open the database Set db = ws.OpenDatabase(sDBName)This is a fairly straightforward process, but rather poorly documented. Additionally, this applies only to 32-bit VB4 and Jet 3.0. If you are using an earlier edition of the Jet engine, you will still need to create an application initialization file with a miminum of the following:
[Options] SystemDB=Keep in mind that if you are working with a secured database, you must set the SystemDB property of the DBEngine before calling any other DAO code or the OpenDatabase method will fail.
AddNew method of the Recordset object
Dim rs As Recordset ' The dbAppendOnly constant is added to improve performance Set rs = db.OpenRecordset _ ("Customers", dbOpenDynaset, dbAppendOnly) ' use optimistic locking rs.LockEdits = False rs.AddNew rs.CustFirst = "Joe" rs.CustLast = "Garrick" rs.UpdateNotes: If you are using a counter field as the primary key for a table, you will often wish to retrieve the value of the new counter when inserting a record. You can do this by either assigning the value of the counter field to a variable before calling the Update method, or by returning to the new record using the built-in LastModified bookmark. I generally use the former method. To do so, delcare a long integer variable in the procedure and assign it the value of the counter field anywhere between using the AddNew and the Update methods. For example:
rs.AddNew lCustID = rs.CustID ... rs.Update
Execute method of the Database Object
Dim sSQLInsert As String sSQLInsert = "INSERT INTO Customers " & _ "(CustFirst, CustLast) VALUES ('Joe', 'Garrick');" db.Execute sSQLInsert, dbFailOnErrorNotes: Search the VB help files for "INSERT INTO" for a complete description of the syntax for inserting records using the Execute method. Also note that this is considerably less practical if you are using counters for your primary key since there is no readily available means of retrieving the new primary key value. While you could theoretically open a new recordset based on the table and move to the last record or query using a WHERE clause based on the newly inserted values, neither method is completely reliable. This method is generally better suited for inserting records where the value of the primary key can be predetermined and explicitly assigned to the appropriate field or fields.
Edit method of the Recordset object.
Dim sSQL As String Dim rs As Recordset sSQL = "SELECT CustFirst, CustLast " & _ "FROM Customers WHERE CustID = 1;" Set rs = db.OpenRecordset(sSQL, dbOpenDynaset) rs.LockEdits = False rs.Edit rs.CustFirst = "John" rs.CustLast = "Smith" rs.UpdateNotes: Here we have opened a Recordset using the CustID field, which is the primary key for the table, as the criteria for the WHERE clause of the query. Generally, using the primary key is the only reliable way of retrieving a single record. You could also open the table without any criteria and use a Find method or the Seek method, but doing so is normally less efficient than simply specifying the records to retrieve in the SQL statement.
Execute method of the Database Object
Dim sSQL As String sSQL = "UPDATE Customers " & _ "SET CustFirst = 'John', CustLast = 'Smith' WHERE CustID = 1;" db.Execute sSQLNotes: Here we have the same where clause for the query, but use the UPDATE ... SET syntax and the Execute method. This approach is generally more efficient than using the Edit method of the Recordset object, but the SQL syntax is somewhat more obscure and more difficult to build dynamically if you are passing the values to update as parameters to the procedure.
Delete method of the Recordset object
Dim sSQL As String Dim rs As Recordset sSQL = "SELECT CustID FROM Customers WHERE CustID = 1;" Set rs = db.OpenRecordset(sSQL, dbOpenDynaset) rs.DeleteNotes: The WHERE clause from the previous examples is again used to open a Recordset containing a single record, to which the Delete method is applied. When you use the Delete method, the current record remains current but is inaccessible. Note also that if you will be deleting a record, there is no reason to retrieve more than a minimum number of fields from the record. Using "SELECT * FROM Customers WHERE CustID = 1;" would simply be a waste of resources.
Execute method of the Database object
Dim sSQL As String sSQL = "DELETE FROM Customers WHERE CustID = 1;" db.Execute sSQLNotes: The same WHERE clause is again used. Like the other examples, this is generally a more efficient way of removing records. When using a SQL DELETE, there is no need to specify a field list.
The example code shown above is, of course, simply a skeleton of what a complete procedure would look like. In addition to the actual insert, update, or delete action, any procedure which manipulates data should also include the following:
INSERT INTO Customers (CustFirst, CustLast) SELECT ContactFirst, ContactLast FROM Contacts;There is also another type of insert construction that can be used to build a new table from existing records (known as a make-table query in Access). Here's the general form:
SELECTHere's what the previous example would look like if you were using the Contacts table to create a new table called customers:
SELECT ContactFirst, ContactLast INTO Customers FROM Contacts;If for some reason (perhaps a test too complex to be expressed as a SQL statement) you do need to do a bulk update using a recordset, the preferred method is to use a Do loop.
Do While Not rs.EOF rs.Edit ... rs.Update rs.MoveNext LoopIn the loop, rs.EOF is the test for the loop exit. The EOF property of a recordset will be True after moving past the last record. Even if you do have a total count of records, using a For loop is not recommended.
The choice of which to use is for the most part personal preference. Both methods are capable of defining database objects. In some cases you must use DAO. The following table shows the SQL and DAO equivalents for creating database objects.
|Table||Database.CreateTableDef||CREATE TABLE statement|
|Query||Database.CreateQueryDef||Unavailable. Use DAO CreateQueryDef|
ALTER TABLE ... ADD COLUMN
ALTER TABLE ... ADD CONSTRAINT
|Relationship||TableDef.CreateRelation||CONSTRAINT clause with CREATE TABLE or ALTER TABLE|
It is also possible to delete objects from the database structure.
If you are unfamiliar with the Jet security system, you should visit my Understanding Jet Security page.
Although in most cases you will be able to manage security attributes for a database using the Access interface, there may be times when you need to manage security using DAO. There may also be occasions where you need to give an end user access to an object for which they normally would not have permission.
The security information you can work with will fall into one of two categories:
Although there are Container objects for forms, reports, macros, and modules in an Access database, you will normally be working with the Tables Container, which represents saved tables and queries in a database.
There are three steps involved in assigning permissions for an object:
Dim lBitField As Long Debug.Print "Add 1 to the bit field twice." lBitField = 0 Debug.Print lBitField lBitField = lBitField + 1 Debug.Print lBitField lBitField = lBitField + 1 Debug.Print lBitField Debug.Print "Or 1 to the bit field twice." lBitField = 0 Debug.Print lBitField lBitField = lBitField Or 1 Debug.Print lBitField lBitField = lBitField Or 1 Debug.Print lBitFieldThe results will be as follows:
Add 1 to the bit field twice. 0 1 2 Or 1 to the bit field twice. 0 1 1As you can see, the results are not correct when using the addition operator with a bit field.
The following example illustrates assigning and revoking permissions for a document.
Dim doc As Document Dim sAccount As Group ' The Tables container holds both tables and queries, which share the same ' name space in a Jet database file. Set doc = db.Containers("Tables").Documents("Customers") ' set permissions for the Users group. ' Note that since user and groups share the same name space, there is only ' a UserName property. No GroupName property is necessary. doc.UserName = "Users" ' grant the Delete permission doc.Permissions = doc.Permissions Or dbSecDeleteData ' revoke the Update permission doc.Permissions = doc.Permissions And Not dbSecReplaceDataAnother important factor to remember when working with objects and permissions is that a user will inherit any permissions assigned to groups of which the user is a member. You can use the AllPermissions property of a Document object to examine both directly assigned and inherited permissions.
Normally, any permission assigned to the user or any group the user belongs to are available to the user. One situation where the permissions may not be what you expect occurs when working with attached tables. With an attached Jet table, the user (or any group the user belongs to) must have permissions for the table in both the database where the table is attached and in the database where the table actually resides. This also applies to tables attached from other sources, such as SQL Server. Although Jet is not aware of security settings on server databases, it cannot violate them. If you are working with server tables, you will need to make sure that the user has the appropriate permissions assigned on both the server and the locally attached tables.
Originally written by Joe Garrick