访问数据库传递问题 [英] Access DB Pass Through Question

问题描述

早上好，

长时间阅读，第一次发布！

Long time reader, first time posting!

我有一个项目在我们的机械车间中使用多个PC上的wifi，每5分钟从MDB提取一次数据.我们的商店有非常糟糕的wifi信号，而且我经常会丢失映射的驱动器，因此其中存在一些try循环.我的问题是...

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)在我的信号强度较低的情况下，我试图将网络流量保持在尽可能低的水平.一次只拉一列吗?我的同事给我留下了这样的印象，那就是我会导致整个表格在客户端经过 而不是在服务器端进行过滤，导致流量超过所需，并建议在服务器端进行通过"连接.

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)在网络不稳定的情况下，这是处理重试我的任务的好方法还是有更好的方法?

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

3)我想到了在程序启动时打开连接并保持打开状态的想法，以避免必须一直打开/关闭.这是一个好主意吗?如果可以，是否可以检查连接是否断开?

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?

这是我的子项目的简短版本(这些列中只有10行左右)...

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)

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

   公共子数据库任务(DBC)
        UpdateTimer.Stop()

TryConnectionAgain:
        MyConnection.ConnectionString ="Provider = Microsoft.Jet.OLEDB.4.0;数据源="& DatabaseFile
        Dim TryCount为整数= 0
       试试
            MyConnection.Open()
       渔获
            TryCount + = 1
            System.Threading.Thread.Sleep(1000)
            Application.DoEvents()
            Me.Refresh()
           转到TryConnectionAgain
           如果TryCount = 10那么
                UpdateTimer.Start()
                SkipCount + = 1

    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

               如果SkipCount = 3，则
                    RestartPC()
               如果结束

               退出子
           如果结束
       结束尝试

                If SkipCount = 3 Then
                    RestartPC()
                End If

                Exit Sub
            End If
        End Try

        MyDataset.Tables.Clear()
        MyDataset.Clear()
TryCommandAgain:
       试试
            MyAdapter.SelectCommand =新的OleDb.OleDbCommand(DBC，MyConnection)
       渔获
            System.Threading.Thread.Sleep(1000)
            Application.DoEvents()
            Me.Refresh()
           转到TryCommandAgain
       结束尝试
        MyAdapter.Fill(MyDataset)
        MyConnection.Close()

        UpdateTimer.Start()
   结束Sub

        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

推荐答案

我确定您确实应该使用电缆代替WiFi.另外，还有网络扩展器，但我不知道其详细信息.

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.

可能您应该升级到SQL Server(或者可能是MySQL).我不知道这会有什么帮助，但是您超出了Access的预期范围.

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的DoEvents是因为VB无法按照设计使用Windows API的方式处理消息. VB已得到极大改进.我建议不要使用Sleep和DoEvents，除非您确定没有更好的方法.我假设 您可以使用计时器而不是睡眠.睡眠期间不处理消息.

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.

您是在Access脚本中执行此操作吗?如果是这样，则应转换为Windows窗体，WPF或UWP应用程序.单独(至少在某种程度上)将数据库设计为使其更易于转换为其他数据库.您可以进行数据库访问 在后台任务中.

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.

这篇关于访问数据库传递问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！