Connect Excel VBA to Access Database
Using Excel Macros (VBA) you can connect to any DB such as SQL, Oracle, Access DB. So in this article we will learn how to connect to an Access DB. For Access 2007/2010 Database, Provider is: Provider = Microsoft.ACE.OLEDB.12.0 First, you must Add reference for ADO DB Connection as follows:
- Open VB Editor (Alt + F11).
- Tools -> References …
- Select “Microsoft ActiveX Data Objects 2.0 Library”. You can choose version 2.0 or any higher version.
- Click OK
Connect to Access 2007/2010 Database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | Option Explicit Public Const dbName = "student.accdb" Sub ADODB_Connect() Dim dbPath As String Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strConn As String Dim query As String On Error GoTo ErrorProcess ' create dpPath from current folder and dbName dbPath = Application.ActiveWorkbook.Path & "" & dbName ' information to connect to 2007/2010 AccessDB strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & dbPath & ";" & _ "User Id=admin;Password=" ' open connection conn.Open (strConn) ' define query query = "SELECT * FROM student" ' execute the query rs.Open query, conn, adOpenKeyset ' show number of records MsgBox rs.RecordCount ' show data from AccessDB Do Until rs.EOF MsgBox rs.Fields.Item("name") & ", " & rs.Fields.Item("age") rs.MoveNext Loop GoTo EndSub ErrorProcess: MsgBox Err.Number & ": " & Err.Description EndSub: Set rs = Nothing Set conn = Nothing End Sub |
USING SQL TO FILTER OR ACCESS DATA IN THE Spreadsheet
After determining how our data table, we can use SQL to access this data with the following SQL statements:
ACCESS DATA FROM EXCEL SHEETS WITH SQL
- Accessing all data in a spreadsheet
SELECT * FROM [A1:G20000]The * in this statement means we want the data of all the columns. If you use the Excel file in the video to execute these SQL statements, you need to add mi_sql to the front of the statement: mi_sql SELECT * FROM [A1: G20000] - Accessing data for certain columns: we write the column name after the SELECT keyword and write the columns separated by commas,
1 2 | SELECT OrderDate, Region, Rep, Item FROM [A1:G20000] |
FILTER EXCEL DATA SHEET WITH SQL
- Filter data from Excel spreadsheet with one condition: Filter data of orders from Hanoi
1 2 3 | SELECT * FROM [A1:G20000] WHERE Region='Hanoi' |
The above SQL statement means: from the data area A1: G20000, filter out the rows where the Region column has a value of Hanoi
- Filter data from Excel spreadsheet with many conditions: Filter data of orders from Hanoi or Danang
1 2 3 4 | SELECT * FROM [A1:G20000] WHERE Region='Hanoi' OR Region='Danang' |
In addition to using the keyword OR, we can also use the keyword IN and the SQL statement will be as follows:
1 2 3 4 | SELECT * FROM [A1:G20000] WHERE Region IN ('Hanoi','Danang') |
- Filter the data of sales orders with unit cost less than or equal to $ 8.99
1 2 3 | SELECT * FROM [A1:G20000] WHERE [Unit Cost] <= 8.99 |
Note: We can see in this example that the column name of this worksheet is “Unit Cost” with spaces in it. It is best to avoid error when querying data, we should not write column names with spaces or special characters (that is, do not write column names in Vietnamese with accents). {. : notice}
- Filter the data of sales orders that have a unit cost of less than or equal to $ 8.99 sorted from the most expensive to the cheapest
1 2 3 4 | SELECT * FROM [A1:G20000] WHERE [Unit Cost] <= 8.99 ORDER BY [Unit Cost] DESC |
In this example, we can replace [Unit Cost] in ORDER BY [Unit Cost] DESC with another column. If the data type of a column is a string, that column will be sorted from Z to A, if the data type of a column is numeric then that column will be sorted smaller.
There are many other SQL hope after this article, you can learn more.