Public Function to pull query

[sdohertyccb]

Can anyone help me with the following code, I am trying to write a Function that will execute a query and show the results. This is as far as I have been able to get with my limited knowledge, any help will be greatly appreciated...
In case you are wondering, I have verified that the connection is avaiable and works in the followign code

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> sql(<SPAN style="color:#00007F">ByVal</SPAN> query <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>****<SPAN style="color:#00007F">If</SPAN> (g_Conn <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN>) <SPAN style="color:#00007F">Then</SPAN><br>********<SPAN style="color:#00007F">Call</SPAN> getCon<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>****g_Sql = query<br>****<SPAN style="color:#00007F">Set</SPAN> g_RS = g_Conn.Execute(g_Sql, , adCmdText)<br> <br>****<SPAN style="color:#00007F">If</SPAN> g_RS.EOF = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>********g_RS.MoveFirst<br>********sql = g_RS.Fields(query)<br>****<SPAN style="color:#00007F">Else</SPAN><br>********sql = ""<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****g_RS.Close<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[xenou]

The issue comes down to what your function is "returning".

1) A query returns a recordset - but that could be 0, 1, or thousands of values.

2) Do you want to give your user a recordset, write values to the worksheet, select a single value from the recordset, etc.?

Alex

 

[james_lankford]

The issue comes down to what your function is "returning".

Alex is correct
and the way you have it , there is no need for your function to return anything
you already have
Set g_RS = g_Conn.Execute(g_Sql, , adCmdText)
so you already have a global recordset object that contains the result of your query

it looks as if you're trying to have your function tell you weather or not the Execute returned anything, and if it didn't, then the function should return ""

if that's what you intended maybe try something like this (I haven't tested this, I'm just doing it from memory, but it should work)

Public Function sql(ByVal query As String) As Boolean
  
    If (g_Conn Is Nothing) Then
        Call getCon
    End If
    
    g_sql = query
    
    If Not g_RS Is Nothing Then
        If g_RS.State = adStateOpen Then
            g_RS.Close
        End If
        Set g_RS = Nothing
    End If
    
    Set g_RS = g_Conn.Execute(g_sql, , adCmdText)
    
    If g_RS.BOF And g_RS.EOF Then
        sql = False
        g_RS.Close
        set g_RS = nothing
    Else
        sql = True
    End If
    
End Function

and then if the function returns true, you can access the results through g_RS

 

[sdohertyccb]

Thank you for your help on this. I 'm sorry I am late in getting back to you, but I have been out of the office.
What I expect is for the results of the query to drop into the spreadsheet, and if it returns nothing then exit. My issue is I am not getting the results to populate the spreadsheet, is there some line that I am missing?
Again, thanks for all of your help on this...

 

[james_lankford]

http://www.mrexcel.com/forum/showthread.php?t=374803

look at the post by Sydney
I think it might help
it won't be an exact match for what you want, but I think it'll help

post again with more questions if it doesn't

 

[sdohertyccb]

James, THanks for your help...
I mdified the post and it worked great, I had already been on a similar track but this helped me refine a bit of code.
I truly appreciate yoru help.
Have a great weekend!