| Title | Define a program's queries in text files |
|---|
| Description | This example shows how to define a program's queries in text files in Visual Basic 6. It uses FSO to read the file and load the queries. |
|---|
| Keywords | data, database, query, configuration |
|---|
| Categories | Database |
|---|
|
|
|
Thanks to Tim Fee.
The program uses a FileListBox to list the .dat files that are available. Those files hold the lists of queries. The following text shows one of the files. Each group of three lines contains a query name, a database file name, and a SELECT statement.
|
|
List Fruit
Fruit.MDB
Select Distinct FruitName from tbl_fruit
Count Fruit
Fruit.MDB
Select count(*) from tbl_fruit
List Color
Color.MDB
Select ColorName from tbl_Color
Count Color
Color.MDB
Select count(*) from tbl_Color
|
|
|
When the user selects a file from the FileListBox, the program opens the selected data file using a FileSystemObject and places the query names in a list.
|
|
Private Sub File1_Click()
' Go to the Data files directory and open the selected
' datafile
Dim FileSystemObject, TextStream As Object
Set FileSystemObject = _
CreateObject("scripting.filesystemobject")
Set TextStream = FileSystemObject.opentextfile(App.Path _
+ "\DatFiles\" + File1.FileName)
List1.Clear
' Populate List1 with all of the action titles from the
' data file
' Lines 1, 4, 7 and 10. This uses the skipline Method
Do Until TextStream.AtEndOfStream = True
List1.AddItem (TextStream.readline)
TextStream.skipline
TextStream.skipline
Loop
TextStream.Close
End Sub
|
|
|
When the user clicks a query name in the list, the program reopens the data file and moves to the selected query. It reads the entry's database file name and the SELECT statement. It opens the database and executes the query.
|
|
Private Sub List1_Click()
Dim SelLine As Integer
Dim RecSet As Recordset
Dim UseDatabase As Database
Dim SelDatabase As String
Dim SQLStr As String
' For List Index = 0
' Get the Line number of the Database Name and SQL
' Statement you want
' to execute
' Since the List index starts with 0 and the Line
' Numbers begin with one
' I need to add 2 to get the Database Name (Line 2) and
' Line 3 for the SQL
' Statement
' For List Index > 0
' the equation to get the Line Number is ListIndex
' Times 3 Plus 2
' i.e. for ListIndex 1 I need to get lines 5 and 6 so
' (1 * 3) + 2 = 5
If List1.ListIndex = 0 Then
SelLine = List1.ListIndex + 2
Else
SelLine = (List1.ListIndex * 3) + 2
End If
' Open the File object
Dim FileSystemObject, TextStream As Object
Set FileSystemObject = _
CreateObject("scripting.filesystemobject")
Set TextStream = FileSystemObject.opentextfile(App.Path _
& "\DatFiles\" & File1.FileName)
' This will skip all lines up to but not including the
' line you want
Do Until TextStream.Line = SelLine
TextStream.skipline
Loop
'Read in the database Name and SQL Statement
SelDatabase = TextStream.readline
SQLStr = TextStream.readline
TextStream.Close
' Use the Like "comparitor" to determine what controls
' will be viewed on the form
Dim SelItem As String
SelItem = List1.Text
Set UseDatabase = OpenDatabase(App.Path & "\" & _
SelDatabase)
Set RecSet = UseDatabase.OpenRecordset(SQLStr)
If SelItem Like "Count*" = True Then
Text1.Visible = True
List2.Visible = False
Text1.Text = RecSet.Fields(0)
Label1.Visible = True
Set FileSystemObject = _
CreateObject("scripting.filesystemobject")
Set TextStream = _
FileSystemObject.opentextfile(App.Path + _
"\DatFiles\Count.txt")
Label1.Caption = TextStream.readall
TextStream.Close
Else
Text1.Visible = False
List2.Visible = True
Label1.Visible = False
List2.Clear
While Not RecSet.EOF
List2.AddItem (RecSet.Fields(0))
RecSet.MoveNext
Wend
End If
RecSet.Close
UseDatabase.Close
End Sub
|
| |
|
| |
