none
Access DB Pass Through Question RRS feed

  • Question

  • Good Morning All,

    Long time reader, first time posting!

    I have a project using multiple pc's on wifi in our machine shop pulling data from a MDB every 5 minutes. Our shop has pretty awful wifi signal and I tend to lose my mapped drives often so there are some try loops in there. My questions are...

    1) With my signal strength low I am trying to keep network traffic as low as possible. Is this pulling just one column at a time? My coworkers are under the impression doing it the way I am will cause the entire table to be gone through on the client side instead of filtered at the server side causing more traffic than needed and suggested a "Pass Through" connection.

    2) With the network being flaky, is this an ok way to handle re-trying my task or is there a better way?

    3) I've toyed with the idea of opening the connection at the start of the program and leaving it open to avoid having to open/close all the time. Is this a good/bad idea? If it is ok, is there a way to check if the connection has been broken?

    Here is the short version of what my sub does (These Columns only have 10 or so rows in them)...

     

            DatabaseTask("SELECT " & ColName & " FROM " & DatabaseTable)

        Public Sub DatabaseTask(DBC)
            UpdateTimer.Stop()

    TryConnectionAgain:
            MyConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DatabaseFile
            Dim TryCount As Integer = 0
            Try
                MyConnection.Open()
            Catch
                TryCount += 1
                System.Threading.Thread.Sleep(1000)
                Application.DoEvents()
                Me.Refresh()
                GoTo TryConnectionAgain
                If TryCount = 10 Then
                    UpdateTimer.Start()
                    SkipCount += 1

                    If SkipCount = 3 Then
                        RestartPC()
                    End If

                    Exit Sub
                End If
            End Try

            MyDataset.Tables.Clear()
            MyDataset.Clear()
    TryCommandAgain:
            Try
                MyAdapter.SelectCommand = New OleDb.OleDbCommand(DBC, MyConnection)
            Catch
                System.Threading.Thread.Sleep(1000)
                Application.DoEvents()
                Me.Refresh()
                GoTo TryCommandAgain
            End Try
            MyAdapter.Fill(MyDataset)
            MyConnection.Close()

            UpdateTimer.Start()
        End Sub

    Saturday, June 3, 2017 2:39 PM

All replies

  • I am sure you know you really should use cables instead of WiFi. Also, there are network extenders but I don't know the details of that.

    Probably you should upgrade to SQL Server (or maybe MySQL). I don't know how that would help but you are beyond the limits of what Access was intended for.

    VB's DoEvents was provided because VB was unable to process messages the way that the Windows API was designed to be used. VB has been drastically improved. I suggest not using Sleep and DoEvents unless you know for sure there is not a better way. I assume you can use a timer instead of Sleep. Messages are not processed during a Sleep.

    Are you doing this in an Access script? If so then you should convert to Windows Forms, WPF or maybe a UWP applications. Design it with the database separate (at least somewhat) to make it easier to convert to some other database. You can do database access in a background task.



    Sam Hobbs
    SimpleSamples.Info


    Saturday, June 3, 2017 5:08 PM
  • Hi JSergeant81, thanks for posting.  Let me try to address each of your questions:

    #1)  Yes.  The query string you are using is getting a single column value from all rows in the table.  If you aren't using every single row then you would be returning excess data which could be potentially culled by adding a WHERE clause to the SELECT statement.

    #2)  Not really.  Instead, use a single timer to periodically attempt to get data.  If the attempt fails, change the period to a lower value until the attempt either succeeds, or your max-retry value is reached.  If successful, reset the timer period to the longer length.  If not, notify the user to restart the PC (or whatever action the RestartPC() method takes).  The Sleep() and DoEvents() are not necessary.

    #3)  Bad idea.  Open the connection, use it, then close it.  If the connection fails to open or the command errors out during execution, you will retry again sooner and begin counting failures as explained in #2 above.  The connection to the database should be closed when not in use.

    You might want to add a check to My.Computer.Network.IsAvailable to ensure that IP connectivity is available before even trying the connection.  I'm not sure at what layer you encounter problems with your WiFi network so this check may or may not improve error handling (e.g. avoid throwing exceptions in the database access code) but it won't hurt anything to add.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Sunday, June 4, 2017 1:51 PM
    Moderator