January 12, 2012 at 4:34 pm
I am creating a database on a sql server 2008 r2 server backend with Access 2007 as the front end. As I go through creating the database I want to ensure I am doing my best to stick to 3NF. On the access 2007 form there is a field called category. I have created a seperate table for this field, named Category and it has 17 values.
There is also a table named CaseData that has a column named Category.
I would like the ID column of the Category table to serve as the primary key to the Category column in the CaseData table.
I would then want the Category field on the Access form to act as a drop down list displaying the data in a column named CatType in the Category table.
What would be the best way to create this type of referential integrity?
Below is the code to create the database.
CREATE DATABASE [GKHELPDESK] ON PRIMARY
( NAME = N'GKHELPDESK', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'GKHELPDESK_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [GKHELPDESK] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_NULLS OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_PADDING OFF
GO
ALTER DATABASE [GKHELPDESK] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [GKHELPDESK] SET ARITHABORT OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [GKHELPDESK] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [GKHELPDESK] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [GKHELPDESK] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [GKHELPDESK] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [GKHELPDESK] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [GKHELPDESK] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [GKHELPDESK] SET DISABLE_BROKER
GO
ALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [GKHELPDESK] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [GKHELPDESK] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [GKHELPDESK] SET READ_WRITE
GO
ALTER DATABASE [GKHELPDESK] SET RECOVERY FULL
GO
ALTER DATABASE [GKHELPDESK] SET MULTI_USER
GO
ALTER DATABASE [GKHELPDESK] SET PAGE_VERIFY CHECKSUM
GO
USE [GKHELPDESK]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [GKHELPDESK] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE GKHELPDESK;
GO
SET NOCOUNT ON;
-- Create CaseData Table
CREATE TABLE dbo.CaseData
(
Id INT IDENTITY(100,1) PRIMARY KEY
,CaseDate DATE NOT NULL
,Customer VARCHAR (50) NOT NULL
,Category TINYINT NOT NULL
,CaseDescription VARCHAR (30) NOT NULL
,CaseStatus VARCHAR(7) NOT NULL
,ClosedDate DATE NULL
,Resolution VARCHAR (400) NULL
);
CREATE TABLE dbo.Category
(
ID INT IDENTITY(1,1) PRIMARY KEY
,CatType VARCHAR (25) NOT NULL
);
GO
--Add values to the Category Table
USE GKHELPDESK
go
INSERT INTO Category (CatType)
VALUES
('Equipment')
,('Email')
,('Asset')
,('Navision')
,('User')
,('Desktop')
,('Laptop')
,('Disaster Recovery')
,('Phone')
,('Citrix')
,('Software')
,('Hardware')
,('PDF')
,('Sales')
,('Server')
,('Security')
,('Database')
,('Printing')
,('Meeting')
,('Project')
,('Microsoft Office');
USE GKHELPDESK
go
INSERT INTO CaseData(CaseDate, Customer, Category, CaseDescription, CaseStatus)
VALUES
('01/07/2012', 'Keith', '17', 'Building GK Help Desk Database', 'Pending');
USE master
go
create login [GKHDUSER] with password = N'password'
, default_database = [GKHELPDESK]
, default_language = [us_english]
, check_expiration = off
, check_policy = off
Go
Use GKHELPDESK
go
create user [GKHDUSER] for login [GKHDUSER]
go
--Set Permissions on Tables for user
--Set Permissions on the CaseData Table
use [GKHELPDESK]
GO
GRANT ALTER ON [dbo].[CaseData] TO [GKHDUSER]
GO
use [GKHELPDESK]
GO
GRANT DELETE ON [dbo].[CaseData] TO [GKHDUSER]
GO
use [GKHELPDESK]
GO
GRANT INSERT ON [dbo].[CaseData] TO [GKHDUSER]
GO
use [GKHELPDESK]
GO
GRANT SELECT ON [dbo].[CaseData] TO [GKHDUSER]
GO
use [GKHELPDESK]
GO
GRANT UPDATE ON [dbo].[CaseData] TO [GKHDUSER]
GO
--Set permission on the Category Table
use [GKHELPDESK]
GO
GRANT SELECT ON [dbo].[Category] TO [GKHDUSER]
GO
January 12, 2012 at 10:09 pm
Maybe I should just worry about creating referential integrity and then see how access deals with it.
So what is the best way to enforce referential integrity? Should I add a constraint to the CaseData table?
such as
Select CatType From dbo_Category Inner Join dbo_CaseData on dbo_CaseData.Category = dbo_Category.ID
Thanks for your response.
January 13, 2012 at 6:04 am
kwoznica (1/12/2012)
So what is the best way to enforce referential integrity? Should I add a constraint to the CaseData table?
You should be able to create a regular Foreign Key Constraint on the CaseData table that points to Category. Do this in SQL Server and I believe it will carry over to your Access front end. I'm assuming your Access front end will be using these tables as Linked Tables, though, and not as Access-side tables that update SQL Server through some sort of import / export process.
January 13, 2012 at 3:33 pm
Thanks Brandie. I added the below code and get an error message stating
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.CaseData' that match the referencing column list in the foreign key 'FK_Category_CatType'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
USE GKHELPDESK
GO
Alter Table dbo.Category
Add Constraint FK_Category_CatType Foreign Key (ID)
References dbo.CaseData (Category);
I'm going to try it the other way with the Constraint being added to the CaseData table.
January 13, 2012 at 3:50 pm
Doing it this way:
USE GKHELPDESK
GO
Alter Table dbo.CaseData
Add Constraint FK_Category_CatType Foreign Key (Category)
References dbo.Category (ID);
Allowed the command to complete successfully. Hopefully access will see it.
January 13, 2012 at 3:55 pm
kwoznica (1/13/2012)
Doing it this way:
USE GKHELPDESK
GO
Alter Table dbo.CaseData
Add Constraint FK_Category_CatType Foreign Key (Category)
References dbo.Category (ID);
Allowed the command to complete successfully. Hopefully access will see it.
Technically there is nothing for Access to see - referential integrity is backend and if the RDBMS (SQL Server engine in this case) finds out that a specific DML is attempting to violate it, it will return an error message to whatever front end is on the other side (Access in this particular case).
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 13, 2012 at 4:02 pm
I think I just found that out for myself.
How then can I make a drop down list in access show data in a seperate table yet relates to the current record of the main table?
I'm sorry but something isn't connecting for me so if someone has suggestions please let me know.
January 14, 2012 at 1:39 am
Is there any particular reason for using Access Front End (except you are familiar with it)? I have used Access Front End but only if the backend is Access. There are better programming languages (front end) that will provide you more control on such requirements.
January 14, 2012 at 10:05 am
Dev (1/14/2012)
Is there any particular reason for using Access Front End (except you are familiar with it)? I have used Access Front End but only if the backend is Access. There are better programming languages (front end) that will provide you more control on such requirements.
Yeah I only know access. Should I be trying this with Visual Basic or C#?
January 14, 2012 at 12:16 pm
Dev (1/14/2012)
Is there any particular reason for using Access Front End (except you are familiar with it)? I have used Access Front End but only if the backend is Access. There are better programming languages (front end) that will provide you more control on such requirements.
Disagree completely - we have been doing Access front-ends to SQL Server backends for over 15 years, and in mission critical situations. It is quite easy using linked tables, and and referential integrity constraints that are established in SQL Server are recognized by the Access front-end. The simplest way to do what you are attempting is to build the entire application in Access, and then use either the SQL Server upsizing tool in SQL Server 2008, or the Access to SQL Server upsizing tool in Access 2007. That will build any referential integrity constraints you have established in Access. One nice aspect is that you can also upload an Access app to a forum such as this if you are encountering problems with your design.
And we've not found any faster method of developing robust front-ends than Access. Only in specialized real-time applications with high volume, or web based applications is there any reason to consider other development environments.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
January 14, 2012 at 12:37 pm
kwoznica (1/14/2012)
Dev (1/14/2012)
Is there any particular reason for using Access Front End (except you are familiar with it)? I have used Access Front End but only if the backend is Access. There are better programming languages (front end) that will provide you more control on such requirements.Yeah I only know access. Should I be trying this with Visual Basic or C#?
No, you don't want to add a new problem to your pile. When selecting a development language you have to take into consideration available skill set and since the skill set pool you have available includes only Access, go with it. I've seen complex front-end apps developed in Access and they work just fine.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 15, 2012 at 4:12 am
If you know only Access then actually you don't have options right now. So for your current issue, please find solution in Access only.
There are few, still prefer Access and I have no complain / objection with them. But my friendly advice to you is to learn another programming language of your choice. It will help you in long run.
January 15, 2012 at 2:20 pm
Hi
In my opinion Access is fine as a front end tool if you are not planning to distribute the app to a large number of users. But the suitability of Access is not the point of this thread and is a whole other discussion on which you find opinions many and varied.
So, to answer your question
How then can I make a drop down list in access show data in a seperate table yet relates to the current record of the main table?
First a word or two on your database. Ideally, you should match your datatypes when it comes to the Category ID attribute - you have it specified as an INT on one table, while you you have it set to TINYINT ion the other. Small point, but reliable, easy to maintain databases are about consistency.
On the assumption that you have successfully created the foreign key, make sure that the combo control is bound to the ID on the CaseData table. On the combo properties sheet data tab, the Row Source property for the combo should be based on a query on your lookup table, with the first column in that query being ID. Make sure that the Bound Column property is set to 1. Youi might want to set Limit To List to Yes if you don't want Users adding types on the fly.
On the format tab, your Column Count can be set to 2 if you want both columns in the drop-down list or 1 if you just want the Type column. Set the Column Widths property to sizes appropriate to the data eg 1cm;3cm or if you don't want the ID to show in the list 0cm;3cm.
And that's about it.
All the best.
Regards
Rowan
January 16, 2012 at 7:37 am
ProofOfLife (1/15/2012)
HiFirst a word or two on your database. Ideally, you should match your datatypes when it comes to the Category ID attribute - you have it specified as an INT on one table, while you you have it set to TINYINT ion the other. Small point, but reliable, easy to maintain databases are about consistency.
On the assumption that you have successfully created the foreign key, make sure that the combo control is bound to the ID on the CaseData table. On the combo properties sheet data tab, the Row Source property for the combo should be based on a query on your lookup table, with the first column in that query being ID. Make sure that the Bound Column property is set to 1. Youi might want to set Limit To List to Yes if you don't want Users adding types on the fly.
On the format tab, your Column Count can be set to 2 if you want both columns in the drop-down list or 1 if you just want the Type column. Set the Column Widths property to sizes appropriate to the data eg 1cm;3cm or if you don't want the ID to show in the list 0cm;3cm.
And that's about it.
All the best.
Regards
Rowan
Rowan,
Thanks for your advice. I have changed the data type to both be INT. So they are now matching.
I have created the foreign key and below is the sql code, which completed successfully.
USE GKHELPDESK
GO
Alter Table dbo.CaseData
Add Constraint FK_Category_CatType Foreign Key (Category)
References dbo.Category (ID);
Aside from that I tried what you recommended for making the combo box bound to the ID column of case data and it started working. I already had the other parameters set as you specified.
Thank you so much I can finally move forward with this.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply