Detailed information about the differences in ADO access to different databases

ado| Access | data | database

Microsoft offers a unified data access interface, ActiveX database Object, or ADO. But in practical application programming, access to different databases, such as access, SQL Server, or VFP, there will be some "subtle" differences, there is often such a case, the ADO object access to a database of programs are debugging passed, but with the same program access to other databases there is such or such a problem, The opposite is the same. This difference seems to me to have no meaning, because it is a company's products, although this difference is not much, but to understand its ins and outs, enough to make users busy for a while, a unwarranted increase in the burden of users. Below, this article discusses some of the differences between the SELECT statement and the access to different databases.

In SQL Server, Access, VFP three databases, each has the following table name is "Employee" data table.

to access the difference between Date-type fields

In most cases, there is no difference between using the SELECT statement to access the table of the three databases, which means that if you write a SELECT statement to access the SQL Server database, you can also access the Access database or VFP database, as long as you change the connected data source. But if you want to inquire about the date field of "birth", for example, to query employee data from January 1, 1980 to December 31, 1985, the statement to access the datasheet in SQL Server is:

Select * from employee where birth date between ' 1980-01-01 ' and ' 1985-12-31 '

If you want to use this statement to access the Access database of course, it's a big mistake to add a function datevalue (), the correct way to do this is as follows:

Select * from employee where birth date between DateValue (' 1980-01-01 ') and DateValue (' 1985-12-31 ')

If you want to access VFP date field, you must use the following style:

SELECT * FROM employee where birth date between {^1980-01-01} and {^1985-12-31}

The above is the difference between a SELECT statement and a date-type field that accesses different databases, and if you don't understand the difference, you'll be disoriented and confused.

Differences in Statistics

On the employee data sheet of the above example, according to the department statistics total wages and numbers, using VB to write a section of statistics and display code, if the Connection object conn, Recordset object RS, and has been connected to a data source, the specific code as follows:

Open the Connection object conn ' to open SQL Server, Access, VFP data source Conn. CursorLocation = adUseClient ' Set cursor to client ... Rs. Open Select Department, SUM (payroll), COUNT (*) from the employee Group by department, conn Set DataGrid1.DataSource = rs ' binds RS to the DataGrid1 control and shows Show .....

You can see that the field names for the tables produced by the employee tables in SQL Server, Access, and VFP, three different databases, differ from each other (Figure 1, Figure 2, Figure 3). Data table statistics in SQL Server do not produce field names; statistics for tables in Access produce statistical field names such as "Expr1001", "Expr1002", and statistics for tables in VFP that produce "sum_ wages", "CNT" statistics field names. Although this is not a big problem, and will not be like the example in the case of the wrong date format, the program will terminate the operation, but after all, different, know that these origins can be in the programming to avoid these "subtle" differences caused by many of the Troubles.

To solve this problem, so that access to different databases have unified code and a unified display interface, it is necessary to write a SELECT statement after the statistics field with the As column name, the column name can be taken, the following example modified code:

... Rs. Open "SELECT Department, SUM (payroll) as total wage, COUNT (*) as number from employee group by department", Conn ...

After the modified code is executed as shown in Figure 4, if you want to call the statistics field later, just call "total salary" or "number".