psqlodbc MSAccess and Postgresql

I'm currently building an Access FE to my PostgreSQL BE. So far it
works great. If you're going to be deploying this to multiple users,
I'd suggest using a DSN-less connection. That way you don't have to set
up a DSN on each user's PC.

Mike

_____

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Typing80wpm@aol.com
Sent: Friday, April 22, 2005 12:28 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] psqlodbc MSAccess and Postgresql

Thanks for the help with /dt... Mike, your post helped me to discover
psql command window, I had been trying out the pgAdmin ...

I found an interesting article regarding MSAccess as a front end to
Postgresql, so I downloaded the psqlodbc installer. I will be getting
up the nerve to run the install and go into windows odbc and install
that driver so I can use MSAccess with Postgresql ... any tips or
caveats appreciated....

This is the link which is guiding me....
http://database.sarang.net/database/postgres/postodbc/faq.html

<Typing80wpm@aol.com> wrote in message news:19f.324000cd.2f99d761@aol.com...

Thanks for the help with /dt... Mike, your post helped me to discover psql
command window, I had been trying out the pgAdmin ...

I found an interesting article regarding MSAccess as a front end to
Postgresql, so I downloaded the psqlodbc installer. I will be getting up
the nerve
to run the install and go into windows odbc and install that driver so I
can
use MSAccess with Postgresql ... any tips or caveats appreciated....

Be sure to check out the FAQs and How-tos at the psqlodbc project site:
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php

You will find that that Access and PostgreSQL get on well together. You can
get by with the default driver settings for the most part; I suggest that
you make sure that the "row versioning" and "true = -1" options are set to
true. If you want to use the "memo" datatype in Access, then you will need
to check "text = longvarchar" option.

If you have further questions, try posting to the pgsql-odbc list.

I know that it sounds crazy,
but I need a bigserial coulumn in a view that is consisted of several tables.
That column should not be based on bigserial column of any table, but should be a calculated column...
How can I accomplish it ?

Thanks in advance.

Zlatko

Hello,

I am currently migrating my MSDE/Access (Access Project) aplication to PostgreSQL.
I have experienced a lot of obstacles till now, but anyway it seems quite posible to make a good aplication by this combination of Access front-end and PostgreSQL base.
I use the following ODBC settings for linked tables successfully:
[ODBC]
DRIVER=PostgreSQL
UID=zmatic
UseServerSidePrepare=0
ByteaAsLongVarBinary=0
BI=0
TrueIsMinus1=1
DisallowPremature=0
UpdatableCursors=1
LFConversion=1
ExtraSysTablePrefixes=dd_
CancelAsFreeStmt=0
Parse=1
BoolsAsChar=1
UnknownsAsLongVarchar=0
TextAsLongVarchar=1
UseDeclareFetch=1
Ksqo=1
Optimizer=1
CommLog=0
Debug=0
MaxLongVarcharSize=8190
MaxVarcharSize=254
UnknownSizes=0
Socket=4096
Fetch=100
ConnSettings=CLIENT%5fENCODING%3dWIN1250
ShowSystemTables=0
RowVersioning=1
ShowOidColumn=0
FakeOidIndex=0
Protocol=6.4
ReadOnly=0
PORT=5432
SERVER=localhost
DATABASE=MyDatabase

Tips and tricks:
1. Disable "Recognize Unique Indexes" every time you link tables. That will allow you to give proper primary keys to Access (you will be prompted). Otherwise, Access do it quite bad.
2- Use Row Versioning
3. Every table must have numeric primary key. Don't use text field as primary key. Access will be confused and you will have "#DELETED#" in your tables.
4. Instead of having JET queries on linked tables, it is better to have good pass-through query. But it doesn't accept parameters, so you will have to combine regular JEt queries with pass-through queries. I use pass-through queries and server-side functions (for calculated columns) that prepare recordset and then filtrate it by regular JET query additionaly (for example: start and end date)...
Other possibility is to use server views linked as tables in Access...

Greetings

Zlatko
----- Original Message -----
From: Typing80wpm@aol.com
To: pgsql-general@postgresql.org
Sent: Friday, April 22, 2005 6:28 AM
Subject: [GENERAL] psqlodbc MSAccess and Postgresql

Thanks for the help with /dt... Mike, your post helped me to discover psql command window, I had been trying out the pgAdmin ...

I found an interesting article regarding MSAccess as a front end to Postgresql, so I downloaded the psqlodbc installer. I will be getting up the nerve to run the install and go into windows odbc and install that driver so I can use MSAccess with Postgresql ... any tips or caveats appreciated....

This is the link which is guiding me....
http://database.sarang.net/database/postgres/postodbc/faq.html

On Sun, Apr 24, 2005 at 22:16:14 +0200,
Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

I know that it sounds crazy,
but I need a bigserial coulumn in a view that is consisted of several tables.
That column should not be based on bigserial column of any table, but should be a calculated column...
How can I accomplish it ?

This doesn't really make sense. It sounds like you want a key that is unique
accross the union of several tables and that doesn't change when other rows
in the union are added or removed. There are a couple of ways you might
go about doing something like this. Is this what you really want?

Please keep replies copied to the list so that other people can learn from
and comment on the discussion unless to have a good reason to make the
thread private.

On Mon, Apr 25, 2005 at 21:46:20 +0200,
Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

The reason for such crazy idea is my front-end MS Access which considers
views as tables and have problem with tables ( views also) if there is no
unique numeric field...
Anyway, how to do it ?
Thanks.

I don't know MS Access, so I am not going to be able to help much with this.
There may be some way to trick MS Access into thinking that your view has
a unique numeric field. It would help to know how it knows that a column
has those properties and how it uses the information.

"Bruno Wolff III" <bruno@wolff.to> wrote in message
news:20050425201046.GA16773@wolff.to...

Please keep replies copied to the list so that other people can learn from
and comment on the discussion unless to have a good reason to make the
thread private.

On Mon, Apr 25, 2005 at 21:46:20 +0200,
Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

The reason for such crazy idea is my front-end MS Access which considers
views as tables and have problem with tables ( views also) if there is no
unique numeric field...
Anyway, how to do it ?

You can tell Access to use any combination of fields as a primary key, when
linking to the view. There must be some unique combination that will work
as a key. But this is really of no significance unless you want to issue
inserts or updates against the view, which raises another set of issues.

If you want a unique key across several tables, can you not do something
like:

CREATE SEQUENCE detail_seq INCREMENT BY 1;

CREATE TABLE table1 (
table1_id INTEGER PRIMARY KEY DEFAULT nextval('detail_seq'),
item1_name VARCHAR(100) NOT NULL
);

CREATE TABLE table2 (
table2_id INTEGER PRIMARY KEY DEFAULT nextval('detail_seq'),
item2_name VARCHAR(100) NOT NULL
);

Then the union of both tables will contain one unique set of ids.

Assuming that is what you are trying to do, that is.

Susan

----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------