与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屋!