You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Monday, August 6, 2007

Building Database Connection String Programmatically

Situation:
In a few days from now, I will be doing a new Excel VBA project for a more-than-2-year-old client. The new project will require me to produce pivot-table reports from an external data source. This kind of requirement is usually easy because Excel PivotTable already supports external source of data. But the problem with this project, they are requiring me to come up with an Excel VBA program that be used to connect with different database platform. As I was thinking about the solution of this problem, I’ve posted to different Excel VBA forum to seek for help. No one has given me the complete answer but while I was reviewing their suggestions, I tried to put them together and came up with the correct solution. Truly forums are very helpful. Below is the sample source code of the database connection string builder that can provide different OLEDB connectivity programmatically:


Solution:


'PROCEDURE TO GET CONNECTION STRING USING DataLinks
Function GetConnectionString() As String
Dim objLink As New MSDASC.DataLinks
Dim strConnectionString As String
strConnectionString = ""
On Error GoTo LinkErr
strConnectionString = objLink.PromptNew
LinkErr:
GetConnectionString = strConnectionString
End Function




'PROCEDURE TO TEST THE FUNCTION ABOVE
Sub Test()
Dim strCN As String
strCN = GetConnectionString
If strCN <> "" Then
MsgBox strCN
End If
End Sub



The heart of the code is the GetConnectionString procedure. It uses the MSDASC.DataLinks object. But before you can use this code you need to load 2 object files from Microsoft. They are:

1.) Microsoft ActiveX Data Objects 2.5 Library (or later version)
2.) Microsoft OLE DB Service Component 1.0 Type Library


See: Referencing Objects

When MSDASC.DataLinks generates a connection string, it stores the value in strConnectionString variable and eventually, it is the value to be returned by the GetConnectionString function.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


1 comments:

assikilo aka ася said...

) Microsoft OLE DB Service Com
33b
ponent 1