Two ways to use ADO for "VBA research"

Iamlaosong

1. Quoting method
Referencing ADO related components: Open the VBA Editor and click "Tools"-"References" in the menu. Make sure that the Microsoft Activitex data Objects 2.8 Library and the Microsoft Activitex data Objects Recordset 2.8 library are ticked. After the reference, then declare:

Dim CNN as New Connection ' declare linked object
Dim rst as New Recordset ' declares Recordset object

Here are the routines:

    Dim cnn As New Connection Dim rst As New Recordset CNN. Open "Provider=msdaora.1;data source=dl580; User ID=EMSSXJK;    PASSWORD=EMSSXJK; " Oraopen = True ' After successful execution, the database is opened Sqls = "SELECT count (*) from TB_EVT_DLV where mail_num= '" & Emsid & "'" Se T rst = CNN. Execute (SQLS) If rst (0) > 0 Then sqls = "Select B.zj_code,b.zj_mc,b.jgfl,b.city,b.ssxs from TB_EVT_DLV A, TB _JG B "Sqls = Sqls &" Where A.dlv_bureau_org_code = B.zj_code and a.mail_num= ' "& Emsid &" ' and Rownu M=1 "Set rst = CNN. Execute (sqls) Sqls = "CopyFromRecordset" ' MaxRow = Sheets (QFXX). [A65536]. End (Xlup). Row ' If maxrow > 1 then Sheets (QFXX). Range ("A2:h" & MaxRow). ClearContents Cells (Row1, Pos_sav).  CopyFromRecordset rst Else sqls = "Select B.zj_code,b.zj_mc,b.jgfl,b.city,b.ssxs from Tb_evt_mail_clct A, TB_JG B "Sqls = Sqls &" Where A.clct_bureau_org_code = B.zj_code and a.mail_num= ' "& Emsid &" ' andRownum=1 "Set rst = CNN. Execute (sqls) Sqls = "CopyFromRecordset" ' MaxRow = Sheets (QFXX). [A65536]. End (Xlup). Row ' If maxrow > 1 then Sheets (QFXX). Range ("A2:h" & MaxRow). ClearContents Cells (Row1, Pos_sav + 5). CopyFromRecordset rst End If

2. Creation method
You do not need to reference ADO-related components and use the CreateObject function directly to create ADO objects, namely:
Set cnn = CreateObject ("Adodb.connection") ' Create ADO Object
Set rst = CreateObject ("Adodb.recordset") ' Create record set

The following is a routine (similar to the previous routine, with the first half being different, followed by the same):

    Dim CNN As Object, rst as Object        Set cnn = CreateObject ("ADODB. Connection ")    Set rst = CreateObject (" ADODB. Recordset ")    CNN. Open "Provider=msdaora.1;data source=dl580; User ID=EMSSXJK; PASSWORD=EMSSXJK; "    Oraopen = True ' The database is opened after successful execution

The use of other components is similar to this, it is recommended to use the creation method, so that you do not have to control the "reference" in the settings, such as:

Dim dic as Object ' direct creation does not require reference
Set dic = CreateObject ("Scripting.Dictionary") ' Create Dictionary Object


Dim FSO as Object ' direct creation does not require reference
Set fso = CreateObject ("Scripting.FileSystemObject") ' Create file object model

Two ways to use ADO for "VBA research"