VB and database (someone else's) 2__ database

In a Visual Basic application, you can write code to create a database, as well as many operations such as indexing, querying, and so on. This will require a few new objects.

, such as: "Database", "TableDef", "Field" and so on, these are collectively referred to as data Access Objects (AccessObject), that is, we often see DAO.

1. Create a database file

The first object is DBEngine, which is the top interface of DAO, under which a collection of workspace objects can be generated, and we are using a Workspace object's

The CreateDatabase method generates a new database file. First we will define the object variables.
Example: Dim newws as Workspace
Dim newdb as Database
Then, we need to indicate which workspace is generated in the NEWWS on behalf of DBEngine.
Cases:
Set newws = dbengine.workspace (0)
In the previous example, we defined the work environment as the workspace (0) object using DBEngine, which is the default value and will be used if we do not define it. But when

When you use other objects, such as workspace (1), you must redefine them. Below we use Workspace's CreateDatabase method to create the database file, the syntax format is as follows:
Set database = workspace. CreateDatabase (name, local [, Option])
which
Database: A variable of the type defined above, representing the newly created database object
Workspace: The workspace type variable previously defined, which represents the working environment used, will contain the new database object
Name: The path and name of the database file that will be created
Local: The expression is used to specify the rules for string comparisons, which are generally alphabetically compared and can be specified as Dblanggeneral
Option: This is an option to specify the version of the data format and whether the database is encrypted, and we can generally not specify this item.
Example: ' Set up a database file named sample in the VB directory of C drive
Set newdb = newws.createdatabase ("C:/vb/sample", dblanggeneral)

2. Create a table

Once the database file has been created, we are about to construct the database structure.
The first is a table, a database can contain many tables, and each table is a TableDef object. We still need to define the object variables first.
Example: ' Define three tabledef object variables
Dim NewTB1 as TableDef
Dim NewTB2 as TableDef
Dim NewTB3 as TableDef
We can define multiple tables for a single database file. In the example above, we have defined three sheets.
These tables are created using the database's CreateTableDef method, which has the following syntax format:
Set table = database. CreateTableDef ([Name[,attribute[,source[,connect]])
which
Table: A variable of the previously defined tables type
Database: A variable of the type of databases that will contain the newly created table
Name: Set new table names
Attribute: Used to specify the characteristics of the newly created table
Source: Used to specify the name of the external database table
Connect: The string variable contains some database source information The last three parameters are used when accessing some database tables, which can usually be defaulted.
Example: ' Create three new tables for newdb
' Named Newtable1, Newtable2, Newtable3, respectively.
Set NewTB1 = newdb.creattabledef ("Newtable1")
Set NewTB2 = newdb.creattabledef ("Newtable2")
Set NewTB3 = newdb.creattabledef ("Newtable3")

3. Create a field

Each table in the database contains multiple fields, each of which is a Field object, and we first define a Field object variable for each table.
Example: ' Define two field variables for the previously created table NewTB1
Dim TB1FLD (2) as Field
' Define three field variables for table NewTB2
Dim TB2FLD (3) as Field
' Define three field variables for table NewTB3
Dim TB3FLD (3) as Field

In the example above, we used an array, which simplifies the definition statement and allows you to define multiple fields in a table at the same time.
Here we will use the CreateField method of the Tabdef object to create the field fields with the syntax in the following format:
Set field = table. CreateField ([name [, type [, size]])
which
Field: Previous new defined Field object variable
Tables: Table type variable, which will contain the new Field field
Name: The newly defined field name
Type: Defines the types of new fields
Size: Specifies the maximum length of the field
Example: ' Create two new fields for NewTB1
Set newfld (0) = Newtb1.createfield ("name", Dbtext, 10)
Set newfld (1) = Newtb1.createfield ("Age", dbinteger,3)

4. Create an index

We can create indexes for new databases, so-called indexes are the records of the specified database sorted in a certain order, which can improve access and storage efficiency, and of course, the index is not a must

Created by. Each index created is an Index object, and each Index object contains several field objects, which are used to specify which field the database will follow to

Cited.
First, you define an object variable.
Example: ' Defining an Indexed object variable
Dim Newidx as Index
Dim IDXFLD as Field
Then we'll use the CreateIndex method to index the table, which has the following syntax:
Set index = table. CreateIndex ("name")
which
Index: The new Index object variable previously defined
Table: Type variable, which will contain the new index index
Name: Newly created index names
It's not enough to create an index, and the next thing to do is to specify the index field for the new index, so that index is indexed by this field, and you need to use the

CreateField method, the syntax is as follows:
Set field = index. CreateField ("name")
which
Field: Previous new defined Field object variable
Index: The new Indexed object variable in the previous step
Name: The name of the field in the library indicating that the index will be sorted by this field
The indexed object also has two more commonly used properties that can be set after the specified index field:
(1) Primary property: Indicates whether index represents the primary index, or true to indicate that the object is the primary index, or false to indicate that it is not the primary index.
(2) Unique attribute: Indicates whether the index value is unique, when the property is true to indicate that the index value is unique, the property is False when the index value is not unique (when the index is composed of multiple fields, will appear

）。

In the next section, we'll go through the final step of how to add the created index to the table.
The following example creates an index on the table NewTB1 and takes the "name" field as the Index field. Cases:
' Create a new index
Set newidx = Newtb1.createindex ("Index by name")
' Specify an index field for the index
Set idxfld = Newidx.createfield ("name")
' Set the appropriate properties
Set newidx.primary = True
Set Newidx.unique = True

5. Adding fields and tables

When we create a good field (and index) for each table, we also add fields (and indexes) to the table one by one, using the Append method.
When a field (and index) is added to a table, it can truly become part of a table. At this point, the object variable defined for the field also accomplishes its mission.
Example: ' Adding a newly created field to the table NewTB1
NewTB1.Fields.Append newfld (0)
NewTB1.Fields.Append NEWFLD (1)
' Add the newly created index field to the index
NewIdx.Fields.Append NEWFLD
' Add the newly created index to the table
NewTB1.Indexes.Append Newidx
Similarly, after we have established the table, we also use the Append method to add it to the database.
Example: ' Adding a new table NewTB1 to the database newdb
NewDB.TableDefs.Append NewTB1
So when we've added both the field and the table, a new database is basically built.

6. Summary

When you create a database structure, you can define any number of tables in a database, and you can define any number of fields in each table, and each field can define multiple records.
The following is a summary of the basic steps to establish a database, the complete code is as follows:
Cases:
' Create database file
' Define basic object variables
Dim NEWWS as WorkSpace
Dim newdb as Database
' Specify the working environment
Set newws = dbengine.workspace (0)
' Create database file
Set newdb = newws.createdatabase ("C:/vb/sample", dblanggeneral)
' CREATE TABLE ' defines a TableDef object variable
Dim NewTB1 as TableDef
' Create a new table for newdb, named Newtable1
Set NewTB1 = newdb.createtabledef ("Newtable1")
' Create field
' Define two field variables for table NewTB1
Dim TB1FLD (2) as Field
' Create two new fields for NewTB1
Set newfld (0) = Newtb1.createfield ("name", Dbtext, 10)
Set newfld (1) = Newtb1.createfield ("Age", dbinteger,3)
' CREATE index
' Define indexed object variables
Dim Newidx as Index
Dim IDXFLD as Field
' Create a new index
Set newidx = Newtb1.createindex ("Index by name")
' Specify an index field for the index
Set idxfld = Newidx.createfield ("name")
' Set the appropriate properties
Set newidx.primary = True
Set Newidx.unique = True
' Add the defined fields to the table NewTB1
NewTB1.Fields.Append newfld (0)
NewTB1.Fields.Append NEWFLD (1)
' Add the newly created index field to the index
NewIdx.Fields.Append NEWFLD
' Add the newly created index to the table
NewTB1.Indexes.Append Newidx
' Add table NewTB1 to database newdb
NewDB.TableDefs.Append NewTB1