ADO reference Abstract: On the basis of OLEDB (established on the lower-layer ODBC, it is an application interface for SQL data sources and non-SQL data sources, such as emails and directories, microsoft introduces another Data Access Object Model: ADO (ActiveXDataObject: ActiveX Data Object ). ADO adopts DAO and RDO
ADO reference Abstract: Based on ole db (established on the lower-layer ODBC, it is an application program interface for SQL data sources and non-SQL data sources, such as emails and directories, microsoft introduces another Data Access Object Model: ADO (ActiveX Da ta Object: ActiveX Data Object ). ADO adopts DAO and RDO
ADO reference
Abstract: Based on ole db (established on lower-layer ODBC, it is an application interface for SQL data sources and non-SQL data sources, such as mail and directory operations, microsoft introduces another Data Access Object Model: ADO (ActiveX DaTa Object: ActiveX Data Object ). ADO uses DAO and RDO-based objects and provides an object model that is simpler than DAO and RDO. It contains some built-in objects that simplify data access tasks in the data storage area. At the same time, ADO has powerful functions and flexibility. It can connect to different databases using the same programming mode, providing us with database access interfaces.
ADO object
The ADO model defines six common objects: Connection, Command, Recordset, Error, Field, and Parameter. Through the attributes and methods of these objects, we can easily establish database connections and perform SQL queries and access operations. Intuitively describes the relationship between objects:
Connection object
Indicates the connection to the data source. Generally, use the collection, method, and attribute of the Connection object to perform the following operations:
1. Use the ConnectionString, ConnectionTimeout, and Mode attributes to configure the connection before opening the connection.
2. Use the DefaultDatabase attribute to set the default database to be connected.
3. Use the Provider attribute to specify the ole db Provider.
4. Use the Open method to establish a physical connection to the data source. Use the Close method to disconnect it.
5. Execute the connection command using the Execute method and configure the command using the CommandTimeout attribute.
6. Use the BeginTrans, CommitTrans, RollbackTrans methods, and Attributes to manage the transactions on the opened connections (if the Provider supports nested transactions ).
7. Use the Errors set to check the Errors returned by the data source.
Command object
Defines the commands executed on the data source. We can use the set, method, and attribute of the Command object to perform the following operations:
1. Use the CommandText attribute to define the executable text of a command (for example, an SQL statement.
2. Use the Parameter object and the Parameters set to define Parameters for parameterized queries or stored procedures.
3. Execute the command using the Execute method and return the Recordset object when appropriate.
4. Use the CommandType attribute to specify the command type before execution to optimize performance.
5. Use the CommandTimeout attribute to set the number of seconds to wait for command execution.
6. Associate the opened connection by setting the ActiveConnection attribute.
7. Set the Name attribute to identify Command as the method associated with the Connection object.
8. Send the Command object to the Source attribute of Recordset to obtain data.
ADO connection to EXCEL2003 table
Conn. open "provider = microsoft. jet. oledb.4.0; extended properties = excel 8.0; data source = "& file path Cnn. open "provider = microsoft. jet. oledb.4.0; Extended properties = 'excel 8.0; hdr = no'; data source = "& Path settings
"HDR = NO" indicates that the first line of the selected region is not the title. If this item is not set, the system uses the non-null record of the first line as the title line of the ADO record set by default.
Connect to the txt file using ADO
ActiveSheet. cells. clearmyText = "Student example .txt" myPath = ThisWorkbook. pathCnnStr = "Driver = {Microsoft Text Driver (*. txt ;*. csv)}; "&" DBQ = "& mypath' Cnn. open CnnStrrs. open myText, cnn, adopenkeset, and adLockOptimisticADO connect to the ACCESS2003 database strAccess = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ _ ThisWorkbook. path + "\ data \ database file. MDB "+ _"; Jet OLEDB: Database Password = "&" 123 "'defines the connection string data source Password (up to 20 characters) Set ADOcn = New ADODB. connection 'value variable is the object reference ADOcn. open straccess' Open connection
Connect to FOXPRO 6.0 database using ADO
Set conn = createobject ("adodb. connection ") set rs = createobject (" adodb. recordset ") SQL =" select * from aa. dbf "conn. open "driver = {microsoft visual foxpro driver}; sourcetype = dbf; excelusive = no; sourcedb =" & file path rs. open SQL, connmsgbox rs. recordcountrs. closeconn. closeconn = nothingrs = nothingOdbc driver of foxpro needs to be installed
Connecting to the database of SQL SERVER using ADO
Dim cnn1 As ADODB. ConnectionDim strCnn As String 'Open the connection. StrCnn = "Provider = sqloledb;" & _ "Data Source = srv; Initial Catalog = pubs; User Id = sa; Password =;" cnn1.Open strCnnADO connects to the con database of ACCESS2007. open "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "& Access2007 Data Source & _"; Jet OLEDB: Database Password = "& Password con. open "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "& Excel2007 Data Source & _"; Jet OLEDB: Database Password = "& Password
Connecting to the oracle database using ADO
Sub connect _ database () Dim strconnt As StringSet connt = New ADODB. connectionDim rs As ObjectSet rs = New ADODB. recordsetDim sevip, Db, user, pwd As String 'sets the server address and connected data, and logon user Password sevip = "*****" Db = "*****" user = "*****" pwd = "*****" strconnt = "DRIVER = {Microsoft ODBC for Oracle }; password = apps; User ID = apps; Data Source = demo "'connt. connectionString = strconntconnt. openEnd Sub
Connecting to the mysql database using ADO
Sub connect _ database () Dim strconnt As String strconnt = "" Set connt = New ADODB. connection Dim rs As Object Set rs = New ADODB. recordset Dim sevip, Db, user, pwd As String 'set server address, connected data, and logon user Password sevip = "192. 168. *. * "Db =" *** "user =" *** "pwd =" ***** "strconnt =" DRIVER = {MySql ODBC 3.51 Driver }; SERVER = "& sevip &"; Database = "& Db &"; Uid = "& user &"; Pwd = "& pwd &"; stmt = set names GBK "'connt. connectionString = strconnt connt. openEnd Sub