• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

SELECT WHERE with Foxpro Db from VB6

I need to create a recordset such as the following:  SELECT * FROM Table1 WHERE Status = "OPEN"   ...but the "table" is a Foxpro .dbf.  I can access the file and use all the records, but I just want a subset.  SQL Server or Access wouldn't be a problem, but I can't seem to find the right method to use this filtering with an ISAM file.  

What's the best way to create a filtered recordset from a Foxpro 2.6 source?
1 Solution
How are you currently accessing the file and using the records? Are you using DAO? Some sample code would be a good start.
' I hope the following works for you !

    Dim wrkJet As Workspace
    Dim dbsTest As Database
    Dim rstTemp As Recordset

    ' Create Workspace
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    ' Open Database : Replace path to fox files and fox version
    Set dbsTest = wrkJet.OpenDatabase("o:\viglen data\dbfs\all", True, True, "FoxPro 2.5;")

    ' Create recordset with a select and a where clause
    ' Chaneg SQL to reflect your query
    ' If the filter is a text string then ensure that single quotes are used
    Set rstTemp = dbsTest.OpenRecordset("SELECT * FROM ordline WHERE ion = '393632'", dbOpenForwardOnly)
    ' Loop through recordset and print some results
    With rstTemp
        Do While Not .EOF
            Debug.Print .Fields(0), .Fields(1)
    End With


ggloverAuthor Commented:
That got me started - thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now