Thread: psqlodbc MSAccess and Postgresql

psqlodbc MSAccess and Postgresql

From
Typing80wpm@aol.com
Date:
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....
 
 

Re: psqlodbc MSAccess and Postgresql

From
"Relyea, Mike"
Date:
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....
 
 

Re: psqlodbc MSAccess and Postgresql

From
"Jeff Eckermann"
Date:
<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.

>
> This is the link which is guiding me....
> _http://database.sarang.net/database/postgres/postodbc/faq.html_
> (http://database.sarang.net/database/postgres/postodbc/faq.html)
>
>
>



Calculated bigserial column in a view

From
"Zlatko Matic"
Date:
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

Re: psqlodbc MSAccess and Postgresql

From
"Zlatko Matic"
Date:
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 -----
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....
 
 

Re: Calculated bigserial column in a view

From
Bruno Wolff III
Date:
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?

Re: Calculated bigserial column in a view

From
Bruno Wolff III
Date:
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.

>
> ----- Original Message -----
> From: "Bruno Wolff III" <bruno@wolff.to>
> To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
> Cc: <pgsql-general@postgresql.org>
> Sent: Monday, April 25, 2005 6:31 AM
> Subject: Re: [GENERAL] Calculated bigserial column in a view
>
>
> >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?
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >              http://www.postgresql.org/docs/faq
> >
>

Re: Calculated bigserial column in a view

From
"Jeff Eckermann"
Date:
"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.

>> 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.
>
>>
>> ----- Original Message -----
>> From: "Bruno Wolff III" <bruno@wolff.to>
>> To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
>> Cc: <pgsql-general@postgresql.org>
>> Sent: Monday, April 25, 2005 6:31 AM
>> Subject: Re: [GENERAL] Calculated bigserial column in a view
>>
>>
>> >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?
>> >
>> >---------------------------(end of broadcast)---------------------------
>> >TIP 5: Have you checked our extensive FAQ?
>> >
>> >              http://www.postgresql.org/docs/faq
>> >
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>