Using SQL Views to Add/Edit Data in Microsoft Access

This is part II of a two part series on views I’ve written, you can see part I here.

Over the last couple of years I have had a great deal of experience with working on Access Databases with the Data is linked to tables in stored in SQL Server. With relational databases and lookup tables you can easily end up with a view that contains multiple numeric columns which just hold the primary key from other tables.

Say for example you have a database with two tables: one for company information and another just for contacts, but you need to link your contacts with companies and you have situations where a contact is linked with multiple companies. You can easily manage this by creating a third table of CompanyContacts which would be rows of data containing CompanyID and ContactID. There are many situations where you would have a similar table that is linking data from one table to another but these tables need to be maintained and and managed by users who really need to see the underlying data and not just the ID’s. To resolve the issue you can create a view with all the needed tables and base your form on the linked view, but you will quickly notice you can’t edit any data, it’s all read only.

Tips for making your view editable

In order to edit your view in Access you’ll need to make sure it has a primary key from the table you wish to edit, along with the time stamp field and an index. The first two can easily be accomplished by adding the fields in your view design in SQL Server, for the latter use the CREATE UNIQUE INDEX as shown below, along with the code to add your view as a linked table:

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

Set db = CurrentDb
Set tdf = db.CreateTableDef
tdf.Name = “ViewNameInAccess”
tdf.SourceTableName = “dbo.ViewName”
tdf.Connect = “ODBC;<SQL Server connection string>”
db.TableDefs.Append tdf

db.Execute “CREATE UNIQUE INDEX [PRIMARY] ON [ViewNameInAccess](MainTableID);”, dbFailOnError