Techniques for compatibility with non-Access databases in VB

Based on the structure of VB database and a concrete example, this paper expounds the methods and techniques of compatible non-access format database in VB.

Keywords: VB, non-Access database, data Access objects

A complete database management system (DBMS) should be compatible with a variety of popular data formats in the market, it fully consider the actual requirements of different users. Since there are many kinds of database formats (such as FoxPro, DBase, Paradox, etc.) are popular in the market, so it is very important to be compatible with non Access database in VB database application.

As a popular development platform, VB provides a powerful database function. There are three main types: Data control method: Using Data Access Object (Database Access Objects) method, directly invoke ODBC 2.0 API interface function method. The method that invokes the data access object has the advantages of convenience, flexibility and powerful function compared to the other two methods. This paper, starting from the method of invoking database Access object, realizes the operation of non access format database (taking FoxPro database as an example), the structure of copy database, and dynamically Janvenku, and expounds the feasibility of making up VB's insufficient support to these foreign databases from the programming skills.

The architecture of VB database

The core structure of VB database is the so-called Microsoft Jet database engine, which provides basic methods and means for VB and database interface. The jet engine is shared by Visual Basic, Microsoft Access, and other Microsoft products. Thus, in VB, Access database format is a standard built-in format, and all non-access databases are referred to as foreign databases.

The jet engine acts like a "panel" on which multiple indexed sequential access methods (i.e. ISAM) data drivers can be inserted. This is the real reason why VB has rich support for non-access databases. VB Professional Edition provides FoxPro, DBASE (or Xbase), Paradox, Btrieve and other databases of the ISAM driver, which allows VB to support these database formats. In addition, many other compatible ISAM drivers can also be obtained from the vendor's after-sales service. In theory, VB can support all compatible ISAM database formats (provided that only the ISAM driver interface program of these databases is obtained).

parameter settings for using non-Access databases and parameter reading of configuration files

It is noteworthy that most programmers do not pay attention to the use of database configuration files, but this is extremely important.

If you use a database operation in a VB program, you must provide a database configuration after you generate an EXE file for the application or package it to build the installer. INI) file, you can set up different types of databases in the INI file. If this INI file is not found, it will cause the database to be inaccessible. Normally, the file name of the INI file is the same as the name of the application, so if it is not indicated, the VB program will look for the INI file with the same name as the application in the Windows subdirectory. You can use the Setdataaccessoptions statement in VB to set up the INI file.

The use of the Setdataaccessoptions statement is as follows:

Setdataaccessoptions 1,inifilename

Where the Inifilename parameter indicates the file name with the path to the INI file.

