January 7, 2012 at 10:45 am
I am designing a simple help desk database and would like to have sql server 2008 as the back end and use access 2007, eventually access 2010, as the front end. I have created the database, tables, and user login/permissions. Code is below.
I have inserted some sample data and am adding a text box to read the case id but it currently does not display the data even though the control source allows me to select it. What I see is attached as a jpg in this forum post.
Can someone take a look and give me some feed back on what I may be doing wrong, why the first record of the casedata table isn't being read, and what direction I should take with making access the front end?
sql code is below.
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 7, 2012 at 11:10 am
I am assuming that you can see the tables from Access (accdb or adp?)
just an idea....what happens if you use the form wizard to create a form based on Casedata....do you see your records?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 7, 2012 at 11:48 am
Thanks!!!
That was it. Wizards for the win!
January 7, 2012 at 11:52 am
Great !
hopefully now you can determine what control sources to use...and dispense with the wizard (it has its uses but I used to abhor it :-P)
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply