Thread: MS Access 2000 w/ PGSQL 8.0.x

MS Access 2000 w/ PGSQL 8.0.x

From

Timothy Reed

Date:

19 August 2005, 22:13:49

I am having some issues when trying to query for booleans from Access
to Postgres 8.0.x.   If I change to use Bools as Chars then I am
unable to delete from a table with a boolean in it, as postgres cannot
convert from boolean to char.  If I do not use bools as chars then I
get  "cache lookup failed for function 0"

I have followed what the tech docs have recommended to do with adding
the new msaccessbool function and = operator
(http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)

But to no avail!

Any help would be greatly appreciated.


ODBC Driver settings:
Disable Genetic Optimizer: YES
KSQO: YES
Recognize Unique Indexes: YES
Declare/Fetch : NO
Parse Statements: NO
Cancel As FreeStmt: NO
Unknown Sizes: Max
Text as LongVarChar: YES
Unknows as LongVarChar: NO
Bools as Char : NO
Read Only: NO
Show System Tables: NO
LF <-> CR/LF: YES
Updatable Cursors: NO
bytea as LO: NO
Row Versioning: NO
Disallow Premature: NO
True as -1: YES
Server side prepare: NO


Table Stucture:
Column         Type
Employee ID     integer
First Name     text
Last Name     text
Clocker     boolean
Labor Type ID     bigint
PC Handle     text
On Payroll     boolean
Address 1     text
Address 2     text
City         text
State         text
Zip Code     text
Hire Date     timestamp without time zone
Pay Type     text
Status         text
Pay Rate Old     money
Social Security     text
Phone Number     text
Shift Start     text
Shift End     text
Sign Off     boolean
Hire Date Temp     timestamp without time zone
Stop Date Temp     timestamp without time zone
Stop Date     timestamp without time zone
Rehire Date     timestamp without time zone
Continuous Date     timestamp without time zone
Direct Labor     boolean
Agency         boolean
Active         boolean
Agency2     boolean
Payroll ID     bigint
EMail         text
ADP Employee ID     integer


SQL STATEMENT:

SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
,"public"."Employees"."First Name";


RESULT:
ERROR:  cache lookup failed for function 0


TIA,

--
--------
Timothy Reed

tareed@gmail.com

"I reject your reality, and substitue my own" - Adam Savage

Re: MS Access 2000 w/ PGSQL 8.0.x

From

"Jeff Eckermann"

Date:

22 August 2005, 04:37:10

"Timothy Reed" <tareed@gmail.com> wrote in message
news:dc53e530050819151357b5be95@mail.gmail.com...
>I am having some issues when trying to query for booleans from Access
> to Postgres 8.0.x.   If I change to use Bools as Chars then I am
> unable to delete from a table with a boolean in it, as postgres cannot
> convert from boolean to char.  If I do not use bools as chars then I
> get  "cache lookup failed for function 0"

When you made the change, did you relink the table(s) affected?  When you
do, what datatype does Access show for that column?

What query are you using to do the delete?  Is it a Jet or passthrough
query?

>
> I have followed what the tech docs have recommended to do with adding
> the new msaccessbool function and = operator
> (http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)
>
> But to no avail!
>
> Any help would be greatly appreciated.
>
>
> ODBC Driver settings:
> Disable Genetic Optimizer: YES
> KSQO: YES
> Recognize Unique Indexes: YES
> Declare/Fetch : NO
> Parse Statements: NO
> Cancel As FreeStmt: NO
> Unknown Sizes: Max
> Text as LongVarChar: YES
> Unknows as LongVarChar: NO
> Bools as Char : NO
> Read Only: NO
> Show System Tables: NO
> LF <-> CR/LF: YES
> Updatable Cursors: NO
> bytea as LO: NO
> Row Versioning: NO
> Disallow Premature: NO
> True as -1: YES
> Server side prepare: NO
>
>
> Table Stucture:
> Column Type
> Employee ID integer
> First Name text
> Last Name text
> Clocker boolean
> Labor Type ID bigint
> PC Handle text
> On Payroll boolean
> Address 1 text
> Address 2 text
> City text
> State text
> Zip Code text
> Hire Date timestamp without time zone
> Pay Type text
> Status text
> Pay Rate Old money
> Social Security text
> Phone Number text
> Shift Start text
> Shift End text
> Sign Off boolean
> Hire Date Temp timestamp without time zone
> Stop Date Temp timestamp without time zone
> Stop Date timestamp without time zone
> Rehire Date timestamp without time zone
> Continuous Date timestamp without time zone
> Direct Labor boolean
> Agency boolean
> Active boolean
> Agency2 boolean
> Payroll ID bigint
> EMail text
> ADP Employee ID integer
>
>
> SQL STATEMENT:
>
> SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
> WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
> ,"public"."Employees"."First Name";
>
>
> RESULT:
> ERROR:  cache lookup failed for function 0
>
>
> TIA,
>
> --
> --------
> Timothy Reed
>
> tareed@gmail.com
>
> "I reject your reality, and substitue my own" - Adam Savage
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Re: MS Access 2000 w/ PGSQL 8.0.x

