function to call an ODBC query...

[N Ramsay]

Hi,

I have an Import Data query set up (ODBC connection saved as a.dqy
file) which returns two columns from a SQL d/b.

What I normally do is run this and then use the results (big 2-column
list) as the basis of a vlookup table.

What I would prefer to do is create a custom function which calls the
ODBC connection.

The function would pass in one parameter and return the result of a
vlookup done on the big list generated by the ODBC query.

Is it possible to code this in VBA?

Thanks in advance.

Neil.

 

[Goofy]

yes


public function myLookup( clause as string ) as variant

dim sqlString as string

sqlString = "SELECT * FROM myTable " + clause ' IE Where Customer='Londis'

'....... Your code for accessing the server

myLookup = myResult

end function

 

[N Ramsay]

yes


public function myLookup( clause as string ) as variant

dim sqlString as string

sqlString = "SELECT * FROM myTable " + clause ' IE Where Customer='Londis'

'....... Your code for accessing the server

myLookup = myResult

end function

Hi Goofy,

Thanks for the quick response.

I can write the SQL query and ODBC connections easily enough, but my
VBA skills are very poor.

Can you please explain what "myResult" refers to in the above, I don't
see it being defined.

I see the sqlString being defined, but not actually being processed..
am I missing the point?

Thanks Again,

Neil.

 

[Goofy]

myResult is whatever you want the funtion to return from your query or as a
result of your query

sqlString is representative of a string you want to send to the server IE
the query itself.

I left the ADODB coding to you.

 

[N Ramsay]

OK - here's what I've got so far:-

_____
Public Function IPLookup(ipadd As String) As Variant

Dim sqlString As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

sqlString = "SELECT <valid stuff> FROM <valid stuff> WHERE <valid
stuff>='" & ipadd & "' "

objConn.ConnectionString =
"ODBC;DSN=blah;UID=blah;PWD=blah;APP=Microsoft Office
2003;WSID=blah;DATABASE=blah;"

objConn.Open

Set objRS = New ADODB.Recordset
Set objRS = objConn.Execute(sqlString)

IPLookup = objRS(0).Value

'Release the recordset and connection
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing

End Function
____

this just returns #Value for input parameters i know are correct.

is thtis a type-mismatch issue, or am i missing something else?

Thanks,

Neil.