Dynamic Database and table creation using ADO objects in VB

 

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 running the program, we also need to reference the object library "Microsoft ADO Ext 2.1. for DDL Security "(ADOX For short), whose 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 Columns that represent tables, indexes, or keywords. 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 you only generate a new Data table based on an existing Data table, you 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, the database has a "payroll" data table, which has the following fields: 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 to the form) 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.