Data Retrieving is slower in nodes

[ragavendran]

Here is my data retrieving code :

 Dim vDT1 As New DataTable

          vSqlText = "SELECT * from From B_RecMast where SOMECONDs order by RecID"
            
          If RecordCNT(vSqlText) = 0 Then
                MsgBox("No Record is available", vbExclamation)
                Grid_Reset(Grid1)
                Exit Sub
            End If

            cmd = New OleDbCommand(vSqlText, cnnRice2)
            vRs = cmd.ExecuteReader()

            Do While vRs.Read
                
                Dim vNR As DataRow
                    vNR = vDT1.NewRow()
                    vNR.Item("ReceiptNo") = vRs("ReceiptNo")
                    vNR.Item("ReceiptDate") = vRs("RectDate")
                    vNR.Item("Mode") = vRs("Mode")
                    vNR.Item("Amt") = vRs("AmtRecdThisRecpt")
                    vNR.Item("Name") = vRs("Name")
                    vDT1.Rows.Add(vNR)
            Loop

            Grid1.DataSource = vDT1

This code retrieves hundreds of records in few seconds in the server PC where the database (Access2007) is there.
Other PCs connected to this Server PC in LAN environment.
In the other PCs retrieving takes several minutes. Why this delay is happening ?

Note : Application folder is Shared in the server PC

[jmcilhinney]

Firstly, don't populate a DataTable that way. Either use an data adapter and call Fill or, if you're going to use a data reader, call Load on the data reader.

Secondly, I have a sneaking suspicion that you are actually executing your query twice. What exactly does that RecordCNT method do? I don't see how it could be getting the number of records retrieved by a query without executing the query, which would be a very silly thing to do. Even if it doesn't do that you should still get rid of that section. If you want to know whether your result set is empty or not then you test the HasRows property of the data reader or, if you use a data adapter, the value returned by Fill. That Grid_Reset method seems pointless too as you can simply bind the empty DataTable and the grid will be empty.

As for your issue, if the code works as it should then the code is fine. If it takes more time to retrieve data over the network than locally then I'd say that there's a fair chance that it's related to the network. Using Access databases over a network is just a bad idea. If you used a "proper" database, e.g. SQL Server, then things could well be better, unless there genuinely is an issue with the network unrelated to the use of an Access database. The Express edition of SQL Server is free and can support databases up to 10 GB, so there's usually not a good reason to not use it.

[ragavendran]

@ JMC
Thank you so much. Here is my stand why I use the Access DB

1. I need to put the database in my client’s PC itself. (They work offline only.)
2. If I use the Access database with PW protection no one can open it and view my tables.
3. On the other hand if I use the Sql Database, I don’t know how to secure my Database as above.

How to make it in such a way that no one be able to view the Table schemas even in windows authentication mode in case I use SqlServer ?