sql interface

[homebuilder]

Two part question:
1) is it possible to use sql to search a database in a similar manner to the VLOOKUP command (i enter data into a cell and it spits out specific data into another cell)?
2) how?

[dk]

Hello,

Here's a function I posted a little while ago which carries out a similar function to VLOOKUP. Before using it you'll need to go into Tools, References and choose Microsoft ActiveX Data Objects 2.n Library (where n will depend on the version of ADO installed on your machine).

The function takes 4 arguments:-

TableName - name of the table in your Access DB.
LookupFieldName - the name of the Field whose values you want to search.
LookupValue - the value you want to search for in LookupFieldName
ReturnField - the name of the field whose value you want to return.

E.g. if you have a table called tblAccountMapping (which lists account numbers, account names, etc) which contains various fields including AccountNumber and AccountDescription. You want to return the account description of the account number in cell A1.

=DBVLookUp("tblAccountMapping","AccountNumber",A1,"AccountDescription")

Here's the code:-

Dim adoCN As ADODB.Connection
Dim strSQL As String
    
Public Function DBVLookUp(TableName As String, LookUpFieldName As String, LookupValue As String, ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & _
         "='" & LookupValue & "';"    ' If lookup value is a number then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
DBVLookUp = adoRS.Fields(ReturnField).Value
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler

Set adoCN = New Connection

adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
adoCN.ConnectionString = "K:financemanagement reportingfinance reporting 2002.mdb" 'Change to your DB path
adoCN.Open
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"

End Sub

HTH,
Dan

[homebuilder]

That looks great except that I'm not looking in Access, it's an external database. And I don't have the references sub-menu in tools.
Also (I'm very new to VBA) where does the code get entered and how is it then linked to a specific cell?

[dk]

Hi,

Sorry, I don't know why I assumed it was an Access database. First of all, the code goes in a standard module :-

Open the VB Editor (Alt+F11)
Click Insert, Module
Paste the code into the module

The function can then be used just like any other function such as SUM.

Now, onto the second problem. What sort of database are you querying? The ActiveX Data Objects (which is what this code uses) was designed to allow a connection to all sorts of databases and still use the same sort of code. These are the lines which you'll need to change:-

adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
adoCN.ConnectionString = "K:\finance\management reporting\finance reporting 2002.mdb" 'Change to your DB path

Provider describes the type of database you want to connect to.

ConnectionString is the path to the database (either on a server or on a normal network folder).

If you let me know the exact type of database I may be able to help.

Regards,
Dan

[jkpd2000]

Hi DK,

I am also facing problem to connect to oracle. could you help.



[ This Message was edited by: jkpd2000 on 2002-05-15 13:19 ]

[sen_edp]

Hi Jkpd

How are you trying to connect to oracle?
through odbc or ado?

[jkpd2000]

odbc

[sen_edp]

hello again

What is the problem, in detail please !

[jkpd2000]

it is saying getting connected but never ending

[dk]

Hi,

Sorry, but my experience of connecting to databases other than Access is limited but bear with me. The way I understand it is that the code will work with many databases including Oracle. The only part that needs to be change is the Provider and the ConnectionString. This link may help:-

http://www.topxml.com/conference/wro...c/text/dov.asp

I'd like to know if you manage to get a connection so please post back if you're successful.

Regards,
Dan