Re: Help with the big picture

Brad Paul,

> I'm very new to data bases (last week was day one). My goal is to put
> our data base in PostgreSQL on a Linux computer and have MS Access be
> the front end for taking orders etc. (I never used Access ether but
> our new office manager seems quit good at it.)

Please see Techdocs: http://techdocs.postgresql.org for lots of helpful
advice. Particularly, there is a Book Review page with several good
reccomendations for database and PostgreSQL books. For example,
"Database Design for Mere Mortals".

> I have made a view for viewing the employee data with address
> information and a rule that allows me to insert data into the view. I
> have gotten this to work from the command line in psql. However I can
> not insert data into the view with MS Access. I can insert data into
> the real tables with MS Access.

This may be a limitation of MS Access, or of ODBC on Windows. I know
that I cannot use many kinds of defaults when using MS Access as a
front end; Access seems to insist on inserting a NULL. There are two
mailing lists that might help you:
pgsql-odbc(at)postgresql(dot)org
and for http://pgadmin.postgresql.org

> 1) Should I not use views and try to only update tables with Access?
> I
> have tried this but then I was unable to get the links in Access to
> do
> what they should. I should not have to manually find the new
> address_id
> to place it in the employee table when adding a new employee.
> (However, if
> the x-mass season does not go well we will never have a real example
> of this, but it would be a good thing to know for my next job.)

Well, if you're looking for "shoulds", I'd say the first "should" is
"should not use MS Access as an interface." I spent 5 years
developing applications based on MS Access and Visual Basic; they are
all high-maintainence and buggy as all-get-out, except for the
smallest, simplest applications. MS Access is just an unstable, quirky
platform. Mind you, some of these applications are still paying me
because they require weekly support just to keep running ...

I realize that you probably don't have a choice. Just wanted to let
you know -- it's not you, it's the software.

You could try Borland's Delphi suite, or one of the advanced Java IDEs.
Sadly, Postgres' accompanying inteface developement kit, PGAccess, is
not quite ready for you to build an application out of it. You should
tinker with it a bit and try, though, it might be better than Access:
http://www.pgaccess.org/

Or you could learn Zope and build and intranet-based application. I'm
told that Zope is impressively developer friendly.

That being said, if Access is preventing you from updating the views,
you would have to learn a little Visual Basic and write some code
attached to the employees form to update both tables.

> 2) How do I get Access to be able to add data to a view, that has a
> rule that works in psql?

Petition Microsoft to fix their buggy software? <grin> good luck.
Work around it, or post your questions on the lists I mention above.

> 3) Am I missing something big?

No. What you want to do *is* the better way to do things ... it's
just that your software (MS Access) doesn't support it.

> 4) How do I deal with removing employees? (A more realistic event.) I
> will need to check if the employees address is referenced by any
> other
> table. (i.e. maybe the employees wife is also a customer and they
> live
> together.) Should I try and make rules to do this or learn how to
> write
> functions?

Writing rules and functions is very similar. It shouldn't be hard.
I suggest that you write a "before" trigger for the employees table.
It may seem daunting at first, but you can easily get through it with
some help from books.

-Josh Berkus