Access and PostgreSQL Comments 2

[gwinn7]

After reading several recent posts today, I am pleasantly surprised that I am not the only one converting their MS Access applications to a PostgreSQL backend. It is definitely a great, stable, low cost solution to take and breath more life into an Access application until a better front-end can be developed.

It seems that linking Access to Postgres is relatively new territory as I can find only a few references out there about it. As I have had a few months experience now rebuilding our Access application around Postgres, I have had to resolve a number of issues, as I am sure you may have too.

Please be encouraged to post your questions, experiences, and solutions. If I can be of help in some way, I will certainly try to help, if I can.

The more I work with PostgreSQL the more I love its capabilities and the direction the product is going. It is a great way to bring a medium sized application, like Access, into the big leagues nearly on par with MS SQL Server and Oracle, without the obligation of licensing/fees etc.

If you are considering moving an application to PostgreSQL, I would definitely give it a good recommendation so far. The PostgreSQL product, even on Windows, is definitely showing signs of its worth. It's performance when querying records from our poorly designed database is fantastic. The newest psqlODBC driver is hugely improved on performance.

There are longer term plans to move our redesigned version to PostgreSQL on FreeBSD, or Linux. We hope to leverage the opensource technologies like PHP, etc as we move forward. In the meantime, this is just a step forward for us.

Thanks and thank you for your posts,
Gary
gwinn7

 

[WonderCow]

Hey dude,

Although Postgres is a good solution - you must be careful with anyone who has access to the backend - If you try to set a primary key to nextval('schemaname.sequence'::text) (autoincrement...) users can insert whatever value they wish, I have not been able to keep it strict.

If the seq is up to 50 (eg) and a user inserts their own value of 75, when the sequence gets to 75, postgres goes nuts until you change the minvalue of the seq to 76.

A real pain in the proverbial to avoid if possible.

My experiance of Access/properDB Backend has been more pleasant with MySQL - maybe worth a look into...

Hope the transition is smooth

WonderCow

 

[gwinn7]

Hi,

We already considered and rejected MySQL as an option. We need the advanced features of Postgres, which MySQL just doesn't have yet.

You make a good point about the sequences. I believe this problem can easily be solved by using table rules, or a combination of rules and views. Using views is likely a good option anyhow as you can shield your backend tables much better this way anyhow.

Thanks WonderCow, you response is much appreciated!

Gary
gwinn7

 

[WonderCow]

No problems - although there is an itching feeling in my head that there are a few like that one, but they're not coming to mind. If i recall them, I'll let you know.

WonderCow

 

[ceco]

the problem with sequences is not a postgresql problem! every decent DB is working the same way ...

