如何使用VBA时，创建表指定主键 [英] How to specify Primary Key when using vba to create tables

问题描述

我已经更新了code与下面给出的建议，这是我测试过的伟大工程，为未来用户快速参考。

I've updated the code with the suggestion given below, which I've tested and works great, for quick reference for future users.

我用下面的code创建链接的表，而无需设置一个DSN为每一个用户，我怎么可以指定一个主键，你会得到，如果手动连接问：

I'm using the below code to create linked tables without having to set up a DSN for each user, how can I specify a primary key as you would be asked if connecting manually:

Dim sConnect    As String
Dim db          As DAO.Database
Dim tdf         As DAO.TableDef

Set db = CurrentDb

Set tdf = db.CreateTableDef
tdf.Name = "dbo_vwFeedback" ' - -- --- This is the Label that you see in Access...
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=server01\serverinstance;DATABASE=db_name;Trusted_Connection=Yes"
tdf.SourceTableName = "vwFeedback" ' - -- --- This is the actual name in SQL Server, minus the owner.
db.TableDefs.Append tdf
CurrentDb.Execute "CREATE UNIQUE INDEX PK_dbo_vwFeedback_PrimaryKey ON dbo_vwFeedback (DataSetID, FeedbackRef) WITH PRIMARY"

注：以上是对SQL Server视图，它是相同的SQL Server表，但你并不需要在 CurrentDB.Execute 行（如果你的主关键是正确设置在服务器上）。

NOTE: The above is for a SQL Server VIEW, it is identical for a SQL Server TABLE, but you do not need the CurrentDB.Execute line (if your primary key is correctly set up on the Server).

在code是在联系表，并手动指定一个主键的地方。

The code is in the place of linking a table and specifying a primary key manually.

如果您不手动或使用上述方法指定创建索引的SQL Server视图中，你只会得到一个只读视图，您可能会遇到错误的数据返回，见下面的评论的例子。

If you do not specifying the creation of an index for SQL Server VIEWs manually or with the above method, you will only get a READ ONLY view and you may experience erroneous data returned, see comments below for examples.

推荐答案

链接表从您的问题code后，你需要这样做：

After linking the table with the code from your question, you need to do this:

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

请参阅 VBA code添加链接表与主键以一个完整的例子。

See VBA Code to Add Linked Table with Primary Key for a complete example.

请注意，你做的不可以需要做，如果你链接的表 - 访问会自动检测到主键（如Remou在他的评论明确下文）。

Note that you do not need to do this if you link a table - Access will detect the primary key automatically (as Remou made clear in his comment below).

但是，当你在访问链接的SQL Server 查看，它是非常重要以指定一个合适的主键在Access中的视图。
如果指定了错误的键（=您选择列不识别的唯一记录）或无键可言，Access将视图链接为只读表（因为你已经注意到了）。

But when you link a SQL Server view in Access, it is very important to specify a proper primary key for the view in Access.
If you specify the wrong key (= you select columns that don't identify a unique record) or no key at all, Access will link the view as read-only table (as you already noticed).

此外，它会搞砸了显示的行 - 看为什么链接视图从MS Access给出不同的结果VS SQL管理器？更多解释。
（读我的答案，和我的下对方的回答评论）的

Plus, it will screw up the displayed rows - see Why does linked view give different results from MS Access vs SQL Manager? for more explanation.
(read my answer, and my comments under the other answer)

这篇关于如何使用VBA时，创建表指定主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！