Go to Top

USING SQL VIEWS TO ADD/EDIT DATA IN 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.

ADVERTISING
MS Access Report Generator

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:

About Susan Pyne

I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I’m certified on Power BI, with more certifications coming. I'm also the chapter coordinator for AccessUserGroups.org, a worldwide organization of Access virtual chapters that meet each month online to discuss a range of topics in different languages. I LOVE working with Access and learning new technologies!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.