From

Timothy Reed

Date:

22 August 2005, 11:12:38

Yes I always relink the tables after an ODBC change (found that one
out the hardway).

If the driver is configured to use Bools as Chars they show as Text.

if it is configure to NOT use Bools as Chars they show as Yes/No

I am using Jet for the time being, just trying to get the application
to work as-is for now.

On 8/22/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> "Timothy Reed" <tareed@gmail.com> wrote in message
> news:dc53e530050819151357b5be95@mail.gmail.com...
> >I am having some issues when trying to query for booleans from Access
> > to Postgres 8.0.x.   If I change to use Bools as Chars then I am
> > unable to delete from a table with a boolean in it, as postgres cannot
> > convert from boolean to char.  If I do not use bools as chars then I
> > get  "cache lookup failed for function 0"
>
> When you made the change, did you relink the table(s) affected?  When you
> do, what datatype does Access show for that column?
>
> What query are you using to do the delete?  Is it a Jet or passthrough
> query?
>
> >
> > I have followed what the tech docs have recommended to do with adding
> > the new msaccessbool function and = operator
> > (http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)
> >
> > But to no avail!
> >
> > Any help would be greatly appreciated.
> >
> >
> > ODBC Driver settings:
> > Disable Genetic Optimizer: YES
> > KSQO: YES
> > Recognize Unique Indexes: YES
> > Declare/Fetch : NO
> > Parse Statements: NO
> > Cancel As FreeStmt: NO
> > Unknown Sizes: Max
> > Text as LongVarChar: YES
> > Unknows as LongVarChar: NO
> > Bools as Char : NO
> > Read Only: NO
> > Show System Tables: NO
> > LF <-> CR/LF: YES
> > Updatable Cursors: NO
> > bytea as LO: NO
> > Row Versioning: NO
> > Disallow Premature: NO
> > True as -1: YES
> > Server side prepare: NO
> >
> >
> > Table Stucture:
> > Column Type
> > Employee ID integer
> > First Name text
> > Last Name text
> > Clocker boolean
> > Labor Type ID bigint
> > PC Handle text
> > On Payroll boolean
> > Address 1 text
> > Address 2 text
> > City text
> > State text
> > Zip Code text
> > Hire Date timestamp without time zone
> > Pay Type text
> > Status text
> > Pay Rate Old money
> > Social Security text
> > Phone Number text
> > Shift Start text
> > Shift End text
> > Sign Off boolean
> > Hire Date Temp timestamp without time zone
> > Stop Date Temp timestamp without time zone
> > Stop Date timestamp without time zone
> > Rehire Date timestamp without time zone
> > Continuous Date timestamp without time zone
> > Direct Labor boolean
> > Agency boolean
> > Active boolean
> > Agency2 boolean
> > Payroll ID bigint
> > EMail text
> > ADP Employee ID integer
> >
> >
> > SQL STATEMENT:
> >
> > SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
> > WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
> > ,"public"."Employees"."First Name";
> >
> >
> > RESULT:
> > ERROR:  cache lookup failed for function 0
> >
> >
> > TIA,
> >
> > --
> > --------
> > Timothy Reed
> >
> > tareed@gmail.com
> >
> > "I reject your reality, and substitue my own" - Adam Savage
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


--
--------
Timothy Reed

tareed@gmail.com

"I reject your reality, and substitue my own" - Adam Savage

Re: MS Access 2000 w/ PGSQL 8.0.x

From

"Jeff Eckermann"

Date:

23 August 2005, 09:15:48

"Timothy Reed" <tareed@gmail.com> wrote in message
news:dc53e53005082204126f08654f@mail.gmail.com...
> Yes I always relink the tables after an ODBC change (found that one
> out the hardway).
>
> If the driver is configured to use Bools as Chars they show as Text.
>
> if it is configure to NOT use Bools as Chars they show as Yes/No
>
> I am using Jet for the time being, just trying to get the application
> to work as-is for now.

