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