Very Important (Call an access querry with function from VB6)

vb6 Lebanon
  • 14 years ago

    Hi,

    I have created a query in access in which a function is being called with parameters from same query.

    The function of course is localized in a module in the same access file.

    When I have tried to call the query from VB6 as a recordset, it didn't work as VB didnot recognize the function name.

    I have created the same function in VB6 but it its taking alot of time to run (up to 50 times than it was taking in access).

    Does any one have any idea on how to retrive the contents of a query as static data and not to run upon being called.

    Your assistance will be highly appreciated.

    below I have enclosed parts of the query, function and calling procedure.

    The Query (Results)

    SELECT SampleID, Abbreviation, Description AS [Test Name], Unit, StatusValidation, Result, Range([Test],[Age],[Sex])), Test, Age, Sex from …

     

     

    The Function (as written in access)

     

    Public rs As ADODB.Recordset
    Public db As ADODB.Connection
    Public SQL As String




    Function range(Test1 As String, Age1 As String, sex1 As String)
        Set db = Application.CurrentProject.Connection
        Set rs = CreateObject("ADODB.Recordset")
        SQL = "select Top 1 Low, High, '[' & Low & ' - ' & High & ']' as Range FROM PSM_MASTERTESTSRANGES WHERE (TestCode = '" & Test1 & "' And Age > '" & Age1 & "' And (PhysiologicalType = '" & sex1 & "' Or PhysiologicalType = '0'))"
        rs.Open SQL, db
        If Not rs.EOF Then
        rs.MoveFirst
            range = rs![range]

        Else
        range = ""
        End If
    End Function












     

    The calling procedure

     

    Dim WithEvents RsRead As Recordset
    Dim db As Connection

     

        Set db = New Connection
        db.Open "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;dsn=PSMLOCAL;uid=;pwd=;"
        Set RsRead = New Recordset



       
       Dim sq As String
       sq = "select * from Results where SampleID= '" & Me.txtField(0) & "' "
      
        RsRead.Open sq, db, adOpenStatic, adLockOptimistic
           
            Set Me.MSHFlexGrid1.DataSource = RsRead1.DataSource





  • 14 years ago

    Hi,

    10x for all of you who viewed the post and actually I have been able to solve the problem which has been caused by the fact that am mistakenly opening a new connection and recordset for each record in the grid.

    I solved that problem through creating a seperate function that opens the connection and sets the recordset once at form load, and kept evry thing else the same and it just worked fine.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perl - The only language that looks the same before and after RSA encryption.” - Keith Bostic