ODBC Connection VBA Code
ODBC Connection VBA Code
There are two common tasks associated with ODBC Connections in Excel:
This wiki will address both of them. The examples here were generated by MS-Query connecting to an Oracle database. Similar techniques can be used when connecting to other database types. Obviously the code cannot be exercised here since the database is not available to the wiki.
Although the code is broken up into two sections in this wiki, it is all in one module with the followign defined as global variables.
Dim User_ID As String
Dim User_Password As String
Changing The SQL Code
Very often, the SQL code for an ODBC connection remains the same for each run of the query with the exception of a parameter, usually a date.
In some cases, MS-Query will allow you to pass a parameter to the query at runtime. If the query is too complicated to display graphically, then you cannot pass the parameter at runtime. In this case, the approach is to rewrite the entire query just before runtime.
This is done by putting the query into an Excel table and using the SUBSTITUTE command to substitute the parameters for the parameter token. The SQL tab in the attached workbook shows how this is done.
The “raw” SQL is maintained in the column Raw SQL. I use a table so that the range expands and collapses appropriately if I have to modify the query and the formulas get copied over automatically.
The Converted SQL column has the formula: =TRIM(SUBSTITUTE(A3,"&Date",LaunchDate)) where LaunchDate is a defined named range ='Control Panel'!$C$5.
This column is concatenated into one big string in Cell B1 on the SQL page using the UDF SuperCat =SuperCat(Table_SQL[Converted SQL])
Cell B1 is given the name “MySQL.”
The newly-manufactured SQL is then placed into the command text using the following code:
Dim ConnectionName As String
' You need to do the following lines for each connection you have in the workbook
ConnectionName = "<Connection Name>"
' Call Change_Connection with Connection Name, User ID and Password here if needed
' MySQL is the "manufactured" SQL Code.
ActiveWorkbook.Connections(ConnectionName).ODBCConnection.CommandText = Range("MySQL")
Note that dates are not the only things that can be substituted using this technique. I have used SuperCat against another table containing model names for use in an IN statement in a WHERE clause. (The SuperCat was on a row in the Raw SQL column).
As long as you remain in the same database, you could clone one report, substitute and entirely new SQL statement and make an entirely new report.
Changing User Credentials
The Data Connection Manager allows you to save credentials in ODBC Connection String. Unfortunately, the user ID and password are not saved encrypted; they are available for all to see in clear text.
It can be annoying for a user to enter in credentials each time a query is refreshed in a multiple query report. I have some reports that run as many as 6 queries. This means that the end user has to babysit the process to enter in the credentials. For queries that take a while to run, this is a waste of time.
The code presented here gets user credential information once. Then the user can press the Refresh Data button that runs all the SubSQL commands and go off for a coffee break.
When the user closes the workbook, the credential information is erased.
' ********** Get User Credentials **********
' Code for the Get Credentials button
User_ID = InputBox("Enter User Name", "User Name")
User_Password = InputBox("Enter Password", "Password")
MsgBox "User Name: " & User_ID & Chr(10) & _
"Password: " & User_Password & Chr(10) & Chr(10) & _
"If these are not correct, rerun the application", vbOKOnly, "Confirmation"
'********** Change User Credentials **********
Sub Change_Connection(ConnectionName As String, MyUID As String, MyPassword As String)
Dim k As Long, nElements As Long
Dim Connection_Array() As String
ActiveWorkbook.Connections(ConnectionName).ODBCConnection.SavePassword = True
' Break the connection string into its constituent parts
Connection_Array = Split(ActiveWorkbook.Connections(ConnectionName).ODBCConnection.Connection, ";")
nElements = UBound(Connection_Array)
' Assign the user name and password
Connection_Array(2) = "UID=" & MyUID
Connection_Array(3) = "PWD=" & MyPassword
' Put the connection string back together
ActiveWorkbook.Connections(ConnectionName).ODBCConnection.Connection = Join(Connection_Array, ";")
' ********** Erase User Credentials **********
' Call this function in the close workbook event. <- ******** IMPORTANT *******
Dim MyConnection As WorkbookConnection
For k = 1 To ThisWorkbook.Connections.Count
Set MyConnection = ThisWorkbook.Connections(k)
MyConnection.ODBCConnection.SavePassword = False
Checking It Out
The spreadsheet is not connected to a database available to the wiki. However, you can use the code shown below. When you first open the spreadsheet, go to Data -> Connections -> Properties and look at the Connection String for the connections. You should not see a password.
Then click on the Set Credentials and enter in any set of credentials
Then run the test_it subroutine and check the Connection String again. This time it will show the new user ID and password.
Close the book and reopen it. The only thing that should show is the user name. The password will be blank.
‘ These are the two named connections in this workbook
Change_Connection "qry_name", User_ID, User_Password
Change_Connection "WP02 Netace Codes", User_ID, User_Password
|This page has been accessed 6,564 times. This page was last modified 18:19, 18 October 2013 by dflak. Disclaimers|