In principle, if your query references the values that are shown in your
Access table, you should have no problem.  The "cache lookup failed" error
is still mysterious.

>
> On 8/22/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
>> "Timothy Reed" <tareed@gmail.com> wrote in message
>> news:dc53e530050819151357b5be95@mail.gmail.com...
>> >I am having some issues when trying to query for booleans from Access
>> > to Postgres 8.0.x.   If I change to use Bools as Chars then I am
>> > unable to delete from a table with a boolean in it, as postgres cannot
>> > convert from boolean to char.  If I do not use bools as chars then I
>> > get  "cache lookup failed for function 0"
>>
>> When you made the change, did you relink the table(s) affected?  When you
>> do, what datatype does Access show for that column?
>>
>> What query are you using to do the delete?  Is it a Jet or passthrough
>> query?
>>
>> >
>> > I have followed what the tech docs have recommended to do with adding
>> > the new msaccessbool function and = operator
>> > (http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)
>> >
>> > But to no avail!
>> >
>> > Any help would be greatly appreciated.
>> >
>> >
>> > ODBC Driver settings:
>> > Disable Genetic Optimizer: YES
>> > KSQO: YES
>> > Recognize Unique Indexes: YES
>> > Declare/Fetch : NO
>> > Parse Statements: NO
>> > Cancel As FreeStmt: NO
>> > Unknown Sizes: Max
>> > Text as LongVarChar: YES
>> > Unknows as LongVarChar: NO
>> > Bools as Char : NO
>> > Read Only: NO
>> > Show System Tables: NO
>> > LF <-> CR/LF: YES
>> > Updatable Cursors: NO
>> > bytea as LO: NO
>> > Row Versioning: NO
>> > Disallow Premature: NO
>> > True as -1: YES
>> > Server side prepare: NO
>> >
>> >
>> > Table Stucture:
>> > Column Type
>> > Employee ID integer
>> > First Name text
>> > Last Name text
>> > Clocker boolean
>> > Labor Type ID bigint
>> > PC Handle text
>> > On Payroll boolean
>> > Address 1 text
>> > Address 2 text
>> > City text
>> > State text
>> > Zip Code text
>> > Hire Date timestamp without time zone
>> > Pay Type text
>> > Status text
>> > Pay Rate Old money
>> > Social Security text
>> > Phone Number text
>> > Shift Start text
>> > Shift End text
>> > Sign Off boolean
>> > Hire Date Temp timestamp without time zone
>> > Stop Date Temp timestamp without time zone
>> > Stop Date timestamp without time zone
>> > Rehire Date timestamp without time zone
>> > Continuous Date timestamp without time zone
>> > Direct Labor boolean
>> > Agency boolean
>> > Active boolean
>> > Agency2 boolean
>> > Payroll ID bigint
>> > EMail text
>> > ADP Employee ID integer
>> >
>> >
>> > SQL STATEMENT:
>> >
>> > SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
>> > WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
>> > ,"public"."Employees"."First Name";
>> >
>> >
>> > RESULT:
>> > ERROR:  cache lookup failed for function 0
>> >
>> >
>> > TIA,
>> >
>> > --
>> > --------
>> > Timothy Reed
>> >
>> > tareed@gmail.com
>> >
>> > "I reject your reality, and substitue my own" - Adam Savage
>> >
>> > ---------------------------(end of
>> > broadcast)---------------------------
>> > TIP 3: Have you checked our extensive FAQ?
>> >
>> >               http://www.postgresql.org/docs/faq
>> >
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faq
>>
>
>
> --
> --------
> Timothy Reed
>
> tareed@gmail.com
>
> "I reject your reality, and substitue my own" - Adam Savage
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: MS Access 2000 w/ PGSQL 8.0.x

From

Timothy Reed

Date:

26 August 2005, 21:07:27

I have a solution!

Problem #1: The table I was trying to delete from did not have a
primary key defined!  Add primary key fixed that.
Problem #2: THe "cache lookup failed" comes from if a form has a
hardcoded SQL statement in it that is looking where a boolean is true
(SELECT * FROM <TABLE> WHERE <FIELD>;).  That is valid in Access, but
does not seem to fly to well between the ODBC driver and Postgres.

Resolutions: Add primary keys (which should have been there anyway).
Check all forms to see if there is an SQL statement in the
RecordSource property.  I do not have "Bools as Char' checked and
everything (for now) seems to be running fine.

Thanks for the help!
Tim