It is noteworthy that when the application can not find this INI file, or call the OpenDatabase function when the value of its connect parameter is not set to the standard value of VB, such as the FoxPro 2.5 format set for "FoxPro;" (Should be "FoxPro 2.5;" Or if the appropriate ISAM driver is not installed, VB will display an error message "Not Found installable ISAM" at this time.

Typically, an INI file is generated before the application is distributed, or dynamically generated at installation time, or it can be generated in the application itself. Typically, this INI file has settings such as "[Options]", "[ISAM]", "[Installed ISAMs]", "[FoxPro ISAM]", "[DBASE ISAM]", "[Paradox ISAM]", and so on. For a complete application, there should also be a set segment that belongs to the application itself, such as "[MyDB]". The more important database parameters, such as datatype, Server, database, Openonstartup, Displaysql, QueryTimeout, can be set up to qualify the application's general operating environment.

The Windows API interface function provides a oswriteprivateprofilestring function in the dynamic-link library provided by the WIN95 system, which writes information according to the writing format of Windows configuration file (. INI).

In general, the application also needs to read the parameters of the dependencies within the configuration file at run time. For example, PageTimeout (page lock timeout time), MaxBufferSize (buffer size), LockRetry (the number of retries when lock failure), and so on, through the reading of these parameters to the application running environment, the capture of potential errors will be greatly improved.
The database configuration file for this application is Mydb.ini, and the process is as follows:

The following are the referenced contents: Funtion getinistring$ (Byval fname$,byval szitem$,byval szdefault$) ' This custom function implementation INI file reads the parameters within the section Dim Tmp as String, x as Integer TMP = String (2048,32) x = osgetprivateprofilestring (Fname$,szitem$,szdefault$,tmp,len (TMP), "Mydb.ini") getinistring = mid$ (tmp,1,x) End Function

This function enables you to read a variety of database formats.

Third, the method of invoking data access object to programming non-access database and its example

VB Professional version of the use of database Access object variables (DAO) is the most powerful and flexible programming features. It can access the management functions of ODBC 2.0 in the program, can control a variety of recordset types: Dynaset,snapshot and table record collection objects, can store procedures and query actions, can access database collection objects, such as Tabledefs,fields, Indexes and querydefs, with real ability to deal with things. This approach works well for most cases of database processing.

From the point of view of VB program code, the Recordset object (Recordset) provided to VB Programmer is independent of the format and type of database used. Databases such as FoxPro can still use a large number of database Access object variables, which provides the most important prerequisites and methods for access to non-Access databases.

Translating from one database type to another in VB requires little or no code modification. Moreover, although dBASE, Paradox's own DDL (data definition Language, the database definitions language), and DML (data manipulation Language, the database manipulation language) are unstructured queries, However, they can still be manipulated using VB SQL statements and the jet engine.

Thus, for non-access databases such as FoxPro, the method of invoking database Access objects is also the best choice.

(i) Non-Access database new and library structure modification

The database Access object variables in VB Professional Edition can be divided into two categories, one for the maintenance and management of database structure and the other for data access. The following objects can be used when representing the database structure: databases, TableDef, Field, Index, and three collections (Collection): tabledefs, fields, and indexes. Each collection is made up of several objects, and the collection of these data objects can be viewed entirely as an array and called by the method of the array.

Once a database object is established, it can be used to modify and process the structure of the database.

For non-Access databases, most of them correspond to a directory, so you can use the mkdir statement of VB as a directory, that is, to create a new database. Each non-Access database file can be considered a datasheet (table) in this directory, but in fact they are independent of each other.

The following is an example of a new FoxPro 2.5 format database.

The

The following is the referenced content: Sub CreateNew () Dim Db1 As Database, Td as TableDefs Dim T1 as new tabledef,f1 as New field, F2 as New field, F3 as new field< Br>dim Ix1 as New Index Dim Path as String Const db_text = 10,db_integer = 3 ChDir "\" path$ = InputBox ("Please enter New pathname: ", Input Dialog") MkDir path$ ' Create a new subdirectory Set Db1 = OpenDatabase (path$, True, False, "FoxPro 2.5;") Set Td = Db1.tabledefs T1. name = ' MyDB ' Creates a new data table named MyDB F1. name = ' name ', F1. Type = DB_Text, F1. Size = F2. Name = "Class", F2. Type = DB_Text, F2. Size = F3. Name = "Grade", F3. Type = DB_Integer T1. Fields.Append F1 ' Add these fields to the datasheet T1. Fields.Append F2 T1. Fields.Append F3 Ix1.name = "Name", Ix1.fields = "Name", Ix1.primary = True ' new index T1. Indexes.append Ix1 ' Add a new index to the Indexes collection of the database Td.append T1 ' Add a new table to the TableDefs collection Db1.close ' must close the database object before exiting the end Sub

It is worth noting in this program that a new non-Access database is not created with a createdatabase function, but rather a opendatabase function, which is very different from an Access database. However, it is only for non-access databases that you can use the OpenDatabase function to create a new database object.

In VB, the different format of the foreign database is only reflected in the last parameter connect of the OpenDatabase function, and the value of the Connect parameter of the foreign database in different format is also different, in addition, in the VB Professional Edition its programming method and the procedure and the skill are basically same.

When you create a new subdirectory, you cannot enter it with the CHDIR statement, otherwise the error "' MyDB ' is not a valid path" appears. At the same time, the definition of new Field objects such as F1, F2, F3 must also be defined separately, otherwise the error "Element Not defined" (the variable is undefined) will appear.

A copy of the library structure of a non-access database can also be implemented through certain programming techniques, and the following is a corresponding program.

function GetPos (tfname$) ' This custom function completes positioning to the last "\" symbol in the path file name

