与 CreateTableDef 链接时如何设置主键 [英] How to set primary key when linking with CreateTableDef

问题描述

在 MS Access 数据库中，我像这样连接到 SQL Server 数据库中的视图:

In a MS Access database, I'm connecting to views in a SQL Server database like this:

Dim s  As String
s = "ODBC;DSN=mydb;Trusted_Connection=Yes;DATABASE=mydb;"

Dim td As TableDef
Set td = CurrentDb.CreateTableDef("vwMyView", 0, "MySchema.vwMyView", s)

CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh

这将创建一个链接表，该表链接到 SQL Server 中的视图.

This creates a linked table, which is linked to a view in SQL Server.

但是，我无法插入/更新/删除，因为 Access 不知道主键".VBA中如何添加主键信息?

However, I cannot insert/update/delete, because Access does not know the "primary key". How can the information about the primary key added in VBA ?

使用链接表向导时，总是会要求您从列表框中选择唯一键列.我想在 VBA 中重现这种行为.

When using the Linked Table Wizard, you are always asked to select the unique key columns from a listbox. I want to reproduce this behaviour in VBA.

推荐答案

您可以随时更新刚刚附加的表以包含索引/主键.类似的东西，

You can always update the table you just attached to include an Index/Primary key. Something like,

Dim s  As String
s = "ODBC;DSN=mydb;Trusted_Connection=Yes;DATABASE=mydb;"

Dim td As TableDef
Set td = CurrentDb.CreateTableDef("vwMyView", 0, "MySchema.vwMyView", s)

CurrentDb.TableDefs.Append td

CurrentDb.Execute "CREATE UNIQUE INDEX SomeIndex ON vwMyView (PrimaryKeyColumn) WITH PRIMARY".

CurrentDb.TableDefs.Refresh

Set td = Nothing

不确定是否需要在创建 INDEX 之前刷新 CurrentDB.首先尝试刷新，如果它不起作用 - 刷新它然后执行 CREATE 语句.

Not sure if you need to refresh the CurrentDB before creating an INDEX. Try refreshing without first, if it does not work - refresh it then Execute the CREATE statement.

这篇关于与 CreateTableDef 链接时如何设置主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！