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.
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:
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
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
- Step 1: create the required Connection and Recordset objects
- Step 2: use the Connection object to connect to the database
- 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
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.