InfoPath Dev

 Hello,

I have a repeating table(x rows) which query an Access database(~400MB, 3 tables each table 500 000 lines).

Well the problem is that when i try to query the database for 5 records, it needs like 40 min just to display "the data is exist" or "the data does not exist"(:s and me i hoped that i could check for like 50 records using the same form :-D)    

This is the code:

Public Sub CTRL11_7_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

            Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()

            Dim rows1 As XPathNodeIterator = domNav.Select( _

            "/dfs:myFields/my:group1/my:group2", NamespaceManager)

            While rows1.MoveNext()

                Dim p As Integer

                p = 1

                Dim field1 As String = rows1.Current.SelectSingleNode( _

                "my:field1", NamespaceManager).Value

                'set the query fields

                Dim xpath1 As String = "/dfs:myFields/dfs:queryFields/q:MARA/@MATNR"

                Dim matnr As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath1, NamespaceManager)

                matnr.SetValue(field1)

                Dim field2 As String = rows1.Current.SelectSingleNode( _

                "my:field2", NamespaceManager).Value

                Dim xpath2 As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@VKORG"

                Dim vkorg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath2, NamespaceManager)

                Dim field3 As String = rows1.Current.SelectSingleNode( _

                "my:field3", NamespaceManager).Value

                Dim xpath3 As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@VTWEG"

                Dim vtweg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath3, NamespaceManager)

                MainDataSource.QueryConnection.Execute()

                Dim XPathMV As String = "/dfs:myFields/dfs:dataFields/d:MARA/d:MVKE"

                Dim mvke As XPathNodeIterator = MainDataSource.CreateNavigator().Select(XPathMV, NamespaceManager)

                Dim XPathMA As String = "/dfs:myFields/dfs:dataFields/d:MARA"

                Dim mara As XPathNodeIterator = MainDataSource.CreateNavigator().Select(XPathMA, NamespaceManager)

                Dim countMA As Integer = mara.Count

                If countMA = 0 Then

                    status("Data does not exist MARA")

                Else

                   'status is a function that add a field in a another repeating table with the query status 

                    status("Data exist in MARA")

                    xpath1 = "/dfs:myFields/dfs:queryFields/q:MVKE/@MATNR"

                    matnr.SetValue(field1)

                    vkorg.SetValue(field2)

                    vtweg.SetValue(field3)

  MainDataSource.QueryConnection.Execute()

                    Dim countMV As Integer = mvke.Count

                    If countMV = 0 Then

                        status("Data does not exist MVKE")

                    Else

                        status("Data exist in MVKE")

                    End If

                End If

            End While

        End Sub

Any help would be appreciated . Thank you

P.S I'm using infopath 2007 and Access database 

I would say with that amount of data you should switch to SQL Express and use a web service.  Then you can query for a specific value and only return those values.

That would be the way to go if you want this to perform acceptably.