|
in one of two ways:
For intCounter = 0 To object.collection.Count - 1
'''' do something with the object
Next intCounter
or, if you are using VBA, you can use
For Each object In Collection
'''' do something with the object
Next object
The second form is obviously more compact and readable. (This is just one of the examples of how VBA is superior to Visual Basic or Access Basic for working with collections).
Opening and Closing Object Variables
With VBA (and Visual Basic and Access Basic in earlier versions), you can set object variables to represent an object in the DAO hierarchy. For example, the following code sets a TableDef object variable to a specific table in the database, and then uses that variable to point to the TableDef:Dim tdfTmp As TableDefSet tdfTmp = DBEngine.Workspaces(0).Databases(0).TableDefs("Customers")Debug.Print tdfTmp.Name
To close the object variable, use the Close method. Note that there are certain objects which you do not want to close without understanding how DAO interacts with object close commands:
- Version 3 of DAO allows you to close a Workspace object, but it closes all of that workspace''''s child objects.
- If you close the current database in Access using the DBEngine(0)(0) syntax, any other DAO variables you have under DBEngine(0)(0) will also be closed.
There are other internal idiosyncracies that are documented in the README.TXT file that ships with Access 95 and VB4.
![[Dividing Line Image]](/Article/UploadFiles/200904/2009042316384258.gif)
Object Creation and Modification
DAO allows you to create new objects, and modify existing ones. This section outlines the steps necessary to accomplish this.
Creating Objects
To create an object, the general steps are:
- Use the appropriate Create method
- Set properties
- Create child objects
- Set child object properties
- Append it to the collection
The rest of this section shows the SQL and DAO ways to create objects.
Creating a Database
DAO:Dim dbNew As DatabaseSet dbNew = CreateDatabase("C:\MYTEST.MDB, dbLangGeneral, dbVersion30)
SQL:
No SQL equivalent.
DAO:Dim dbsTmp As DatabaseDim tdfTmp As TabledefDim fldTmp As FieldSet dbsTmp = OpenDatabase("C:\MYTEST.MDB")Set tdfTmp = dbsTmp.CreateTableDefs("MyTable")Set fldTmp = tdfTmp.CreateField("MyField", dbText)tdfTmp.Fields.Append fldTmpdbsTmp.TableDefs.Append tdfTmpdbsTmp.Close
SQL:Dim dbsTmp As DatabaseSet dbsTmp = OpenDatabase("C:\MYTEST.MDB")dbsTmp.Execut ("CREATE TABLE MyTable (MyField Text);")dbsTmp.Close
Setting Field Properties
DAO:
Dim dbs As Database
Dim tdf As TableDef
Dim fldID As Field
Dim fldName As Field
Dim fldResponse As Field
Dim fldClass As Field
Set dbs = OpenDatabase("C:\MYTEST.MDB")
Set tdf = dbs.CreateTableDef("Marketing Survey")
set FldID = tdf.CreateField("ID", dbInteger)
fldID.Required = True
Set fldName = tdf.CreateField("Name", dbText)
fldName.Required = True
fldName.Size = 40
fldName.AllowZeroLength = True
fldName.DefaultValue = "Unknown"
Set fldResponse = tdf.CreateField("Response", dbMemo)
set fldClass = tdf.CreateField("Class", dbText, 10
fldClass.Required = True
fldClass.ValidationRule = "in(''''A'''',''''B'''',''''X'''')"
fldClass.ValidationText = "Enter of of A, B, or X"
tdf.Fields.AppendFldID
tdf.Fields.Append fldName
tdf.Fields.Append fldResponse
tdf.Fields.Append fldClass
dbs.TableDefs.Append tdf
dbs.Close
SQL:
Using SQL Data Definition Language statements, you can only specify the field names and data types. Use programmatic DAO access to specify all properties.
Using Temporary Objects
Unlike other objects in the DAO hierarchy, you can create QueryDef objects and use them without having to append them to the QueryDefs collection. This is a powerful feature that allows you to create queries on the fly, execute them, and not have to worry about deleting them from the database when you are done with them.
Modifying Objects
Some properties can''''t be changed on existing objects. For example, although you can add new fields to an existing table, you cannot change the data type of an existing field. DAO strives to be as lean and efficient as possible. The act of changing a field''''s data type actually relies on a number of operations such as restructuring the table, converting all the existing data, and writing the converted data back to the original table. If DAO were to support such operations, it would be a much larger component and require much more memory,
Some applications that use DAO, such as Microsoft Access, allow you to change the data type of an existing field. This is accomplished through functionality supplied by Access, not DAO. If you want to modify an object such as the field data type, you must:
- Create a new table
- Clone the source table''''s structure to the new table
- Change the data type of the desired field while creating the new table
- Run an Append query that copies the data from the source table to the new table
- Delete the source table and rename the new table to the source table''''s name
Types of Properties
When using DAO to access Jet databases, it is important to understand the types of properties available through Jet. Properties are divided into two categories:
Engine-Defined Properties
These properties are defined and managed by Jet. That means that these properties will always exist for new objects. For example, when you create a new field object, it always has a default set of properties such as Type and AllowZeroLength.
User-Defined Properties
These are properties that are defined by the user of Jet. This user can be your application, or an application such as Microsoft Access. When you use Jet to create an object, User-Defined properties do not exist until you create them. As an example, if you create a field object in Microsoft Access using DAO, certain Access-defined (which in this case also means user-defined since Access is the "user" of Jet) properties will not exist (such as "Description") until you either create the property using DAO, or use the Microsoft Access interface to add a value in the Description field.
Working with Data
There are several advanced topics regarding data access using DAO. This section covers these topics.
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
