All geek questions in one place

VBA code to add a linked table with a primary key

I have an updatable view in sql server database. When I create a linked table with ODBC, I am asked to select a unique record identifier so that it can be updated.

Dialog in the wizard to select unique identifier

I need to dynamically bind this table in VBA, so I need to drop and recreate the linked table (I cannot update the TableDef.Connect property for the ODBC table).

I found several solutions that are not applicable in my case:

  • create index after link: I cannot for ODBC source
  • create a primary key in the database: I can’t, this is a view

They are applicable:

  • code that will do what the master does
  • code for the link without the need to remove the TableDef and which works with the associated ODBC table and will not reset the previously set identifier

Temporary workaround:

  • transform a view into a materialized view and create a unique index on it
+6
source share
1 answer

Why can't you create an index for an ODBC source after the link?

At work, we use Access with SQL Server related tables, and when someone wants to connect to another database (moving from a production environment to a test environment), we do something similar 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"
+11
source

Source: https://habr.com/ru/post/910174/

More articles:


All Articles