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

Foxpro and ADO speed and permissions issues

My issue seems to be two part.  I'm using VB6 to access a Visual Foxpro 8 database.  I need to select a single record as readonly in the most speedy fashion.  I'm only using some of the fields in this record and there is a field with a calculated value that I specifically cannot query (returns errors).  What could I do to make this faster and possibly elimate the Runtime Error 70 Permission Denied.

    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCon, strDB, strSQL, strItem, strBin, strRead, strInput, strOutPut, strOutPutText, strDesc As String
    Dim intQty As Integer
    Dim lngCost, lngPrice As Double
    Dim Fsys As New FileSystemObject
    Dim OutStream As TextStream

    'This part retrieves the connection string and the SQL statement from a text file
    Set TStream = Fsys.OpenTextFile(".\BSV.ini", ForReading, False)
    While TStream.AtEndOfStream = False
    strRead = TStream.ReadLine
    Select Case Left(strRead, 3)
        Case "Con"
        strCon = Trim(Mid(strRead, 5, 300))
        Case "SQL"
        strSQL1 = Trim(Mid(strRead, 5, 300))
    End Select
    Set TStream = Nothing
    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset
    con.ConnectionString = strCon

    Set TStream = Fsys.OpenTextFile(strInput, ForReading, False)
    While TStream.AtEndOfStream = False
        strRead = TStream.ReadLine
            If Left(strRead, 2) = "sI" Then
            strItem = Trim(Mid(strRead, 4, 25))
            'Here is the SQL statement retrieved from the text file
            strSQL = "Select item_cd, item_desc, bal_qty, rtl_price1, pur_price, item_loc from xmsalinv.dbf Where item_cd = '" & strItem & "'"

            'Here's what takes forever to open
            rst.Open strSQL, con, adOpenStatic, adLockReadOnly

                  'This part sometimes returns the runtime 70 Permission Denied errors. This is a .dll error. The file system is shared, the database is too, and there is no password.
                  If rst.EOF = False Then
                        intQty = IIf(rst!Qty = 0, "", rst!Qty)
                        lngPrice = IIf(Round(rst!Rtl * 100) = 0, "", Round(rst!Rtl * 100))
                        strBin = rst!Bin
                        lngCost = IIf(Round(rst!Cst * 100) = 0, "", Round(rst!Cst * 100))
                        strDesc = rst!Des
                  End If
             strOutPutText = strOutPutText & "SB " & strBin & vbCrLf & "sC " & lngCost & vbCrLf & "sD " & strDesc & vbCrLf & "sE " & strVen & vbCrLf & "sI " & strItem & vbCrLf & "sO " & intQty & vbCrLf & "sP " & lngPrice & vbCrLf
             strItem = ""
             strDesc = ""
             lngCost = 0
             lngPrice = 0
             intQty = 0
             End If
  • 2
1 Solution
HKComputerAuthor Commented:
I forgot to mention that the table I'm accessing is huge.  Several hundred thousand records.  Also, my SQL statement listed above isn't quite right.  It is as follows:

"Select item_cd, item_desc As Des, bal_qty As Qty, rtl_price1 As Rtl, pur_price As Cst, item_loc As Bin from xmsalinv.dbf Where item_cd = '" & strItem & "'"

Should this question be posted in the DB section instead?
HKComputerAuthor Commented:
I'm going to ask to have this question deleted and points refunded.  There was multiple problems that I found a fixed.

(A) I chose to use an OLEDB connection instead of ODBC.  It now operates about 10 times faster.
(B) I was getting Type Mismatch and Runtime Error 70: Permission Denied errors in my retrieving of field values from the recordset.  This turned out to be an error with my data/variable type.  I changed my currency and number variables to be variant instead.  And now they work flawlessly without errors.  And my calculations also work this way.
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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