Introduction to database connections in Excel for efficient risk control - Part 2
Part 2: Creating dynamic queries in Excel with VBA
In the last article, we introduced how to use the EXCEL interface to connect EXCEL with SQL, to make general table/pivot table and charts. In this article, we will introduce another connection method with EXCEL VBA. Similar as the approach mentioned in our previous article, an analyst can use this technique to allow other users without SQL knowledge to access the required information from database. The advantage of this “dynamic queries” approach is that the SQL query can be parametrized by user input from Excel, which allows us to provide specific information that the user requests.
In this article, we raise a simple example – creation of Excel spreadsheet that automatically pulls the contact information of selected customer ( customer’s name and phone number, phone type, name and relationship of the corresponding contact person) from the database. This is a typical task used in daily practice to support the work of call center operators in case that the desired data cannot be obtained from existing IT system. For example, this approach can be used to provide some additional contacts from database to be used by skip tracing team to find the customers who are difficult to be reached.
Here, we use EXCEL2010 and SQL Server 2014 Management Studio as the example. There might be some small differences between different versions.
STEP1: Prepare the SQL table
It is easy for analyst to prepare the table in SQL, so we skip the process.
In our example, the SQL table is named ‘contact1’. We have 6 columns: customer_name, ID_Card, contact_name, relationship, phone_number, phone_type.
STEP2: Design EXCEL form for search
1. Add the developer to tool bar
We will use developer several times for our example, so let’s add it to tool bar first:
Click Office button -> EXCEL options -> Customized ribbon -> Developer -> Add
2. Design the table & add the ‘search’ button
For our example, we design a table as below:
We can see in the screen cut, for the pink part, we can enter a given condition for search; for the green part, we will get the result. We can add ‘search’ button as below:
STEP3: Connect database with VBA
1. Open VBA editor
Method 1: Alt + F11 (shortcut)
Method 2: (EXCEL tool bar) Developer -> Visual Basic:
2. Add reference
Before we write some VBA code, we need to add the reference. Reference (function) contains libraries that provide basic rule to facilitate VBA running processes.
For different objects we used in our VBA code, we should add different reference. For example, in our example, we use ADODB connection, so we should add corresponding ADODB library. Otherwise, we will get a wrong message when we run the VBA code because system doesn’t know how to deal with it.
(Tool bar) Tool -> Reference
For example, for Microsoft Active-X Data Objects, we have so many libraries below:
In our example, we add these references:
3. Write VBA code according to the goal
Here user can enter a given id card number, click ‘Search’, and get the customer’s name in B3, and corresponding name, relationship,phone number, phone type of contact person.
For example, here we want to find the list of information for id ‘451029********5219’ (for customer’s privacy, we cover 8 digits):
Next is to create VBA code. Before we show the code, let’s explain the logic first. One important thing is to connect excel to SQL server. In this article, we use ADODB connection. First we need to declare two objects: ADODB connection (to connect the data) and ADODB recordset (to save the target table); Then we create connection to database and open the connection of the record table; then use loop to write the result to EXCEL; after finish the work, we should close the record.
Below is our VBA code and its corresponding explanation:
Sub newbook()
'close screen updating: improve running efficiency
Application.ScreenUpdating = False
'clean results of the last search
Cells(3, 2).Clear
Range("A9:J200").Clear
'declare connection
Dim cn As New ADODB.Connection
' declare recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
' declare variables
Dim strCn As String, strSQL1 As String, strSQL2 As String
' declare worksheet
Dim sht As Worksheet
'assign worksheet
Set sht = ThisWorkbook.Worksheets("sheet3")
'get condition from given cell
id_card = sht.Cells(2, 2)
'create connection to database
strCn = "Provider=SQLOLEDB;Data Source=111.111.111.111\MYDB;Initial Catalog=Model_data;UID=DP;Pwd=12345678"
'Provider=SQLOLEDB: depends on which type of database and type of connection drivers your computer provided. For more knowledge, you can visit www.connectionstrings.com
‘data source, initial catalog, UID, Pwd: server name, database, user name, and password of SQL server
’connect/open the database with specific condition
strSQL1 = "select distinct contact_name,relationship,phone_number,phone_type from contact_table where id_card='" &id_card& "'"
strSQL2 = "select distinct customer_name from contact_table where id_card='" &id_card& "'"
cn.OpenstrCn
’record database
rs1.Open strSQL1, cn
rs2.Open strSQL2, cn
'write the results into given cells
sht.Cells(3, 2) = rs2("customer_name")
i = 9
Do While Not rs1.EOF
sht.Cells(i, 1) = rs1("contact_name")
sht.Cells(i, 2) = rs1("relationship")
sht.Cells(i, 3) = rs1("phone_number")
sht.Cells(i, 4) = rs1("phone_type")
rs1.MoveNext
i = i + 1
Loop
rs1.Close
'close screen updating: update the result in EXCEL
Application.ScreenUpdating = True
End Sub
4. Connect‘Search’ button with macro
5. Get the searching result
By just one click on ‘Search’ button, we get final result as below:
STEP4: The safety questions
There still remains one subtle thing. It is security. Inside the VBA-code, we wrote a password to access to database, which means that anyone who can open VBA editor and have a little understanding of code can steal the password. So for data safety, it is better to do following :
Create special database account for this application – this account must have rights just for reading and have access to limited list of tables (you can ask your databases administrators to provide account like this);
Don’t write the password in VBA-code. It means that after the start of the macro, system will ask user to put the password in a special window. And for this purpose, we need to provide password to all users of this excel tool.
But step number 2 above still allows the user with certain skills to get more information than allowed by this application (because the user knows the password). And the user can view all the data in tables available to him/her, not only the data that he/she is allowed to view by the application. In our example, we want to allow the users to view contact data of selected customers only one by one, but using the password and SQL skills, they could get the complete list of customer contact data. So we need to somehow control all users, how they use the password. But usually, it is very complicated and sometimes just impossible.
To solve this problem, we can write password inside the VBA-code, but hide all the VBA-code in our Excel-workbook from users by the safety password:
Open VBA editor -> VBA Project Properties -> protection -> checkbox, set the password -> Save
After this, anyone who wants to visit or change the VBA code need to enter the password. So, this approach allows us not to give password for database to all users. And we are also protected from the fact that someone can get the password from the VBA code
Wrap-Up
VBA is a powerful add-in in EXCEL. In this article, we use it to connect excel with SQL Server. Similar approach can be used to connect other types of database (such as Oracle) via ODBC interface. You can use this approach for solving this kind of tasks that require a user without SQL knowledge (such as call center team leader) to pull required data from database based on input parameters.
