preface

Needless to say, the importance of database. Slightly formal or complex applications need to manipulate the database to read and write data. Of course, relational databases are mainly mentioned here. Common databases include MS Access (yes, access is a desktop relational database), MS SQL Server, IBM DB2, Oracle, etc.
There are many connection modes, such as ODBC, ADODB and so on.
It sounds very complicated, but actually connecting to the database in VBA is very simple. Here we introduce the relatively clear structure of ADODB, which is actually universal and the same usage in other languages.

ADODB

There are a lot of information about the conceptual things of ADODB on the Internet, so I won't introduce it much. Here we focus on the use of.

Preparation before ADODB use

As described many times before, for various objects, the easiest way for beginners is to Reference it in the Reference first. Select "Microsoft ActiveX Data Objects Library". There are many versions. Don't worry too much. For daily simple applications, whichever version is the same.

Two core concepts of ADODB

For ADODB, there are two very important objects:

• Connection
  As the name suggests, the Connection object establishes and maintains a Connection to the target database. To use a database, the first thing to do is "connect" to the database
• Recordset
  Usually, the purpose of connecting to the database is to read data or write data. Data acquisition, that is, "record", is accessed through Recordset.

Of course, there are other important objects such as Command and Error, but for ordinary small projects, learning to use Connection and Recordset is enough.

ADODB three steps for reading data

1. Step 1: create the required Connection and Recordset objects
2. Step 2: use the Connection object to connect to the database
3. Step 3: use Recordset object to get data

Sub Test()
  'First step: create object
    Dim objConnection As New ADODB.Connection
    Dim objRecordset As New ADODB.Recordset
    
    'Step 2: Connect to database,One time job
    With objConnection      'Set parameters
        .CursorLocation = adUseClient
        .ConnectionTimeout = 120
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=<myDataSource>;Network Library=DBMSSOCN;Initial Catalog=<Initial Catalog>;User ID=<User Name>;Password=<Password>"
        .Open
    End With
    
    'Step 3: Read data, Suppose there is a table called User_Table
    'The following settings are one-time
    With objRecordset
        .ActiveConnection = objConnection
        .CursorLocation = adUseClient
        .LockType = 3
        .CursorType = 1
    End With
    
    'The following code needs to be used once every time the data is read
    If objRecordset.State = 1 Then objRecordset.Close  'If Recordset Object is already in open State, close it
    objRecordset.Open ("SELECT * FROM User_Table")
End Sub

Connection string

In the whole process, the most important is the connection string. The With statement is used in the above example, or the Provider can be directly written into the connection string without using With.

objConnection.Open("Provider = "SQLOLEDB"; Data Source=<myDataSource>;Network Library=DBMSSOCN;Initial Catalog=<Initial Catalog>;User ID=<User Name>;Password=<Password>")

Here, the parameter in the Open() method is the connection string. Obviously, it consists of several fixed parts.
Provider is the most important. Different databases have different providers:

• SQL Server: SQLOLEDB
• AS400: IBMDA400.DataSource.1
• Ms Access / Excel: Microsoft.ACE.OLEDB.12.0

To connect Access and Excel, you usually only need to provide Provider and Data Source. (if the Excel connection fails, you can try to provide one more Excel version parameter "Extended Properties=Excel 12.0;")

'Access
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test.accdb" )
'Excel, be careful Data Source Full path and file name are required
objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; Data Source= C:\Temp\Test.xlsx")

Connect to Excel and use the worksheet as a special matter of data source

• If you connect to the workbook run by VBA, you can use ThisWorkbook.FullName to obtain the full path and file name:

  objConnection.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; Data Source= " & ThisWorkbook.FullName)
  

• An Excel can have multiple worksheet s, which is equivalent to multiple tables in the database. Enclose the data table name and column range with []. In the following example, Sheet2 is used as the data source from column B to column C, and rows 2 to 30. Note that the first line will be considered the title line

  objRecordset.Open ("SELECT * FROM[Sheet2$B2:C30]")
  

  If the entire worksheet is used as the data source, there is no need to specify row and column information

  objRecordset.Open ("SELECT * FROM[Sheet2$]")
  

  Empty rows will not be counted. In the above example, although B2:C30 is queried, there are only 3 rows of valid data and only 3 rows of returned data.