Re: psqlodbc MSAccess and Postgresql

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(at)aol(dot)com
To: pgsql-general(at)postgresql(dot)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