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

vb6 Lebanon
  • 14 years ago


    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
            range = rs![range]

        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


    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).


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.

“There are only two kinds of languages: the ones people complain about and the ones nobody uses” - Bjarne Stroustrup