Re: Access 2002/PostgreSQL ODBC Inactivity

Thanks Janet. I'm sorry, I should have mentioned that.

I'm using the latest MDAC 2.7 and Jet 4.0 SP6 drivers on all the different
OS's.

Inexplicably, the SP6 version of Jet 4.0 is not listed on Microsoft's Data
Access page (at least I can't find it). Only SP3 is listed. Here is the link
to Jet 4.0 SP6 for Access 2002:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q282010

-Alan

-----Original Message-----
From: Janet Borschowa [mailto:borschow(at)roguewave(dot)com]
Sent: Friday, October 04, 2002 5:44 PM
To: 'Alan R. Johns'; 'pgsql-odbc(at)postgresql(dot)org'
Subject: RE: [ODBC] Access 2002/PostgreSQL ODBC Inactivity

Hi Alan,
You might try looking at the versions of MDAC on the machines where you've
seen it crash and perhaps check MS's website for upgrades, known bugs, etc.
I've seen problems with odbc and sql server and the jet dll's (all part of
MDAC) in the past so perhaps you'll see different behavior with a different
version of MDAC.

good luck!

-- Janet

-----Original Message-----
From: Alan R. Johns [mailto:alan(at)graphicsuniversal(dot)com]
Sent: Friday, October 04, 2002 10:07 AM
To: pgsql-odbc(at)postgresql(dot)org
Subject: [ODBC] Access 2002/PostgreSQL ODBC Inactivity

This issue has been discussed briefly before: Using Access 2002 with
PostgreSQL ODBC v7.2.3 (or any version for that matter) after a period of
inactivity, Access will crash with either:

"Faulting application msaccess.exe, version 10.0.4302.0, faulting module
unknown, version 0.0.0.0, fault address 0x0000176d."

OR

When you try to close the forms you'll get an endless "This action will
reset the current code in break mode.". The only way out of Access is to
terminate the process.

I've been continuing to research this problem. I found some messages from
people using MySQL ODBC having the exact same problem. One guy suggested
changing the ODBC connection timeout in the registry:

Go to :
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]

Set:
ConnectionTimeout = 0

0 means never disconnect. I know this is probably not a recommended setting,
but it has solved my problem thus far.

The default registry setting is 600 (10 minutes). I experimented setting it
to 10 seconds and turning on CommLog and Mylog in the ODBC driver. After
approximately one minute of inactivity, I was almost guaranteed Access would
crash. This seems to be the key to what causes Access 2002 to crash. When
Access drops the connection with the server and then tries to reestablish it
(when activity resumes), it inevitably crashes. I've attached a small
portion of the commlog, but it really just shows nothing more than the ODBC
driver reestablishing the connection and returning 10 rows. MyLog shows
nothing out of the ordinary either.

Another possible solution is to lower the ODBC Refresh Interval
(Options-Advanced) to a number lower than the registry's connect timeout.
However, I have STILL had Access crash on me in this situation.

Obviously, Access is the problem here. I'm not suggesting there is anything
wrong with the PostgreSQL ODBC driver. I'm just trying to provide some
information for others who may run into this problem.

BTW:
I'm using the latest Office XP SP2 for Access. The OS doesn't seem to
matter. I've seen it crash on Win98, 2000 SP2, and XP SP1.

Access 2000 does not seem to have this problem at all. I'd be happy to
downgrade to 2000, but I've already created my app with many of the enhanced
printing functions in 2002.

Anyone else have any other suggestions? (Besides getting rid of Access <G>)

-Alan