Foxpro and ADO speed and permissions issues

Posted on 2006-05-12
Last Modified: 2010-05-01
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
Question by:HKComputer
    LVL 4

    Author Comment

    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?
    LVL 4

    Author Comment

    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.

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now