mdmackillop
09-17-2009, 04:13 AM
The following code works, but I need to refine it so I can query the original RecordSet (Filtr) from other routines. What is the syntax to use the desired code in the Load routine, and apply the additional filter from within the Test sub.
This is for a VB standalone application.
Option Explicit
Option Compare Text
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim Nm, Fn
Dim Nms As String
Dim objConnection
Dim filtr, filt2
Dim strPathtoTextFile
Dim MyArray()
Private Sub Form_Load()
ReDim MyArray(3, 500)
Set objConnection = CreateObject("ADODB.Connection")
Set filtr = CreateObject("ADODB.Recordset")
strPathtoTextFile = "S:\Database\"
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
'This works
filtr.Open "SELECT FirstName, LastName, Company,BusinessPhone, MobilePhone FROM Outlook.csv where LastName = 'Brown'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Desired
'filtr.Open "SELECT FirstName, LastName, Company,BusinessPhone, MobilePhone FROM Outlook.csv", _
' objConnection, adOpenStatic, adLockOptimistic, adCmdText
Call Test
End Sub
Sub Test()
Dim i As Long, j As Long, k As Long
i = -1
List1.Clear
List2.Clear
List3.Clear
List4.Clear
'**********Help needed here*********
'Desired
'Filt2 = Select from Filtr WHERE LastName = 'Brown'"
'********************************
'then change filtr to filt2 below
Do Until filtr.EOF
Nm = filtr.Fields.Item("LastName")
Fn = filtr.Fields.Item("FirstName")
i = i + 1
Nms = Nm & ", " & filtr.Fields.Item("FirstName")
MyArray(0, i) = Nms & " "
MyArray(1, i) = filtr.Fields.Item("Company") & " "
MyArray(2, i) = filtr.Fields.Item("BusinessPhone") & " "
MyArray(3, i) = filtr.Fields.Item("MobilePhone") & " "
filtr.MoveNext
Loop
ReDim Preserve MyArray(3, i)
For j = 0 To i
List1.AddItem MyArray(0, j)
List2.AddItem MyArray(1, j)
List3.AddItem MyArray(2, j)
List4.AddItem MyArray(3, j)
Next
End Sub
This is for a VB standalone application.
Option Explicit
Option Compare Text
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim Nm, Fn
Dim Nms As String
Dim objConnection
Dim filtr, filt2
Dim strPathtoTextFile
Dim MyArray()
Private Sub Form_Load()
ReDim MyArray(3, 500)
Set objConnection = CreateObject("ADODB.Connection")
Set filtr = CreateObject("ADODB.Recordset")
strPathtoTextFile = "S:\Database\"
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
'This works
filtr.Open "SELECT FirstName, LastName, Company,BusinessPhone, MobilePhone FROM Outlook.csv where LastName = 'Brown'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Desired
'filtr.Open "SELECT FirstName, LastName, Company,BusinessPhone, MobilePhone FROM Outlook.csv", _
' objConnection, adOpenStatic, adLockOptimistic, adCmdText
Call Test
End Sub
Sub Test()
Dim i As Long, j As Long, k As Long
i = -1
List1.Clear
List2.Clear
List3.Clear
List4.Clear
'**********Help needed here*********
'Desired
'Filt2 = Select from Filtr WHERE LastName = 'Brown'"
'********************************
'then change filtr to filt2 below
Do Until filtr.EOF
Nm = filtr.Fields.Item("LastName")
Fn = filtr.Fields.Item("FirstName")
i = i + 1
Nms = Nm & ", " & filtr.Fields.Item("FirstName")
MyArray(0, i) = Nms & " "
MyArray(1, i) = filtr.Fields.Item("Company") & " "
MyArray(2, i) = filtr.Fields.Item("BusinessPhone") & " "
MyArray(3, i) = filtr.Fields.Item("MobilePhone") & " "
filtr.MoveNext
Loop
ReDim Preserve MyArray(3, i)
For j = 0 To i
List1.AddItem MyArray(0, j)
List2.AddItem MyArray(1, j)
List3.AddItem MyArray(2, j)
List4.AddItem MyArray(3, j)
Next
End Sub