3

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

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"

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).

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

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.

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
aSystemOverload
  • 2,994
  • 18
  • 49
  • 73

1 Answers1

6

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"

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

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).

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).

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)

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • It might be an idea to check that an index has not already been assigned (`tdf.Indexes.Count`) and whether or not it is primary (`tdf.Indexes(0).Primary`). If you link a table with a suitable index, it will be used. – Fionnuala Jul 17 '12 at 23:16
  • It depends. I'm using a local table which contains a list of server tables/views to link, and I have a function that loops through that list, and deletes and re-links each table. So I *know* that the table doesn't already have an index at that moment. – Christian Specht Jul 18 '12 at 05:09
  • 1
    The point I am trying to make is that an SQL Server table that has a primary key set on SQL server and is linked through a TableDef, as in the OP, will have a primary key set automatically, without any further intervention the name will be the same as the SQL Server name. – Fionnuala Jul 18 '12 at 08:54
  • 2
    Yes, you're right, I should have mentioned this in my answer. But the OP is linking a **view**, not a table. And when you link a view, you **have** to explicitly specify the key if you don't want to run into the problems described in my answer. – Christian Specht Jul 18 '12 at 09:01