function to call an ODBC query...
 

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.

function to call an ODBC query...
 

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" wrote in message
ps.com...
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.

function to call an ODBC query...
 

Goofy wrote:
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.

function to call an ODBC query...
 

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" wrote in message
ps.com...

Goofy wrote:
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.

function to call an ODBC query...
 

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.