1、VB创建 ACCESS数据库全解（VB creates ACCESS database full solution）This article is contributed by achm207VB ACCESS database to create solutions to create the database, dynamically create table, create a record, ADO ADOX2010-01-22 13:28 Abstract: This paper describes the VB program using ADO dynamic method to
2、 create database and data table, these methods have practical value in developing VB database application, it can improve the flexibility of database program.Keywords: database, data table, ADO, ADOXThe 1: question is proposedIn Visual Basic, data access interface commonly used are the following thr
3、ee: database access object (DAO Data, Access Object), the remote database objects (RDO, Remote Data Object) and ActiveX (ADO, ActiveX, Data data object Object). Database access technology has been improving, and each of these three interfaces represents the different stages of the development of the
4、 technology. The latest is ADO, a simpler, more flexible object model than RDO and DAO. Because of this, more and more people use ADO as the data access interface when developing database software with VB. In the development process, we usually use the method is to use the database management system
5、 (e.g. Microsoft Access) or VisData in VB to establish a database and data table structure, and then through the use of ADODC database control or reference to the ADO object to establish a connection with the database table in the program, and then through the database controls (e.g., text boxes, Da
6、taGrid etc.) to carry out various operations of the database. In this development process, we sometimes have to face the question of how to allow users to dynamically build their own databases and data tables in order to improve the flexibility of the program In the process of running the establishm
7、ent of their own required database and data table, its essence is to use code (or through programming) to build databases and data tables. As you all know, this is an easy event in Foxpro or ASP programming. So, in the VB database programming, and how to operate it? The VB database programming, if y
8、ou use DAO as database access interface technology, can be achieved by CreateDatabase combined with CreateTableDef method, there are many books and magazines about this way, this is no longer about; but if you are using the latest technology of ADO database access interface, you can find the book an
9、d no magazine article that how to use the code to create database and data table, sometimes we can be very necessary to use this method, here we have to solve this problem.2:, ADO, and ADOXLets take a brief look at ADO and ADOX. In VB6, the use of ADO database application development, we should refe
10、r to “Microsoft ActiveX Data Objects object library 2.5 Library“, it is referred to as ADO, it is the core of the VB6 database and VB database object groups, developers often referred to as VB6 in the library, you can see various versions it, from version 2 to version 2.6 there are a lot of people a
11、re familiar with it, then we are no longer in detail. To create databases and tables during the run of the program, we also refer to the object library “Microsoft ADO Ext 2.1. DDL Security“, referred to as “ADOX“, and its library name is “Msadox.dll“: For. ADOX is an extension of the ADO object and
12、programming model that extends ADO to include creation, modification, and deletion of schema objects such as tables and processes. It also includes security objects for maintaining users and groups, and granting and revoking privileges to objects. The object of ADOX is shown in the following table:O
13、bject specificationCatalog contains a collection that describes the schema of the data source schema.Column represents columns of tables, indexes, or keywords.Group represents the group account with access rights in the secure database.Index represents the index in the database table.Key represents
14、the primary key, external keyword, or unique keyword in the database table.Procedure represents the stored procedure.Table represents a database table, including columns, indexes, and keywords.User represents the user account with access rights within the secure database.View represents the filtered
15、 set of records or virtual tables.ADOX commonly used methods are: Append (including Columns, Groups, Indexes, Keys, Procedures, Tables, Users, Views (Create), create a new directory (Delete), delete the object in the collection (Refresh), update the objects in the collection and so on). For more inf
16、ormation about ADOX, see the Web page on ADOX for Microsoft published in http:/ generates new data tables based on the existing data tablesIf only new data tables are generated on the basis of existing data tables, we simply refer to the object library “Microsoft, ActiveX, Data, Objects, 2.5, Librar
17、y“ and then use Select. The Into statement is fine. For example: a database named Wage.mdb, the database has a “payroll data sheet, the field data table: number, name, salary, allowances, deductions, wages, real wages, there have been many records in the table. Now we have the real wages of more tha
18、n 2000 records were screened out to form a new table, the name of the new table input by the user from the text box, the new table we as long as three fields, they are: number, name, real wages. We can do it with the following procedures:Add a text box Text1 and a command button Command1 in the form
19、 beforehandDim, Conn, As, New, ADODB.ConnectionDim, RS, As, New, ADODB.RecordsetDim, command, As, New, ADODB.commandPrivate, Sub, Command1_Click ()Dim BM As StringDim SQL As StringIf Text1.Text “Then“BM = Trim (Text1.Text)Sql= “Select number, name, real wage Into“ + BM + From salary Where, 2000 sala
20、ry“Set command.ActiveConnection = connCommand.CommandText = SQLCommand.ExecuteElseMsgBox “you must enter a name.“End IfPrivate, Sub, Form_Load ()Dim str As StringSTR = App.PathIf Right (STR, 1) “ Then “STR = STR + “End IfPSTR = “Provider=Microsoft.Jet.OLEDB.3.51“;“PSTR = PSTR “PSTR = PSTR “cant chan
21、ge“ 4 “here to“ 3.51 “PSTR = PSTR in VB6, the 3.51 version of the Microsoft Jet OLEDB driver corresponds to the Access97 database. In other words, built by this method with the database and table with the Access2000 database and table are of the same type, you can directly use the Access2000 to open
22、, although you can use VB6 to access the database and the data table, but you cannot use Access97 or VB6 in the “visual data manager to directly open.When the program is running, as long as the user clicks the command button, you can create your own database. The code corresponding to the view comma
23、nd button is:Private, Sub, Command3_Click ()Set DataGrid1.DataSource = RSEnd SubThe code corresponding to the update command button is:Private, Sub, Command4_Click ()Rs.UpdateBatchEnd SubThis example is used to illustrate the problem in practical application, you can take it further improvement, for
24、 example: you in the form to add some text box and combo box for the user to enter or select the data table name, field names, field width and the number of decimal places. In this way, the user can determine all the contents of the database autonomously, and the flexibility of the program will be g
25、reatly improved.5: conclusionIf you are using the latest data access interface technology ADO (Microsoft strongly recommends that you use the latest technology), you want to create a database file in the program and data table, at this point, you should be in the Visual Basic 6 object reference libr
26、ary “Microsoft ActiveX Data Objects 2.5 Library“ and “Microsoft ADO Ext 2.1. For DDL Security, Create, Append and other methods and then use the ADOX object can be achieved. The version of the Microsoft Jet OLEDB driver used by this method is 4, so the database files and data tables built using this method can be opened directly using Access2000. The author has used this method many times during the development of VB database software. This method has a great effect on improving the flexibility of programs and satisfying the special needs of users.
