Introduction to ADO of Visual Basic Data Connection

ADO

ActiveX Data Objects

The data connection on the logon interface of the VB system is almost required.

ADO isProgramObject used to represent the data structure and data contained in the user database. In the Microsoft Visual Basic Editor, you can use the ADO object and the additional components of ADO (Microsoft ADO extensions for DLL and Security (ADOX )) to create or modify tables and query, check databases, or access external data sources.

Connection method 1

Create an ADO reference for direct access.

Select Project/reference/Microsoft ActiveX Data Objects 2.5 Library

After the reference, the connection object and record set are defined.

Dim objcn as new connection define a connection object dim objrs as new recordsect define a record set object dim strcn as string 'define the connection character variable dim strsql as string' define the SQL statement string variable the next step is to establish a database connection. The ADO onstring attribute of the connection object of ADO is used to specify the basic information for connecting to the data source. Objcn. connectionstring = "provider = Microsoft. Jet. oledb.3.51;" & "Data Source =" & App. Path & "\ database \ instance 1.mdb" objcn. open' open the data source connection
 

Then run the query command to obtain user information.

 
Ado recordset is used for query.Some Properties of the object, activeconnection, represent the active connection of the data source.
 

  
Strsql = "select password from system user where username = '" & username & "'" set objrs. activeconnection = objcnobjrs. open (strsql) If objrs. EOF then 'determine whether query result' closes the database connection and releases the object objcn. closeset objrs = nothingset objcn = nothing it is best to return some error messages here. This is a user-friendly gperror: check_password = 255 set objrs = nothing set objcn = nothing
 
 
2. Using ADO to access ODBC data sources has the following features:
 
 
A. Access the database through ODBC
 
 
B. Use the adorecordset object to save data and execute the update method for real-time update.
 
 
ODBC Open Database Interconnection
 
 
(Open Database Connectivity, ODBC), which establishes a set of specifications and provides a set of standard APIs for database access (application programming interfaces ). These APIs use SQL to complete most of their tasks. ODBC also provides support for the SQL language. You can directly send SQL statements to ODBC. It has the advantages of both FoxPro, access andOracleDatabase, ODBC can be used
API. It can be seen that the biggest advantage of ODBC is that it can process all databases in a unified manner.
 
 
There are three ODBC data source types
 
User DSN: local computer data source, which can only be used by the current user
 
 
System DSN: local computer data source, which can be used by any user with Permissions
 
 
File DSN: the user-shared connection object that can be installed with the same driver may not be a local data source on the computer. When establishing a data source connection, the msdasql (Microsoft OLE dbprovider for ODBC) driver is used by default, therefore, you only need to specify the data source name in the connection string, for example, objcn. open "DSN = myaccess" to establish a database connection
 
 
When using ODBC to access data, you must first create an ODBC data source.
 
 
Under win7
 
 
1 start/control panel/select big icon/Data Source (ODBC command), open ODBC data source Manager, click Add, create new data source dialog box
 
 
 
 
2. Select the Microsoft Access Driver (*. mdb) in the table, click Finish, open the "odbcmicrosoft access installation" dialog box, and enter the data source name and other information.
 
 
3. Click Options, select a database, and click OK.
 
 
4. Click OK to close the "odbcmicrosoft access installation" dialog box. The new database name is displayed in the "ODBC data source Manager" dialog box.
 
 
Which isData Connection
 
FirstCreate an ADO referenceI will not elaborate on it here.
 
 
Connectionstring is the property name of the connection object. It provides the specific information required for the data provider or service provider to open a connection to the data source, including database, DSN, uid, and so on.
 
 
(1) Database: A string expression that specifies the name of the database on the server.
 
 
(2) uid: A string expression that specifies the user ID (User Account Name) for the ODBC data source ).
 
 
(3) PWD: string expression, which specifies the user password for the ODBC data source.
 
 
(4) DSN: A string expression that specifies the name of the connected ODBC data source.
 
 
Second, definitionA connection object, A recordset object, and a Boolean variable. The connection object is used to establish a data source connection. The recordset object is used to save the data retrieved from the data source. A boolean variable indicates whether the current record is modified. The statement is as follows:
 
 
Dim objrs as recordset is used to save the data source record set.
 
Dim objcn as connection is used to create a data source connection.
 
 
Dim isneedsave as Boolean 'is used to identify whether to save
 
 
ThirdCreate a data connection
 
 
 
 
Set objcn = new connection 'instantiate the connection object objcn. open "DSN = instance 2dsn" 'create database connection set objrs = new recordset' create client record set with objrs 'to set query. cursorlocation = aduseclient 'specifies the location of the temporary table. cursortype = adopenstatic 'uses a keyset cursor. locktype = adlockoptimistic 'specifies the lock type. open "select * from system user", objcnend
 
 
 
 
 
FinallyClose release object
 
 
Set objrs = nothing 'release record set object
 
Objcn = nothing 'release the data connection object
 
 
Set objcn = nothing 'release the connection object