Excel Lookup against the sql table

My working excel vba function:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)
On Error GoTo Error:
SQLookup = “Error”
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

Dim connString As String
connString = “driver={SQL Server};” & _
“server=server_name;Trusted Connect=Yes;database=database_name”
Conn.ConnectionString = connString
Conn.Open

‘ If Conn.State = adStateOpen Then
‘ MsgBox “Welcome to Pubs!”
‘ Else
‘ MsgBox “Sorry. No Pubs today.”
‘ End If

Set RecSet = New Recordset

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” = ‘” & LkUpValue & “‘”, connString

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit Function
Error: SQLookup = Err.Number & “|” & Err.Source & “|” & Err.Description
End Function

My working calling the excel vba function:

=SQLOOKUP(B2 (ExcelLookupFieldName), “LookupSqlColumnName”, “dbo.TableName”, “ReturnSqlColumnName”,$E$3)

Note: $E$3 is the connection string is not being used from the cell E3, it is embeded in the funciton.

Lookup against External Database

At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells.  I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.

While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.

External data in Excel

To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.

The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).

For this function to work, a reference to “Microsoft ActiveX Data Objects x.x Library ” must be made in VBA (under Tools Menu/References).

Macro References

The code for the function is as follows:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

SQLookup = “Error”

On Error GoTo Exit_Func

SQLookup = “No Conn”
Conn.Open ConnStr

Set RecSet = New Recordset

SQLookup = “Error”

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” like ‘” & LkUpValue & “‘”, ConnStr, , , adCmdText

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit_Func:

End Function

As an illustration of this function, let us assume that we have an SQL database named FRUITS containing a table tblFRUIT with the following entries:

Fruit Table

An example of the connection string to this database would be as follows:

“DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits”

A good reference for conneciton strings is this site.

To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP(“Grapes”,”Fruit”,”tblFRUIT”,”Quantity”,A1). The cell A1 contains the connection string.

This function looks for the field “Grapes” under the field “Fruit” from the database table and returns the corresponding value under the field “Quantity”, which is 31.

Like all Excel functions, the various parameters can come from other cells, see example below.

SQLookup Example

Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).

A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.

Advertisements

SQL Error: Agent job error DTExec: The package execution returned DTSER_FAILURE (1)

Scenario

I was running SSIS packages which is available only in 32 bit on 64 bit server and I was receiving the error

Message
Executed as user: SHELTER\SSIS_Admin. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:33:53 Error: 2015-12-03 19:29:56.44 Code: 0x00000001 Source: create file Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:33:53 Finished: 19:29:56 Elapsed: 14162.4 seconds. The package execution failed. The step failed.

Solution

a

In order to access the network shared folder, I had to add the sql_proxy user which was domain user as a user in that server (the server that has shared the folder)

jQuery: Remove class from siblings

The issue is that siblings works for elements under the same parent. Your a links are not under the same parent since each one is wrapped in a h3.
So I believe this is what you want
$(document).ready(function(){

$
(".accordion h3:first").addClass("active");
$
(".accordion a:first").addClass("active");
$
(".accordion p:not(:first)").hide();

$
(".accordion h3").click(function() {
$
(this)
.addClass('active') //set the current as active
.siblings("h3") //find sibling h3 elements
.removeClass("active") // and remove the active from them
.end() // return selection to the current element
.next("p") // find the next p
.slideDown("slow") // and show it
.siblings("p:visible") // find the other visible p elements
.slideUp("slow"); // and hide them

$
(this)
.find('a') // find a under current element
.addClass('active') // and add active class
.end() // return to current element
.siblings('h3') // find sibling h3 elements
.find('a') // find their a elements
.removeClass('active'); // and remove the active
});

});