Skip site navigation (1) Skip section navigation (2)

Re: ODBC Driver Failure- MS Access- Large record volume

From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Robin Weber <rweber(at)pmc(dot)ucsc(dot)edu>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: ODBC Driver Failure- MS Access- Large record volume
Date: 2005-09-08 21:29:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
Hello and welcome Robin,
I find the key to getting help is giving enough information.

You might answer a few questions.
1. What version of the ODBC driver are you using? (I found my  version using REGEDIT to key 
2. What version of Access are you using?
3. What Access library are you using -DAO or ADO?
4. Since Access is typically interactive, how are you transacting these millions of records, reading a 
file or is some external device driving Access?
5. Are you connecting though the Access objects (normal queries and tables), or using pass-through?
6. What version of Postgres server are you using? Linux or Windows?
7. Is logging turned on at the server? (e.g. pg_ctl start -l my_pg.log)

By the way other list memebers, after installation is there a recommended way to determine the pgODBC 
driver version?

Before knowing any of these answers I can say
the  ADO Connection object is your means of "opening and closing" the ODBC (or OLE/DB) driver. Open and 
close often is the rule of thumb for interacting with server database. There is typically caching going on 
for recent connections, so the opening a new connection is not the resource hog it might seem. If all that 
is ok, you may indeed have a pgODBC  driver version with a leak.

And as a complete aside because it has bitten me too many times, set the Jet ConnectionTimeout under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\ to 0 (never timeout). A jet connection 
(linked table) will loose connectivity after a default time of 60 minutes of inactivity unless this key is 
set to some other value (longer or shorter) or 0.

Refreshing linked tables is also do-able. You should be able to google it or write back and someone will 
no doubt expound upon it. The Access (version) Handbook's from Sybex are excellent references for the 
mundane to the arcane.

Robin Weber wrote:

> Hello Community,
> I'm new to this list.
> I am a user of the Postgres ODBC driver, mainly under MS Access and 
> Matlab.  We deal in very large tabular datasets.  (My largest table is 
> currently 72 million records, and it will grow several times that size 
> before the year is out.
> The driver fails after several million record inserts (under access).  
> Once failed (with nothing more helpful tan an "ODBC call failed" message 
> box), it will process no further requests.  (I believe it will also fail 
> after receiving that much data, as well a sending.)
> (I would think of a memory leak of some sort, but in my 27 pro years of 
> programming, I have never dealt with the inner workings of an ODBC 
> driver, and I don't know how to get driver error information.  This is 
> also my first open source experience, so please forgive my naivete.)
> I would like to figure out how to fix this problem.  I'm willing to help 
> debug, but I need help getting started.  How do I go about trying to 
> understand this problem?
> One thing that might be helpful:  Is there a programmatic way to get 
> Access to close and unload, then reopen the ODBC driver without actually 
> quitting Access?  I notice that when I start Access "fresh" I can pump 
> another several million records through before failing.  If I can make 
> my programs unload then reload the driver every few million records, it 
> might make a good workaround for my problem.  Any clues out there?
> With thanks,
> Robin
> --------------------------------------------------
> Robin Weber
> Data Manager and Application Developer for
> Marine Mammals and Birds
> Tagging of Pacific Pelagics Project
> University of California Santa Cruz
> Long Marine Lab - Center for Ocean Health
> 100 Shaffer Road
> Santa Cruz, CA 95060-5730
> (831) 459-2691
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)

In response to


pgsql-odbc by date

Next:From: Dave PageDate: 2005-09-08 22:07:17
Subject: Re: ODBC Driver Failure- MS Access- Large record volume
Previous:From: Robin WeberDate: 2005-09-08 20:21:56
Subject: ODBC Driver Failure- MS Access- Large record volume

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group