pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedAccess problems
5+ messages / 5 participants
[nested] [flat]
* Access problems
@ 1999-02-26 08:01 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Davis @ 1999-02-26 08:01 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: 'byronn@insightdist.com' <byronn@insightdist.com>
The sql.log file is over 5M. There are no references to foreign keys
anywhere in the log file. I noticed the following:
Orders 138:118 ENTER SQLExecDirect
HSTMT 0x09072230
UCHAR * 0x040f10f0 [ -3] "SELECT
Config, nValue FROM MSysConf\ 0"
SDWORD -3
This failed because my Postgres database does not have this table. Any idea
what MsysConf, what it contains, or how to create it? Could this have
anything to do with my Access subform problem?
Thanks, Michael
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
<mailto:[SMTP:byronn@insightdist.com]>
Sent: Thursday, February 25, 1999 2:12 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have created the following trigger on the parent/master
table
(orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the
orderlines
table when
> every a row is deleted from orders. This triggers works
great.
Access,
> however, is still not recognizing the foreign key
relationship
between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify
that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of
the
session? This would be the "sql.log" file which contains all api
calls to
odbc. Make sure the file is clear beforehand. Then run the test.
Quit
Access, turn tracing off and there's your logfile. You can send it to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number
appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Access problems
@ 1999-02-26 14:05 Byron Nikolaidis <byronn@insightdist.com>
parent: Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Byron Nikolaidis @ 1999-02-26 14:05 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces
Michael Davis wrote:
> The sql.log file is over 5M. There are no references to foreign keys
> anywhere in the log file. I noticed the following:
> Orders 138:118 ENTER SQLExecDirect
> HSTMT 0x09072230
> UCHAR * 0x040f10f0 [ -3] "SELECT
> Config, nValue FROM MSysConf\ 0"
> SDWORD -3
> This failed because my Postgres database does not have this table. Any idea
> what MsysConf, what it contains, or how to create it? Could this have
> anything to do with my Access subform problem?
>
No. This is a normal Access warning. It is looking for a configuration file
for the Jet database engine. It is optional.
But it is interesting that there are no references to "SQLForeignKeys". What's
up with that? Are there any references to SQLPrimaryKeys?
I don't understand -- if it doesn't call SQLForeignKeys, how else would it find
out about this information?
Byron
^ permalink raw reply [nested|flat] 5+ messages in thread
* Access problems
@ 1999-12-20 14:23 Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Meskes @ 1999-12-20 14:23 UTC (permalink / raw)
To: pgsql-interfaces
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);
* Re: [INTERFACES] Access problems
@ 1999-12-20 19:45 David C Hartwig Jr <daybee@bellatlantic.net>
parent: Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: David C Hartwig Jr @ 1999-12-20 19:45 UTC (permalink / raw)
To: Michael Meskes <meskes@postgreSQL.org>; +Cc: pgsql-interfaces
Michael Meskes wrote:
> 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.
>
>
I am a bit rusty on the internals on the psqlODBC driver as it relates to MS
Access. But as I seem to recall, Access cannot have a key column over 256
bytes. Text columns are virtually unbounded. This may explain some of your
tables linking problems. If you can, try changing some of those primary key
fields to varchar(255) or smaller.
Ultimately what you need to do is activate the logging feature in the driver
and attach a minimalist failure log file.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Access problems
@ 1999-12-21 11:03 Michael Meskes <meskes@postgreSQL.org>
parent: David C Hartwig Jr <daybee@bellatlantic.net>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Meskes @ 1999-12-21 11:03 UTC (permalink / raw)
To: David C Hartwig Jr <daybee@bellatlantic.net>; +Cc: Michael Meskes <meskes@postgreSQL.org>; pgsql-interfaces
On Mon, Dec 20, 1999 at 02:45:19PM -0500, David C Hartwig Jr wrote:
> I am a bit rusty on the internals on the psqlODBC driver as it relates to MS
> Access. But as I seem to recall, Access cannot have a key column over 256
> bytes. Text columns are virtually unbounded. This may explain some of your
> tables linking problems. If you can, try changing some of those primary key
> fields to varchar(255) or smaller.
I did and now it works without a problem.
Thanks a lot.
> Ultimately what you need to do is activate the logging feature in the driver
> and attach a minimalist failure log file.
I did but did not find much of an explanation. But then maybe I didn't check
closely enough.
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-12-21 11:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-26 08:01 Access problems Michael Davis <michael.davis@prevuenet.com>
1999-02-26 14:05 ` Byron Nikolaidis <byronn@insightdist.com>
1999-12-20 14:23 Access problems Michael Meskes <meskes@postgresql.org>
1999-12-20 19:45 ` David C Hartwig Jr <daybee@bellatlantic.net>
1999-12-21 11:03 ` Michael Meskes <meskes@postgreSQL.org>
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