Re: Using MS Access front-end with PG

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