×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access and PostgreSQL Comments
2

Access and PostgreSQL Comments

Access and PostgreSQL Comments

(OP)
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

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

(OP)
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

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

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/)

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

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?!?!

RE: Access and PostgreSQL Comments

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.

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

(OP)
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

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

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.

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

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.

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

try

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

RE: Access and PostgreSQL Comments

(OP)
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

RE: Access and PostgreSQL Comments

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

RE: Access and PostgreSQL Comments

(OP)
Paulmac,

Great!  Actually, I got that solution from another web site.  

Well, I am not an expert on encoding types.  But, I intend on paying more attention to it soon.   When you have a solution, please do post it!  

Gary
gwinn7

RE: Access and PostgreSQL Comments

(OP)
Navicat - Mini-Review

Just to let everyone know, I purchased Navicat Enterprise for Postgres and I love it!  Importing my large MS Access databases was a snap!   It is very intuitive and easy to use without the headache.

The only draw back I found so far is that I can't Export multiple tables simultaneously back to a single MS Access MDB.     That was very annoying, but I have a workaround.   Navicat support confirmed this limitation and hopefully will  remedy this in a future build.

Anyhow, if you have questions about Navicat, I can most certainly try to help.

Gary
gwinn7

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close