Thread: Using MS Access front-end with PG

I've got an MS Access front end reporting system that has previously
used MS SQL server which I am moving to Postgres.

The front end has several hundred if not thousand inbuilt/hard-coded
queries, most of which aren't working for the following reasons:
1.) Access uses double quotes ("") as text qualifiers, PG uses single
quotes. ('')
2.) The Like function in SQL Server is case insensitive, PG it is case
sensitive. The ilike function is not recognised by Access and it tries
to turn that into a string, making my test (like "ilike 'blah'")

Has anyone had any experience with moving an access program from SQL
server to PG?

Is there any way to change the text qualifier in PG or the case sensitivity?

TIA,
P.

--
Paul Lambert
Database Administrator
AutoLedgers

Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> Is there any way to change the text qualifier in PG

No.  I suppose you could hack the Postgres lexer but you'd break
pretty much absolutely everything other than your Access code.

> or the case sensitivity?

That could be attacked in a few ways, depending on whether you want
all text comparisons to be case-insensitive or only some (and if so
which "some").  But it sounds like MS SQL's backward standards for
strings vs identifiers has got you nicely locked in, as intended :-(
so there may be no point in discussing further.

            regards, tom lane

Paul Lambert wrote:
> I've got an MS Access front end reporting system that has previously
> used MS SQL server which I am moving to Postgres.
>
> The front end has several hundred if not thousand inbuilt/hard-coded
> queries, most of which aren't working for the following reasons:
> 1.) Access uses double quotes ("") as text qualifiers, PG uses single
> quotes. ('')
> 2.) The Like function in SQL Server is case insensitive, PG it is case
> sensitive. The ilike function is not recognised by Access and it tries
> to turn that into a string, making my test (like "ilike 'blah'")
>
> Has anyone had any experience with moving an access program from SQL
> server to PG?
>
> Is there any way to change the text qualifier in PG or the case
> sensitivity?

I would suggest pushing things like this to a pass through query.

Joshua D. Drake

>
> TIA,
> P.
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Paul Lambert wrote:
> I've got an MS Access front end reporting system that has previously
> used MS SQL server which I am moving to Postgres.

Are you using "PassThrough" queries?  It is not clear....
>
> The front end has several hundred if not thousand inbuilt/hard-coded
> queries, most of which aren't working for the following reasons:
> 1.) Access uses double quotes ("") as text qualifiers, PG uses single
> quotes. ('')

What Access uses should not make any  difference.  In pass through
queries I as MS-SQL uses single quotes same as PostGres, and "attached
table" queries all this gets transalated at the Access to ODBC layer.
>
> 2.) The Like function in SQL Server is case insensitive, PG it is case
> sensitive. The ilike function is not recognised by Access and it tries
> to turn that into a string, making my test (like "ilike 'blah'")

The only way the "ilike" can be passed from ACCESS to Postgres is
through pass through queries.  This is probably not what you want
though.  I do not actually know how MS-Access translates the "Like"
operator at the ACCESS-> ODBC layer (probably just converts the search
string to use % and _ from * and ?).  I do not know if it is possible to
switch off case sensitivity in Postgres though
>
>
> Has anyone had any experience with moving an access program from SQL
> server to PG?
>
Yes, but some time ago.  I did not have the case sensitivity problem as
I knew that was a non-standard feature and did not rely on it while
developing the MS-SQL solution though (just call me smartypants :-)).  I
still had to change a few things though (I cannot remember what, sorry).

Eddy

Paul Lambert wrote:
> I've got an MS Access front end reporting system that has previously
> used MS SQL server which I am moving to Postgres.
>
> The front end has several hundred if not thousand inbuilt/hard-coded
> queries, most of which aren't working for the following reasons:
> 1.) Access uses double quotes ("") as text qualifiers, PG uses single
> quotes. ('')

Ignore point one in my op, it wasn't the double quotes causing the
problem and was a quick and easy fix.

Thanks,
P.

--
Paul Lambert
Database Administrator
AutoLedgers

> 2.) The Like function in SQL Server is case insensitive, PG it is case
> sensitive. The ilike function is not recognised by Access and it tries
> to turn that into a string, making my test (like "ilike 'blah'")
>
> Has anyone had any experience with moving an access program from SQL
> server to PG?
>
> Is there any way to change the text qualifier in PG or the case sensitivity?

I wonder if this would be a good feature to request from the ODBC developers by adding a parameter
to the drivers to use ilike instead of like.

Regards,
Richard Broersma Jr.