On 8/23/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> "Timothy Reed" <tareed@gmail.com> wrote in message
> news:dc53e53005082204126f08654f@mail.gmail.com...
> > Yes I always relink the tables after an ODBC change (found that one
> > out the hardway).
> >
> > If the driver is configured to use Bools as Chars they show as Text.
> >
> > if it is configure to NOT use Bools as Chars they show as Yes/No
> >
> > I am using Jet for the time being, just trying to get the application
> > to work as-is for now.
>
> In principle, if your query references the values that are shown in your
> Access table, you should have no problem.  The "cache lookup failed" error
> is still mysterious.
>
> >
> > On 8/22/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> >> "Timothy Reed" <tareed@gmail.com> wrote in message
> >> news:dc53e530050819151357b5be95@mail.gmail.com...
> >> >I am having some issues when trying to query for booleans from Access
> >> > to Postgres 8.0.x.   If I change to use Bools as Chars then I am
> >> > unable to delete from a table with a boolean in it, as postgres cannot
> >> > convert from boolean to char.  If I do not use bools as chars then I
> >> > get  "cache lookup failed for function 0"
> >>
> >> When you made the change, did you relink the table(s) affected?  When you
> >> do, what datatype does Access show for that column?
> >>
> >> What query are you using to do the delete?  Is it a Jet or passthrough
> >> query?
> >>
> >> >
> >> > I have followed what the tech docs have recommended to do with adding
> >> > the new msaccessbool function and = operator
> >> > (http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)
> >> >
> >> > But to no avail!
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> >
> >> > ODBC Driver settings:
> >> > Disable Genetic Optimizer: YES
> >> > KSQO: YES
> >> > Recognize Unique Indexes: YES
> >> > Declare/Fetch : NO
> >> > Parse Statements: NO
> >> > Cancel As FreeStmt: NO
> >> > Unknown Sizes: Max
> >> > Text as LongVarChar: YES
> >> > Unknows as LongVarChar: NO
> >> > Bools as Char : NO
> >> > Read Only: NO
> >> > Show System Tables: NO
> >> > LF <-> CR/LF: YES
> >> > Updatable Cursors: NO
> >> > bytea as LO: NO
> >> > Row Versioning: NO
> >> > Disallow Premature: NO
> >> > True as -1: YES
> >> > Server side prepare: NO
> >> >
> >> >
> >> > Table Stucture:
> >> > Column Type
> >> > Employee ID integer
> >> > First Name text
> >> > Last Name text
> >> > Clocker boolean
> >> > Labor Type ID bigint
> >> > PC Handle text
> >> > On Payroll boolean
> >> > Address 1 text
> >> > Address 2 text
> >> > City text
> >> > State text
> >> > Zip Code text
> >> > Hire Date timestamp without time zone
> >> > Pay Type text
> >> > Status text
> >> > Pay Rate Old money
> >> > Social Security text
> >> > Phone Number text
> >> > Shift Start text
> >> > Shift End text
> >> > Sign Off boolean
> >> > Hire Date Temp timestamp without time zone
> >> > Stop Date Temp timestamp without time zone
> >> > Stop Date timestamp without time zone
> >> > Rehire Date timestamp without time zone
> >> > Continuous Date timestamp without time zone
> >> > Direct Labor boolean
> >> > Agency boolean
> >> > Active boolean
> >> > Agency2 boolean
> >> > Payroll ID bigint
> >> > EMail text
> >> > ADP Employee ID integer
> >> >
> >> >
> >> > SQL STATEMENT:
> >> >
> >> > SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
> >> > WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
> >> > ,"public"."Employees"."First Name";
> >> >
> >> >
> >> > RESULT:
> >> > ERROR:  cache lookup failed for function 0
> >> >
> >> >
> >> > TIA,
> >> >
> >> > --
> >> > --------
> >> > Timothy Reed
> >> >
> >> > tareed@gmail.com
> >> >
> >> > "I reject your reality, and substitue my own" - Adam Savage
> >> >
> >> > ---------------------------(end of
> >> > broadcast)---------------------------
> >> > TIP 3: Have you checked our extensive FAQ?
> >> >
> >> >               http://www.postgresql.org/docs/faq
> >> >
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: Have you checked our extensive FAQ?
> >>
> >>                http://www.postgresql.org/docs/faq
> >>
> >
> >
> > --
> > --------
> > Timothy Reed
> >
> > tareed@gmail.com
> >
> > "I reject your reality, and substitue my own" - Adam Savage
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
--------
Timothy Reed

tareed@gmail.com

"I reject your reality, and substitue my own" - Adam Savage