you can fix this by using rules or triggers (or just don't allow users to alter that value)

(mysql has much more gotchas

http://sql-info.de/)

 

[WonderCow]

This is not an issue at all, if no unexperienced users are allowed access to the backend (unfortunately we have one in our company

)

 

[ceco]

then this only one person should just read a little documentation and you will not have problems with postgesql sequences (and everything else)

sorry for the offtopic :-( but

in mysql you can do the following

mysql> create table test (test numeric(4,2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test (test) values (10000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+
| test |
+--------+
| 999.99 |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------------------------+
| version() |
+-----------------------------+
| 4.0.24_Debian-10ubuntu1-log |
+-----------------------------+
1 row in set (0.00 sec)

do you find it reasonable?!?!

 

[WonderCow]

well, it doesn't seem reasonable, no - but I can't say I've had those issues with MySQL before.

Apparently there are plans to make a strict casting switch - I assume that this would stop the problem, as it wouldn't be able to cast the value.

 

[flugh]

We can all sit here and debate PostgreSQL vs. MySQL all day, so I won't bother putting my .02 in there (we all know postgresql p0wnz mysql anyway, bleh ;-))

I'd like to suggest following the postgresql-odbc mailing list (postgresql-general is excellent too). You can find it at

http://archives.postgresql.org/pgsql-odbc/

I'm using Access 2000 on both WinXP Home and Pro (service pack 2 on both), and postgresql treats it sanely in both cases. The only gotcha I can suggest so far is make your primary key an integer if you can. Not sure why, but if I make my char(12) a primary key, I get '#DELETED#' if I open the linked table from Access.

My 'if I ever get a few weeks peace and quiet and can turn off the cell phone' project is to convert my Access front-end to Mono using gtk (I'm pretty sure it works on windows now?) so that I have a single codebase for cross-platform use at the office.

Not sure if any of that is even remotely interesting or useful to you, but I've typed too much to dispose of it. You aren't alone

----
JBR

 

[gwinn7]

flugh,

Thank you for your post.

I encountered the same problem and resolved it the same way. However, I should also add that when running a query with a join on a table that does not have a numeric primary key with one that does, the query should able to be viewed just fine, if memory serves.

Gary

 

[WonderCow]

has the link table got the correct details for the field, notices pk, gets correct data type,etc?

 

[WonderCow]

That's the other problem with postgres - the pg_dump app is screwed when it comes to views - back them up separately (I am working on a solution at the moment - if it works, I'll give you the shout).

The problem being that if a view is dependant on another view, the dump app ignores this, but then fails to create it. I'm making a simple perl script to automate the process, but it's one to look out for.

 

[ceco]

what version of postgesql do you use (about the pg_dump)

and what problem do you have with views? a problem may exist in previous version of pg, when circular references/dependencies exist, when there is no circularity I have never experienced a problem (with 8.0 even then there is no problem) give an example

 

[WonderCow]

7.4 and all of a set of views that were created (mostly interdependant) cannot be recreated - they must be done by hand after a restore.

 

[gwinn7]

Some time ago, I ran across a link that details a lot of Access to PostgreSQL issues and concerns. Very very good link...

http://techdocs.postgresql.org/v2/Guides/Using Microsoft Access with PostgreSQL/#68

Sorry for the link length.

Gary
gwinn7

 

[kevincat]

To migrate Access to PostgreSQL, I would suggest below tool which provide an intuitive interface and helpful wizards to take you through the whole migration process.

http://pgsql.navicat.com

You can download a trial version at:

http://pgsql.navicat.com/download.html

Kevin

 

[paulmac172]

I have migrated tables from a MS Access database into Postgresql 8.1.3 on a remote windowsxp pc. I have installed the latest postgresql ODBC driver on both PCs and have linked the tables from Postgresql back into MS Access.
It all seems to work except for queries that test booleans eg

SELECT fielda, fieldb, fieldc
FROM table
WHERE table.booleanfield = false;

This returns an error message "ODBC--call failed ERROR: operator does not exist: boolean = integer (#7)
If I setup a similar query/view on postgres via pgAdminIII, it works ok. This seems like an ODBC driver issue.
I've also tried 0 instead of False, makes no difference.

Do you have any suggestions?

Thanks

Paul

 

[ceco]

try

SELECT fielda, fieldb, fieldc
FROM table
WHERE NOT table.booleanfield;

 

[gwinn7]

I am not sure how relevant this is but did you update your PostgreSQL database system to handle MS Access Booleans?

Here is a script for this...

Step 1 - add this statement to your Postgres

CREATE OPERATOR <>

Step 2 -

DROP OPERATOR = (bool, int4);
DROP OPERATOR <> (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
DROP FUNCTION MsAccessBoolEq (bool, int4);
DROP FUNCTION MsAccessBoolNeq (bool, int4);

CREATE FUNCTION MsAccessBoolEq (bool, int4) RETURNS BOOL AS '
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;

IF $1 IS TRUE THEN
IF $2 <> 0 THEN
RETURN TRUE;
END IF;
ELSE
IF $2 = 0 THEN
RETURN TRUE;
END IF;
END IF;
RETURN FALSE;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION MsAccessBoolNeq (bool, int4) RETURNS BOOL AS '
BEGIN
RETURN NOT MsAccessBoolEq($1, $2);
END;

' LANGUAGE 'plpgsql';

CREATE OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBoolEq,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);


CREATE OPERATOR <> (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBoolNeq,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);

Hope that helps!

Gary
gwinn7

 

[paulmac172]

Many thanks gwinn7,
Talk about co-incidence, only half an hour ago I found a similar solution at

http://archives.postgresql.org/pgsql-odbc/2004-01/msg00041.php

This solves the problem.
I now have another problem when I attempt to backup my database it errors part way through with a message pg_dump: ERROR: could not convert UTF8 character 0x00e2 to ISO8859-1
This seems to happen because my Database is encoded in UTF8 and client coding is set for Latin1.

Any suggestions?

Thanks

Paul