The following are the referenced contents: Dim I as integer,tmp as String tmp$ = tfname$ For I = 0 to 255 pos% = pos% + InStr (1, tmp$, "\") e1% = INSTR (1, tmp$, "\") tmp$ = right$ (tmp$, Len (tfname$)-pos%) If e1% = 0 Then ' finds the location of the last "\" symbol and notes GetPos = pos% Exit for End If Next I End Funtion Sub Copystruc () Dim Db1 as database, Ds1 as dynaset,td as TableDefs, Fld as Fields Dim Fname,sourcef,destf,path as string,pos1 as Integer CMD1. Filter = "FoxPro database file (*. DBF) |*. dbf| All Files |*.* "' CMD1 as the control name for a dialog box CMD1. DialogTitle = "Transfer into MS FoxPro database File" CMD1. FilterIndex = 1 CMD1. Action = 1 destf$ = inputbox$ ("Enter the destination file name:", "Enter dialog box") If CMD1. FileName = "" "Or destf$ =" "Then MsgBox "source or destination file name is empty" Exit Sub Else sourcef$ = CMD1. Filename End If FileCopy sourcef$, destf$ pos1% = GetPos (sourcef$) path$ = left$ (sourcef$, pos1%) ' Gets the path name of the source file fn$ = left$ (destf$, INSTR (1, destf$, ".")-1) ' Get the database name of the new file ' fn$ is the actual FoxPro database name, which is also the value of the Source property within the CreateDynaset function Set Db1 = OpenDatabase (path$, True, False, "FoxPro 2.5;") Set Ds1 = Db1.createdynaset (fn$) If no records in the If ds1.eof and Ds1.bof Then ' database exit totalnum% = 0 MsgBox "This datasheet is an empty table!" " Exit Sub End If ' Delete record, keep library structure Ds1.movefirst Todo Ds1.delete Ds1.movenext Loop Until ds1.eof End Sub

Visible, the method of copy library structure is to copy an existing database into a new file, then delete all the records in the new file, keep its library structure, get a new library structure complete empty library.

(ii) Dynamic transfer of non-access databases

In many cases of practical application, it is often necessary to transfer, display and print the records of a database that does not know the structure of its specific libraries in advance. Therefore, the realization of the dynamic transfer of unknown format database is also an important symbol to evaluate the compatibility of VB database application.

In VB, the grid control is very suitable for browsing the data in the database, just put the data into the grid.

The key to dynamically entering when using a grid is the record (Colume) content and field (Row) content, including the name of the field, type, value, etc.), so it is necessary to generate a dynaset object that can correspond to all or part of a record in one or more data tables. The dynaset object can also be the result of a dynamic query, which can be used to add, delete and modify records.

Here is a section of the program that displays the FoxPro database in a grid.

The following are the referenced contents: Sub Dbload () Dim Db1 as database, Ds1 as dynaset,td as tabledefs,fld as Fields Dim Fname,tmp,path Totalnum as string,i,j,pos1 as Integer Dim MyNum ' defines a variant type of data CMD1. Filter = "FoxPro database file (*. DBF) |*. dbf| All Files |*.* " CMD1. DialogTitle = "Transfer into MS FoxPro database File" CMD1. FilterIndex = 1 CMD1. Action = 1 fname$ = CMD1. Filename pos1% = GetPos (fname$) path$ = left$ (fname$, pos1%) tmp$ = right$ (fname$, Len (fname$)-pos1) fn$ = left$ (tmp$, Instr (1,tmp$, ".") -1) Set Db1 = OpenDatabase (path$, True, False, "FoxPro 2.5;") Set Ds1 = Db1.createdynaset (fn$) If ds1.eof and Ds1.bof Then ' database table has no records then exits Totalnum = 0 MsgBox "This datasheet is an empty table!" " Exit Sub Else ' shows the actual number of records in the database table Ds1.movelast Totalnum = Ds1.recordcount Grid1.rows = totalnum + 1 ' The actual number of rows in the grid Total.caption = str$ (totalnum) End If ' The actual number of columns in the grid and the width of each column Set Td = db1.tabledefs Set Fld = Td (fn$). Fields Grid1.cols = Fld.count + 1 Grid1.colwidth (0) = 600 For I = 1 to Fld.count Grid1.colwidth (I) = 1500 Next I ' Fill in the field name in the first row of the grid Grid1.row = 0, Grid1.col = 0 Grid1.text = "ordinal" For I = 1 to Fld.count Grid1.col = I Grid1.text = Fld (I-1). Name Next I ' Fill in the grid with the appropriate data Ds1.movefirst I = 1 Do as not ds1.eof Grid1.rowheight (I) = 300 Grid1.row = I Grid1.col = 0 Grid1.text = I For J = 1 to Fld.count Grid1.col = J MyNum = Ds1.fields (J-1). Value ' Evaluate the data type of the record and do the corresponding processing If IsNumeric (MyNum) Or IsDate (MyNum) Then Grid1.text = str$ (Ds1.fields (J-1). Value) Else If VarType (MyNum) = 8 Then Grid1.text = Ds1.fields (J-1). Value Else If VarType (MyNum) = 0 Or VarType (MyNum) = 1 Then Grid1.text = "" End If On Error Resume Next Next J Ds1.movenext i = i + 1 Loop Ds1.close Db1.close Exit Sub

Finally, it should be remembered that in the VB database application before running, must be in the Autoexec.bat file to add a sentence share.exe/l:500.

All of the above procedures are in the pentium/166 machine, Chinese Windows95 with VB4 debugging through.

Iv. concluding remarks

The compatibility of non Access databases is an integral part of VB database programming. It is therefore extremely important to have a good command of the programming of non-Access databases using DAO methods. Moreover, the application of certain programming skills can also help to make up for the lack of VB support for external database.