Dynamically create databases and tables using ADO objects in the vbprogram

Abstract: This article describes how to use ADO objects to dynamically create databases and data tables in VB Programs. These methods are of great practical value in the development of VB Database applications, and can improve the flexibility of database programs.
Key words: database, data table, ADO, ADOX

1: Question proposal
In Visual Basic, there are three common data access interfaces: Database Access Object (DAO, Data Access Object) and remote database object (rdo, remote data object) and ActiveX Data Objects (ADO and ActiveX Data Objects ). The database access technology is constantly improving, and each of these three interfaces represents the different development stages of the technology. The latest is ADO, Which is simpler than rdo and Dao, but more flexible object model. As a result, more and more people are using ADO as the data access interface when developing database software using VB. In the development process, we usually use the following methods: first use the database management system (such as Microsoft Access) or visual data manager in VB to establish the database and data table structure, in the program, you can use the adodc Database Control or reference the ADO object to establish a connection with the table in the database, and then use the database-aware control (such as the text box and DataGrid) to perform various database operations. In this development process, we sometimes need to face the following problem: how can users dynamically create their own databases and data tables during the running process to improve Program Flexibility? In the process of running the program, you need to create your own database and data tables. In essence, you can use code (or programming) to create databases and data tables. As we all know, in Foxpro or ASP programming, this is an easy event. So how does one operate in VB Database programming? In VB Database Programming, If Dao is used as the database access interface technology, createdatabase can be used in combination with the createtabledef method. At present, many books and magazines have mentioned this method, this article will not talk about it anymore. However, if you are using the latest database access interface technology ado, you have found that there are no articles in books and magazines about how to use code to create databases and data tables. But sometimes we need to use this method. let's solve this problem.
2: ADO and ADOX
Let's start with a simple understanding of ADO and ADOX. In VB6, when using ADO to develop database applications, we need to reference the object library "Microsoft ActiveX Data Objects 2.5 library", which is short for ADO, it is the core object group of the VB6 database and is also the object library frequently referenced by VB Database developers. You can see its versions in VB6, from version 2.0 to version 2.6, many people are familiar with it and we will not detail it here. If you want to create a database and a table while the program is running, we also need to reference the object library "Microsoft ADO ext 2.1. for DDL Security (ADOX for short). The Library name is msadox. DLL. ADOX is an extension of ADO objects and programming models. It extends ADO to include creating, modifying, and deleting mode objects, such as tables and processes. It also includes security objects for maintaining users and groups, and granting and revoking permissions on objects. The following table lists the ADOX objects:
Object Description
Catalog contains a set of directories that describe the data source mode.
Column indicates a table, index, or keyword column.
Group indicates the group account with access permissions in the security database.
Index indicates the index in the database table.
Key indicates the primary keyword, external keyword, or unique keyword in the database table.
Procedure indicates the stored procedure.
Table indicates a database table, including columns, indexes, and keywords.
User indicates the user account with access permissions in the security database.
View indicates the filtering set of the record or virtual table.
Common ADOX methods include append (including columns, groups, indexes, keys, procedures, tables, users, and views), create (create a new directory), and delete (delete objects in the set) and refresh (Objects in the update set. For more information about ADOX, see the web page for ADOX instructions published by Microsoft in the http://www.microsoft.com/data/ado.

3: Create a new data table based on the original data table
If a new data table is generated only on the basis of an existing data table, we only need to reference the object library "Microsoft ActiveX Data Objects 2.5 library" and then use select... The into statement is enough. For example, there is a project named wage. mdb database, which has a data table named "payroll". The data table fields include: number, name, basic salary, allowance, pay-as-you-go, fee deduction, and pay-as-you-go, this table already has many records. Now, we filter out the records with a real-time salary of more than 2000 to form a new table. The name of the new table is input by the user from the text box. In the new table, we only need three fields: ID, name, and pay-as-you-go. We can use the following program: (add a text box text1 and a command button command1 in the form in advance)
Dim conn as new ADODB. Connection
Dim RS as new ADODB. recordset
Dim command as new ADODB. Command

Private sub commandementclick ()
Dim BM as string
Dim SQL as string
If text1.text <> "then
Bm = trim (text1.text)
SQL = "select number, name, pay-as-you-go into" + BM + "from payroll where pay-as-you-go> 2000"
SET command. activeconnection = Conn
Command. commandtext = SQL
Command. Execute
Else
Msgbox "you must enter a name"
End if

Private sub form_load ()
Dim STR as string
STR = app. Path
If right (STR, 1) <> "/" then
STR = STR + "/"
End if
Pstr = "provider = Microsoft. Jet. oledb.3.51 ;"
Pstr = pstr & "Persist Security info = false ;"
Pstr = pstr & "Data Source =" & STR & "wage. mdb"
Conn. Open pstr
Rs. cursorlocation = aduseclient
Rs. Open "payroll", Conn, adopenkeyset, adlockpessimistic
Set datagrid1.datasource = rs
End sub
Of course, we can also design the program better. For example, let the user select any required fields and conditions in the window and then combine them to generate a new table. However, in any case, this operation can only generate a new table based on the original table, and cannot generate a database file, and the new table and the original table are placed in the same database.

4. Create a new database and table
The method mentioned above has some limitations. It does not allow users to generate the database files they need. Even if data tables can be generated, they can only be generated based on the original table. Our current goal is to allow users to create databases and tables while using ADO objects, just as if they use access to create databases and tables. Therefore, we should reference the object library "Microsoft ActiveX Data Objects 2.5 library" and "Microsoft ADO ext 2.1. For DDL Security"
We may use an instance to describe the specific operation process and method. The purpose of our instance is to create a database during the program running. The database name is input by the user. Create a data table named "mytable" in the database. The data table has three fields: "number" (integer type) and "name" (sequential type, (8 in width), "address" (50 in width), add a record to the data table, and display the Record Content in the DataGrid Control, in addition, you can modify or add records in the DataGrid Control.
First, create a new form in VB, and then reference the object library "Microsoft ActiveX Data Objects 2.5 library" and "Microsoft ADO ext 2.1. For DDL Security" in the "project" menu ". Three form-level object variables and a form-level string variables are defined as follows:
Dim cat as new ADOX. Catalog 'can be used without cat using another name.
Dim conn as new ADODB. Connection
Dim RS as new ADODB. recordset
Dim pstr as string 'defines this variable for later writing convenience
To create a database more flexibly, we can add a general dialog box, a DataGrid Control, and three command buttons to the form. Their headers are: create, view, and update databases and tables. The general dialog box is used to enter the database file name and determine the database storage location. The code corresponding to the "Create Database and table" command button is:
Private sub commandementclick ()
The dim FM as string 'fm variable is used to obtain the user input file name.
Commondialog1.filter = "MDB File (*. mdb) | *. mdb | allfiles (*. *) | *. * |"
Commondialog1.filterindex = 1
Commondialog1.initdir = "D:/jthpaper"
Commondialog1.flags = 6
Commondialog1.action = 2
If commondialog1.filename = "" then
Msgbox "you must enter a file name. Please save it again! "
Exit sub
Else
Fm = commondialog1.filename
End if
Pstr = "provider = Microsoft. Jet. oledb.4.0;" 'You cannot change 4.0 to 3.51.
Pstr = pstr & "Data Source =" & FM
Cat. Create pstr 'create a database
Dim TBL as new table
Cat. activeconnection = pstr
TBL. Name = "mytable" 'table name
TBL. Columns. APPEND "no.", adinteger "indicates the first field in the table.
TBL. Columns. APPEND "name", advarwchar, the second field of the 8' table
TBL. Columns. APPEND "Address", advarwchar, the third field in the 50' table
Cat. Tables. append TBL 'create a data table
Conn. Open pstr
Rs. cursorlocation = aduseclient
Rs. Open "mytable", Conn, adopenkeyset, adlockpessimistic
Rs. addnew' Add a new record to the table
Rs. Fields (0). value = 9801
Rs. Fields (1). value = "Sun Wukong"
Rs. Fields (2). value = "Huashan, Guangzhou"
Rs. Update
End sub
The above program has a description, which is the statement: pstr = "provider = Microsoft. jet. oledb.4.0; ", this statement indicates that the Microsoft Jet oledb driver version is 4.0, which is the latest version, you can use the ADO object in VB to access the database created in Access2000 and earlier versions. You cannot change "4.0" to "3.51". Otherwise, the program cannot run normally. in VB6, the Microsoft Jet oledb driver of version 3.51 corresponds to the Access97 database. In other words, the databases and tables created using this method are of the same type as the databases and tables created using Access2000. You can only open them using Access2000 directly, although you can use VB6 to access such databases and data tables, you cannot directly open them using Access97 or "visualized Data Manager" in VB6.
When running the program, you only need to click the command button to create the database you need. The code corresponding to the "View" command button is:
Private sub command3_click ()
Set datagrid1.datasource = rs
End sub
The code corresponding to the "Update" command button is:
Private sub command4_click ()
Rs. updatebatch
End sub
This example is only used to illustrate the problem. You can further improve it in practical application. For example, you can add some text boxes and combos to the form, you can enter or select a data table name, field name, field width, and number of decimal places. In this way, the user can determine all the content of the database, and the flexibility of the program is greatly improved.
5. Conclusion
If you are using the latest data access interface technology ADO (Microsoft strongly recommends that you use this latest technology), and you want to create database files and data tables while the program is running, at this time, you should reference the object library "Microsoft ActiveX Data Objects 6.0 library" and "Microsoft ADO ext 2.5" in Visual Basic 2.1. for DDL security, and then use the CREATE, append, and other methods of the ADOX object. The Microsoft Jet oledb driver version used in this method is 4.0. Therefore, the database files and data tables created using this method can be opened directly using Access2000. I have used this method many times in the Process of VB Database software development. This method plays a great role in improving Program Flexibility and meeting the special needs of users.