在MSAccess中使用ADO从另一个mdb文件打开表

I'm trying to access a table from another mdb file. This isn't something a linked table can fix, please don't suggest it.

我正在尝试从另一个mdb文件访问一个表。这不是链表可以解决的问题,请不要提出建议。

Edit: This is part of an archive tool that creates a backup file of the backend of the database from a certain date range, and then deletes the data. Before I delete the data, it was suggested that I check and make sure all the data copied over to the created backup. (IE, if the worst case scenario happened and the share folder lost connection or something, and not all the data copied over, but the access database deleted that data anyway)

编辑:这是存档工具的一部分,该工具从特定日期范围创建数据库后端的备份文件,然后删除数据。在删除数据之前,建议我检查并确保将所有数据复制到创建的备份中。 (IE,如果最坏的情况发生并且共享文件夹丢失连接或者其他东西,而不是所有数据都复制了,但访问数据库仍然删除了该数据)

If there's a way to create a linked table in access and then delete the link in the end WITH VBA, not manually, maybe that would work(?) but I was hoping to do it this way so hopefully it wouldn't cause the database to bloat up as much as it has been in the past.

如果有一种方法可以在访问中创建一个链接表,然后删除最后的链接WITH VBA,而不是手动,也许那会起作用(?)但是我希望这样做,所以希望它不会导致数据库像过去那样膨胀起来。

I'm trying to check if all my data copied over to a backup file before I delete the ones in the current database.

在删除当前数据库中的数据之前,我正在尝试检查是否所有数据都复制到备份文件中。

Dim rs as ADODB.Recordset
rs.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=Backup.mdb;DefaultDir=C:\Fish;"

I'm having trouble here with the syntax. I know it works with currentdb, but what if the table is in a different mdb file? I tried with DefaultDir=C:\Fish and DefaultDir=C:\Fish\ Is there something I'm missing or is this a lot more complicated then what I should attempt?

我在语法方面遇到了麻烦。我知道它适用于currentdb,但是如果该表位于不同的mdb文件中该怎么办?我尝试使用DefaultDir = C:\ Fish和DefaultDir = C:\ Fish \是否有我遗漏的东西或者这比我应该尝试的要复杂得多?

The rest of it I think I understand how to get the fieldvalues to compare, but trying to connect to a table in a different mdb file is giving me trouble.

其余的我认为我理解如何让fieldvalues进行比较,但尝试连接到不同mdb文件中的表是给我带来麻烦。

1 个解决方案

#1

First of all: exactly like Gord already said in his comment, I can't see a reason against using a linked table either.

首先:就像戈德在评论中已经说过的那样,我也看不出使用链接表的原因。

But in the end it doesn't really matter if you create a linked table and open a local Recordset on it, or if you open a Recordset directly on the table in the other Access database.

但最终,如果您创建链接表并在其上打开本地Recordset,或者直接在其他Access数据库中的表上打开Recordset,则无关紧要。

So I'll show solutions for both ways:

所以我将展示两种方式的解决方案:

---

1. The approach from your question

Your connection string works for me, but there are mistakes in the rest of your code:

您的连接字符串适用于我,但其余代码中存在错误:

• An ADODB.Recordset doesn't have a property ConnectionString...it's called ActiveConnection instead.

ADODB.Recordset没有属性ConnectionString ...它被称为ActiveConnection。

• You need to actually create a new ADODB.Recordset before you try to set the connection string or open the Recordset

在尝试设置连接字符串或打开Recordset之前,您需要实际创建一个新的ADODB.Recordset

The following code works for me:

以下代码适用于我:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=Backup.mdb;DefaultDir=C:\Fish;"

rs.Open "select * from foo"

---

2. Using a linked table

Create it:

DoCmd.TransferDatabase acLink, "ODBC", ";DATABASE=C:\Fish\Backup.mdb", acTable, "TableName", "TableNameLocal", False

Note: TableName is the original name of the source table, and TableNameLocal is the name that the linked table will have in your local database. (You can use the same names if you want, of course)

注意:TableName是源表的原始名称,TableNameLocal是链接表在本地数据库中的名称。 (当然,如果你愿意,可以使用相同的名称)

Delete it:

DoCmd.DeleteObject acTable, "TableNameLocal"