this the VB version of DBLOOKUP and DBCOLUMN:
Function SQL_GETTABLEDATA(ByVal f_Table As String, ByVal f_RetCol As String, ByVal f_KeyCol As String, ByVal f_KeyVal As String, ByVal b_KeyNumeric As Boolean, Optional bDISTINCT As Boolean = True) As String
On Error GoTo errSQL_GetTableData
SQL_GETTABLEDATA = ""
Set SQL_Res_Temp = Nothing
Set SQL_Res_Temp = New ADODB.Recordset
SQL_Res_Temp.CursorLocation = adUseClient
If f_KeyCol <> "" And f_KeyVal <> "" Then
's_SQL = "Select " & IIf(bDISTINCT, "DISTINCT", "") & " [" & f_Table & "].[" & f_RetCol & "] from [" & f_Table & _
"] Where cstr([" & f_Table & "].[" & f_KeyCol & "])=" & _
IIf(b_KeyNumeric, "", "'") & cADO.ValidateStringForSQL(f_KeyVal) & _
IIf(b_KeyNumeric, "", "'") & ";"
s_SQL = "Select " & IIf(bDISTINCT, "DISTINCT", "") & " [" & f_Table & "].[" & f_RetCol & "] from [" & _
f_Table & "] Where CONVERT(VARCHAR(255),[" & f_Table & "].[" & f_KeyCol & "])=" & _
IIf(b_KeyNumeric, "", "'") & cADO.ValidateStringForSQL(f_KeyVal) & _
IIf(b_KeyNumeric, "", "'") & ";"
Else
s_SQL = "Select " & IIf(bDISTINCT, "DISTINCT", "") & " [" & f_Table & "].[" & f_RetCol & "] from [" & f_Table & _
"];"
End If
SQL_ErrInit
If cADO.ExecuteSQL(s_SQL, , , , , SQLRecAff, SQLRecCnt, SQLErrNum, SQLErrDesc, SQL_Res_Temp) = False Then
GoTo doneSQL_GetTableData
End If
With SQL_Res_Temp
If SQLRecCnt < 1 Then GoTo doneSQL_GetTableData
SQL_GETTABLEDATA = IsNullEx(SQL_Res_Temp.Fields(0).Value, "")
End With
doneSQL_GetTableData:
If Not (SQL_Res_Temp Is Nothing) Then
If SQL_Res_Temp.State = 1 Then SQL_Res_Temp.Close
End If
Set SQL_Res_Temp = Nothing
Exit Function
errSQL_GetTableData:
SQL_GETTABLEDATA = ""
Err.Clear
Resume doneSQL_GetTableData
End Function
