From: Michael Meskes <meskes@postgresql.org>
To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Access problems
Date: Mon, 20 Dec 1999 15:23:53 +0100
Message-ID: <19991220152353.A585@fam-meskes.de> (raw)
I have installed onshore-timesheet on my machine. It works like a char. No I
tried to access the database via M$ Access and found some problems. The ODBC
connection is up and running. However, Access refuses to link in several
tables.
I attach the table definitions.
It says the following attributes are not correct either in index or in
relationship definition:
table attribute
-------------------------------
category category_id
hours del
job downloaded
notification fkpersonnel_id
personnel personnel_id
Only the client relation is okay.
So far I wasn't able to see what's wrong with these. Maybe anyone on this
list has an idea. Also ODBC logs don't show something special.
Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Attachments:tables.sql (text/plain, 4.1K) download
CREATE TABLE notification ( // table of who should be notified for actions on a job (not implemented)
fkjob_id int, // the job id number (relates to job_id in job table)
fkpersonnel_id text, // the personnel id number (relates to personnel_id in personnel table)
constraint notification_pkey primary key ( fkjob_id, fkpersonnel_id )
);
CREATE TABLE client ( // list of all clients with open or closed jobs
client_id int, // client id (generated externally from ×heet;)
client_name text not null, // the client's full name as it should appear on reports
default_bill_rate money, // the default billing rate for the client
default_approved int, // if automatically approved, 0 = no, 1 = yes
constraint client_pkey primary key ( client_id )
);
CREATE TABLE personnel ( // list of all personnel, employees and supervisors who use timesheet
personnel_id text, // the logon id for the user
first_name text, // user's first name
middle_name text, // user's middle initial
last_name text, // user's last name
email text, // e-mail address
super_user text, // if they have supervisor status or not, 0 = no, 1 = yes
constraint personnel_pkey primary key ( personnel_id )
);
CREATE TABLE hours ( // list of all logged, approved and deleted hours in ×heet;
hours_id int, // id of the hours being logged, generated internally by ×heet;
fkjob_id int not null, // the job's id as relates to the job in the job table
fkclient_id int not null, // the client's id as relates to the client in the client table
fkpersonnel_id text not null, // the user's id as it relates to the personnel_id in the personnel table
date_entered date, // date the user logged the hours, generated by ×heet; as the time the entry is made
intime text, // date the user attributes the hours worked
time_in text, // time the hours entry started
time_out text, // time the hours entry stopped
total_hours real not null, // total hours for a particular entry
billable int, // should this be billable to the client, 0 = no, 1 = yes
del text, // is this a deleted hours entry, 0 = no, 1 = yes
hours_description text, // the description of the hours worked
comment text, // comments about the hours worked
category text, // category for this work (from set list)
parking real, // parking fees accrued for this hours entry
expense_amount money, //
downloaded text, // downloaded because approved, 0 = no, 1 = yes
fkapprover_id text, // the id of the supervisor who approved the hours
approval_date date, // the date the hours were approved
constraint hours_pkey primary key ( hours_id )
);
CREATE TABLE job ( // list of all possible jobs
job_id int, // id of the job, generated internally by ×heet;
fkclient_id int not null, // the client's id as relates to the client table
estimated_hours real, // estimated number of hours for the completion of this job
current_hours_estimate real, //
current_estimate_date date, // the date the estimate of hours was made on the job
open_date date, // date the job was opened
close_date date, // date the job was closed
open int, // job is open or not, 0 = no, 1 = yes
bill_rate money, // the billing rate for this job
job_description text, // the description of the job
default_approved int, // any hours logged to the job are automatically approved
fksupervisor_id text, // the id of the supervisor responsible for this job, as relates to the id in the personnel table
fknotify_id text, // list of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation
flag_hrs int, // number of hours logged to this job for flagging a warning to the supervisor
category text, // category for this job (from set list)
downloaded text, // if the job has been downloaded or not, 0 = no, 1 = yes
constraint job_pkey primary key ( job_id )
);
CREATE TABLE category ( // list of possible categories of work for hours and jobs
category_id text, // the id for the category
creator_id text, // the id for who created the category
constraint category_pkey primary key ( category_id )
);
indices.sql (text/plain, 984B) download
create index client_client_id on client (client_id);
create index hours_date_entered on hours (date_entered);
create index hours_del on hours (del);
create index hours_downloaded on hours (downloaded);
create index hours_fkclient_id on hours (fkclient_id);
create index hours_fkjob_id on hours (fkjob_id);
create index hours_fkpersonnel_id on hours (fkpersonnel_id);
create index hours_approval_date on hours (approval_date);
// unsure about this one, i.e., why it existed
// create index hours_fkpersonnel_id on hours using hash ( fkpersonnel_id text_ops);
create index hours_hours_id on hours (hours_id);
create index hours_id_index on hours (hours_id);
create index job_downloaded on job (downloaded);
create index job_fkclient_id on job (fkclient_id);
create index job_fksupervisor_id on job (fksupervisor_id);
create index job_job_id on job (job_id);
create index personnel_personnel_id on personnel (personnel_id);
create index personnel_super_user on personnel (super_user);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: meskes@postgresql.org
Subject: Re: Access problems
In-Reply-To: <19991220152353.A585@fam-meskes.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox