Обсуждение: 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.
On Mon, Aug 16, 2010 at 12:07 PM, Benjamin Smith <lists@benjamindsmith.com> 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-t1950659.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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 <lists@benjamindsmith.com> 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 >
Le 16/08/2010 21:07, Benjamin Smith a écrit : > 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 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 same database) and leave them open, I only see one connection open on the server. We link the tables using some VBA DAO code (we've been doing it with ADO also, works the same), not with a System DSN. Maybe this is where the different behaviour comes from ? Regards, Arnaud Lesauvage
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 <lists@benjamindsmith.com> 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.