我在SQL Server数据库的可更新视图。当我创建链接表将其与ODBC,有人问我,选择唯一记录标识符,以便它是可更新的。
I have an updatable view in sql server database. When I create linked table to it with ODBC, I'm asked to select unique record identifier, in order for it to be updateable.
我需要动态地重新链接该表在VBA,所以我需要删除并重新创建链接表(我无法更新TableDef.Connect属性ODBC表)。
I need to dynamically relink this table in VBA, so I need to drop and recreate the linked table (I cannot update the TableDef.Connect property for ODBC table).
我发现了几个解决方案,这并不适用于我的情况:
I found several solutions, which are not applicable in my case:
连接之后创建索引:我不能为ODBC数据源 创建数据库主键:我不能,这是一个视图这些是适用的:
在一个code,将做向导做 在一个code重新链接,而不需要删除的TableDef和工程使用ODBC链接表,并不会重置previously集标识符临时解决方法:
转换以物化视图,并在其上创建唯一索引为什么你不能链接后创建索引ODBC源?
Why can't you create an index for an ODBC source after linking?
在工作中,我们使用的是与访问链接的SQL Server表,当有人想要连接到不同的数据库(从生产环境的变化,以测试环境下),我们做这样的事情的所有表:
At work, we are using Access with linked SQL Server tables, and when someone wants to connect to a different database (change from production environment to test environment), we do something like this for all tables:
Dim TD As TableDef
Dim ConString As String
ConString = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DbName;Trusted_Connection=Yes;"
CurrentDb.TableDefs.Delete "SomeTable"
Set TD = CurrentDb.CreateTableDef("SomeTable", 0, "SomeTable", ConString)
CurrentDb.TableDefs.Append TD
Set TD = Nothing
CurrentDb.Execute "CREATE UNIQUE INDEX SomeIndex ON SomeTable (PrimaryKeyColumn) WITH PRIMARY"