access复制数据_使所有查询在Access中都是可复制的

access复制数据

Recently, I've been making changes to an Access database that someone else built. In most cases, that's enough of a challenge, but this database has an extra hurdle – it's set up for replication.

最近，我一直在对其他人构建的Access数据库进行更改。 在大多数情况下，这已经是一个挑战，但是此数据库还有一个额外的障碍–已为复制设置。

One copy of the database is set up as a Design Master, and replicas are made from that copy. Everyone can make changes and additions in their copy, then all the copies are synchronized, to pull all the data together.

将数据库的一个副本设置为设计母版，并从该副本制作副本。 每个人都可以在其副本中进行更改和添加，然后同步所有副本，以将所有数据收集在一起。

I hit a snag, right near the end of the development phase, so I'm posting this solution, in case it helps someone else -- or me, later, when I need the code again. 😉

我在开发阶段快要结束时遇到了麻烦，所以我发布了此解决方案，以防日后再次需要代码时对其他人或我有帮助。 😉

不要移动设计大师 (Don't Move the Design Master)

I'll spare you the gory details, but all the structural changes have to be made in the Design Master. If that file is lost or corrupted, you can create a new Design Master from one of the replicas in the set.

我将为您省去细节，但是所有结构上的更改都必须在Design Master中进行。 如果该文件丢失或损坏，则可以从集合中的一个副本中创建一个新的设计母版。

My client sent me the Design Master to work on, and I've been busily making changes for several weeks. Now, it's time to send it back, so they can use all the fancy new forms and reports.

客户将我的设计大师寄给我进行工作，而我忙着进行几周的更改。 现在，是时候将其发送回去了，以便他们可以使用所有新奇的表格和报告。

Unfortunately, I learned that when the Design Master is moved from its original folder (i.e. when the client sent me a zipped copy), the moved copy becomes a replica.

不幸的是，我了解到，将设计母版从其原始文件夹中移出时(即，当客户向我发送压缩副本时)，移动后的副本将成为副本。

创建一个新的设计大师 (Create a New Design Master)

So, I had to get another copy of the database, with all the current data, and strip out everything except the tables. Then, I imported all the queries, forms and reports from the new version, and will send it back to my client.

因此，我必须获得具有所有当前数据的数据库的另一个副本，并除去表以外的所有内容。 然后，我从新版本导入了所有查询，表单和报告，并将其发送回我的客户端。

When they get the database, they'll make it the Design Master, and create new replicas for everyone to use.

当他们获得数据库时，他们将成为设计大师，并创建供所有人使用的新副本。

It's an annoying workaround, but we've done a few tests, and it works fine.

这是一个烦人的解决方法，但是我们已经进行了一些测试，并且效果很好。

使查询可复制 (Make the Queries Replicable)

It's easy to do an import of all the forms, queries and reports that are in another database, and that step took just a couple of minutes. However, all the imported objects should come in marked as "Replicable", because that is property setting in the other database.

导入另一个数据库中的所有表单，查询和报告很容易，而这一步骤仅花费了几分钟。 但是，所有导入的对象都应标记为“可复制”，因为这是另一个数据库中的属性设置。

The forms and reports were fine, but none of the queries had the Replicable property turned on. There are over 100 queries, and I wanted a way to turn on that property programmatically, not manually.

表单和报表都很好，但是所有查询都没有启用Replicable属性。 有超过100个查询，我想要一种以编程方式而不是手动方式打开该属性的方法。

You'd think that would be easy, but I spend a long time searching in Google, Bing, and my shelf full of Access books. Nothing helpful appeared in the search results, but I didn't give up!

您可能认为这很容易，但是我花了很长时间在Google，Bing和我装满Access书籍的书架上搜索。 搜索结果中没有显示任何有用信息，但我没有放弃！

Finally, I found an article, written in 1999, on the Microsoft website, and it had the code I was looking for -- Implementing Database Replication with JRO

最终，我在Microsoft网站上找到了一篇写于1999年的文章，其中包含我想要的代码- 使用JRO实现数据库复制

设置参考 (Set a Reference)

The code has to run from another database, so I used a different copy of the client's database. The database with the queries to update is closed.

该代码必须从另一个数据库运行，所以我使用了客户端数据库的另一个副本。 具有要更新查询的数据库已关闭。

In the database where I put the replication code, I had to set a reference to JRO in the Visual Basic Editor (Tools > Reference)

在放置复制代码的数据库中，我必须在Visual Basic编辑器中设置对JRO的引用(“工具”>“参考”)。

复制代码 (The Replication Code)

The following code will change Replicable setting for the specified object to True.

下面的代码会将指定对象的Replicable设置更改为True。

Sub MakeObjectReplicable(strDBPath As String, _
                         strObjectName As String, _
                         strObjectType As String)
   Dim repMaster      As New JRO.Replica
   repMaster.ActiveConnection = strDBPath
   repMaster.SetObjectReplicability strObjectName, strObjectType, True
   Set repMaster = Nothing
End Sub

循环查询 (Loop Through the Queries)

The sample code in the article only changed one query, but I wanted to change them all. The database I used to run this code had all the same queries as the target database, so I looped through its queries.

本文中的示例代码仅更改了一个查询，但我想全部更改。 我用来运行此代码的数据库具有与目标数据库相同的查询，因此我遍历了所有查询。

As you can see in the code comments, even though I'm updating queries, the object type is "Tables". I tried "Queries" first, but that didn't work, and then I noticed this warning in the Microsoft article:

从代码注释中可以看到，即使我正在更新查询，对象类型也是“表”。 我先尝试了“查询”，但是没有用，然后我在Microsoft文章中注意到了这个警告：

Important   Even though the SetObjectReplicability property provides an ObjectType argument to specify the type of object you are working with, the argument only accepts a value of "Tables" for both queries and tables.

重要说明即使SetObjectReplicability属性提供了一个ObjectType参数来指定要使用的对象的类型，该参数对于查询和表都仅接受“ Tables”值。

The code runs quickly, and this will be handy if we ever have to rebuild the Design Master. Use this at your own risk though – I'm certainly not a Replication expert, and it might not work in all cases.

代码运行很快，如果我们不得不重建设计母版，这将非常方便。 但是，使用此方法需要您自担风险 –我当然不是复制专家，并且它不一定在所有情况下都有效。

Sub MakeAllQueriesReplicable()
Dim strPath As String
Dim strFile As String
Dim qry As QueryDef
strPath = "C:\Data"
strFile = "MyNewDesignMaster.mdb"
on error resume next
For Each qry In CurrentDb.QueryDefs
	'NOTE: object type 'Tables' is used for both tables and queries
	MakeObjectReplicable strPath & "\" & strFile, qry.Name, "Tables"
Next qry
End Sub

翻译自: https://contexturesblog.com/archives/2012/10/08/make-all-queries-replicable-in-access/

access复制数据