通过网络对* .mdb的ODBC访问 [英] ODBC access over network to *.mdb

问题描述

我对带有* .mdb的ODBC如何工作有一个一般性的问题.

I have a general question of how ODBC with *.mdb works.

以我的理解，访问存储在另一台计算机(例如NAS)上的MS Access数据库(* .mdb)时，无论我是否使用odbc，都可以纠正我的错误.首先，需要加载完整的* .mdb文件，然后再对其进行任何操作，无论它们多么简单.与仅将结果发送回查询的SQL Server不同.

In my understanding, and please correct me if I'm wrong, no matter if I use odbc or not, when accessing an MS Access database (*.mdb) that is stored on another computer, i.e. a NAS, my computer first needs to load the full *.mdb file before it can do any operations on it, no matter how simple they are. Much unlike an SQL server who just sends back the result to a query.

如果您可以确认这一点，那么关于通过odbc访问mdb的多用户环境，我有几个相关问题:

If you can confirm this, then I have a few related questions with regards to multiuser environments where the mdb is accessed via odbc:

• 文件多久保存回商店(即NAS)?是否存在某种空闲"超时或类似的超时?我试图找出使用较大文件生成的网络流量.
• 一个用户多快能看到另一个用户在同一文件上所做的更改?
• 在没有不断导致数据损坏的情况下，这种文件访问如何在多用户环境中工作?

请注意，我确实知道更好和更有效的解决方案，我真的只是对这种特定情况的内部运作感兴趣.

Please note that I do know about better and more efficient solutions, I am really just interested in the inner workings of this specific situation.

谢谢

推荐答案

以我的理解，访问存储在另一台计算机(例如NAS)上的MS Access数据库(* .mdb)时，无论我是否使用odbc，都可以纠正我的错误.首先，需要加载完整的* .mdb文件，然后再对其进行任何操作，无论它们多么简单.

In my understanding, and please correct me if I'm wrong, no matter if I use odbc or not, when accessing an MS Access database (*.mdb) that is stored on another computer, i.e. a NAS, my computer first needs to load the full *.mdb file before it can do any operations on it, no matter how simple they are.

你错了.打开数据库时，整个网络上不会.emdb(或.accdb)文件.如果可以使用索引来标识相关行，那么即使是对特定表的操作也不一定会通过网络拉动整个表.对于使用Wireshark执行的实际测量，请在此处查看我的答案.

You are wrong. The entire .mdb (or .accdb) file will not be pulled across the network when the database is opened. Even operations on a particular table will not necessarily pull the whole table over the network if indexes can be used to identify the relevant row(s). For real-world measurements performed using Wireshark, see my answer here.

文件多久保存回商店(即NAS)?是否存在某种空闲"超时或类似的超时?我试图找出使用较大文件生成的网络流量.

How often is the file saved back to the store (i.e. the NAS)? Is there some kind of "idle" timeout or similar? I am trying to figure out the network traffic this generates with larger files.

在提交更新之前，Access数据库引擎可能会将其缓冲几秒钟.这样做可能是为了减少对数据库文件的争用.提交更新 时，Access数据库引擎仅将修改后的页面通过网络发送回去；它不会重写整个表(或文件).

The Access Database Engine may buffer updates for a few seconds before committing them. This is probably done to reduce contention on the database file. When the updates are committed, the Access Database Engine only sends the modified pages back over the network; it does not re-write the whole table (or file).

一个用户多快能看到另一个用户在同一文件上所做的更改?

How quickly can one user see the changes made by another on the same file?

我前段时间进行了一些测试，发现大约五(5)秒后，一个用户(连接)所做的更改通常可用于其他连接.详情请参阅我的答案此处.

I ran some tests a while ago and found that changes made by one user (connection) are normally available to other connections after about five (5) seconds. See my answer here for details.

在没有不断导致数据损坏的情况下，这种文件访问如何在多用户环境中工作?

How can this kind of file access work in a multiuser environment without constantly leading to corrupted data?

每个用户都有自己的Access数据库引擎实例，该实例可操作共享数据库文件，但是各个实例相互协作以管理记录/页面锁定.请注意，以共享模式"打开Access数据库文件(.accdb或.mdb)时，会在同一文件夹中创建锁定文件(.laccdb或.ldb). Access数据库引擎的各种实例都使用此锁定文件来管理多用户访问.

Each user has their own instance of the Access Database Engine that manipulates the shared database file, but the various instances work in co-operation with each other to manage record/page locking. Note that when an Access database file (.accdb or .mdb) is opened in "shared mode" a lock file (.laccdb or .ldb) is created in the same folder. The various instances of the Access Database Engine use this lock file to manage multi-user access.

我只是使用Wireshark捕获由使用ODBC运行的VBScript生成的网络流量

I just used Wireshark to capture the network traffic generated by a VBScript that uses ODBC to run

SELECT COUNT(*) AS n FROM TestData WHERE ID=1

针对网络共享上的84.3 MB Access数据库文件.该文件由一(1)个表组成，该表包含一百万(1,000,000)行，并以Long Integer [ID]字段作为其主键.

against an 84.3 MB Access database file residing on a network share. The file consists of that one (1) table which contains one million (1,000,000) rows and has the Long Integer [ID] field as its Primary Key.

来自的总网络流量

• 打开与数据库文件的ODBC连接，
• 运行查询，
• 返回结果，并且
• 关闭连接

是110 KB.

这篇关于通过网络对* .mdb的ODBC访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！