Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access 2007 to SQL 2008 Help needed Expand / Collapse
Author
Message
Posted Saturday, January 14, 2012 10:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, June 14, 2014 11:52 AM
Points: 179, Visits: 404
I am trying to create a simple help desk application using access 2007 as the front end but am falling short in with some concepts. I have attached images(please see attached images) of the acces form as well as sql code to run on your own sql server.
I have two simple tables. A CaseData table and a Category table. The CaseData table will hold the marjority of the data and be on the main form in access but I want to have a combo box lookup list that points to the category table. when I put such an object in the access form I can pick the category but when I look in the casedata.category column referntial integrity is lost as the field only shows null in sql server. You will see this in the untitled4.jpg I have attached.
I would like the casedata.category column to be updated with the correct number from the category.id column. The untitled5.jpg message shows the category options.

what could I be missing?

I have added a foreign key constraint on the casedata.category column to the category.id column but this doesn't seem to have taken any effect.

Below is the sql code to create the database, tables, and user objects
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');
--01/12/2012 recieved a mismatch error in access so changing this to INT datatype
alter table CaseData Alter column Category INT

--01/13/2012 trying to add referential integrity for a category drop down list in access
USE GKHELPDESK
GO
Alter Table dbo.CaseData
Add Constraint FK_Category_CatType Foreign Key (Category)
References dbo.Category (ID);
--01/14/2012 change the Case Status column to a BIT datatype
alter table CaseData Alter column CaseStatus BIT



Any suggestions are welcome.


  Post Attachments 
Untitled3.jpg (8 views, 34.18 KB)
Untitled4.jpg (7 views, 41.63 KB)
Untitled5.jpg (5 views, 40.11 KB)
Post #1236170
Posted Saturday, January 14, 2012 11:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 23,077, Visits: 31,604
Please do not cross post. You already have a thread started for this issue here, please continue using it while working the problem.

Please do not post responses on this thread, use this one.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1236178
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse