Connection limits and MS Access?

Server PostgreSQL 8.4 / 64 bit
Client MS Access 2007
ODBC driver 8.4.200

In order to support one of our clients, we've set up ODBC access to our
database server, and the contract specifies a single ODBC database connection.
We've enabled ODBC and gotten started, but Access seems to want to open a
database connection for each linked table - hardly beneficial!

We've tried enabling connection pooling for the PG ANSI driver (the one client
is trying to use) to no avail - as soon as we try to set up a 2nd linked
table, we get an error about connection limits.

Is there a way to force Access (or the ODBC driver) to reuse one connection
rather than opening multiple connections?

-Ben

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Connection limits and MS Access?

On Mon, Aug 16, 2010 at 12:07 PM, Benjamin Smith
wrote:

> We've enabled ODBC and gotten started, but Access seems to want to open a
> database connection for each linked table - hardly beneficial!

I'm not to sure if this can be caused by the odbc driver. It's job is
to open a connection when requested to do so.

I've opened up a thread on an MS-Access site to see if there is any
help that they can offer.

http://www.utteraccess.com/forum/Access-2007-Excessive-o-t19 50659.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Connection limits and MS Access?

Hi,

I have a similar setup, except the server is 8.3.11 / 32 bit Ubuntu
Hardy LTS. I use the 32-bit Unicode driver (version 08.04.0200) on a
64-bit Win7 laptop. I cannot reproduce the same behaviour. I'm
monitoring the connections in PgAdmin, and I see only one
connection/PID. If I open then close the linked table, the connection
persists. If I open another table, the same PID stays up. If I have
more than one table, I still see only one connection/PID. The
connection/PID is closed when I exit Access 2007. All as expected. As
far as I know, I haven't done any special modifications from the
default settings, except enable Row Versioning and Updatable Cursors
(Page 2 options).

-Mike

On 16 August 2010 12:07, Benjamin Smith wrote:
> Server PostgreSQL 8.4 / 64 bit
> Client MS Access 2007
> ODBC driver 8.4.200
>
> In order to support one of our clients, we've set up ODBC access to our
> database server, and the contract specifies a single ODBC database connection.
> We've enabled ODBC and gotten started, but Access seems to want to open a
> database connection for each linked table - hardly beneficial!
>
> We've tried enabling connection pooling for the PG ANSI driver (the one client
> is trying to use) to no avail - as soon as we try to set up a 2nd linked
> table, we get an error about connection limits.
>
> Is there a way to force Access (or the ODBC driver) to reuse one connection
> rather than opening multiple connections?
>
> -Ben
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Connection limits and MS Access?

Le 16/08/2010 21:07, Benjamin Smith a =E9crit :
> Server PostgreSQL 8.4 / 64 bit
> Client MS Access 2007
> ODBC driver 8.4.200
>
> In order to support one of our clients, we've set up ODBC access to our
> database server, and the contract specifies a single ODBC database conn=
ection.
> We've enabled ODBC and gotten started, but Access seems to want to open=
a
> database connection for each linked table - hardly beneficial!

We are running PostreSQL 8.4.3 32bit on Windows 2003.
Clients use PsqlODBC 8.04.0200.
I cannot reproduce this. I can open 10 linked tables (all are in the=20
same database) and leave them open, I only see one connection open on=20
the server.

We link the tables using some VBA DAO code (we've been doing it with ADO=20
also, works the same), not with a System DSN. Maybe this is where the=20
different behaviour comes from ?

Regards,
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Connection limits and MS Access?

On Monday, August 16, 2010 01:33:33 pm Mike Toews wrote:
I haven't done any special modifications from the
> default settings, except enable Row Versioning and Updatable Cursors
> (Page 2 options).

And, for some reason, checking these options fixed the problem. I haven't yet
had the time to figure out which of these two does the trick, but changing it
thusly does it! (Have incorporated screenshots thusly in our dox as a nice
contribution to long, tortured existence of Cargo Cult computing)

Thanks,

Ben

>
> -Mike
>
> On 16 August 2010 12:07, Benjamin Smith wrote:
> > Server PostgreSQL 8.4 / 64 bit
> > Client MS Access 2007
> > ODBC driver 8.4.200
> >
> > In order to support one of our clients, we've set up ODBC access to our
> > database server, and the contract specifies a single ODBC database
> > connection. We've enabled ODBC and gotten started, but Access seems to
> > want to open a database connection for each linked table - hardly
> > beneficial!
> >
> > We've tried enabling connection pooling for the PG ANSI driver (the one
> > client is trying to use) to no avail - as soon as we try to set up a 2nd
> > linked table, we get an error about connection limits.
> >
> > Is there a way to force Access (or the ODBC driver) to reuse one
> > connection rather than opening multiple connections?
> >
> > -Ben
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
> >
> >
> > --
> > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-odbc

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc