Hi
This is a problem that was annoying me for sometime, the fact this it is not easy to work from VB6 using SQL queries.
So I wrote this code that made life a lot easier for me. a function that will execute a query and return the result as a simple string, and another function to return an array of strings
First create a DSN with the required connection settings then use the following code
Public Function QR(t As String) As String
Dim dbA As ADODB.Connection
Dim rsA As ADODB.Recordset
Set dbA = New ADODB.Connection
Set rsA = New ADODB.Recordset
dbA.Open "DSN=access"
Dim csSql As String
csSql = t
Set rsA = dbA.Execute(csSql)
On Error Resume Next
If rsA.EOF = True And rsA.BOF = True Then
QR = ""
End If
'Get this to Array.
Dim nNoOfReords As Integer
arrreCordarray = rsA.GetRows
nNoOfReords = UBound(arrreCordarray, 2) + 1
QR = arrreCordarray(0, 0)
End Function
'forum.AngryByte.com
And another one to return an array of strings (for example from a query that requests a list of names)
Public Function QRarr(t As String) As String()
Dim dbA As ADODB.Connection
Dim rsA As ADODB.Recordset
Set dbA = New ADODB.Connection
Set rsA = New ADODB.Recordset
dbA.Open "DSN=access"
Dim csSql As String
csSql = t
Debug.Print csSql
Set rsA = dbA.Execute(csSql)
If rsA.EOF = True And rsA.BOF = True Then
ReDim QRarr(0) As String
End If
'Get this to Array.
Dim nNoOfReords As Integer
'Dim arrreCordarray()
If Not (rsA.BOF And rsA.EOF) Then
arrreCordarray = rsA.GetRows
nNoOfRecords = UBound(arrreCordarray, 2) + 1
Dim qarr() As String
Debug.Print nNoOfRecords
ReDim qarr(nNoOfRecords) As String
For xx = 0 To nNoOfRecords - 1
On Error Resume Next
qarr(xx) = arrreCordarray(0, xx)
Next
QRarr = qarr
End If
End Function
Thats all, I hope you'll find it useful.
