Dropped Back-end Oracle Sessions In Ms Access, Access 2003 (1 Viewer)

[whdyck]

Hoping someone can help.

We have an MS Access app that connects to an Oracle back-end. After about 10 minutes of inactivity, MS Access's connection to Oracle gets dropped (as per TOAD's Session Browser); a few minutes later, a form (opened when starting the app) looks like the attached.

If I open an Oracle session in TOAD, the session remains intact until I explicitly drop it or close TOAD. But the MS Access connection to Oracle is somehow different.

Anyone know why this is happening in Access?

Thanks for any help you can give.

(Cross-posted to UtterAccess.com)

Wayne

 

[jdraw]

Wayne,

I have no experience with your specific set up, but I don't think Access has any native inactivity timer functionality.
I found this via Google: on Bytes

As long as any form is still open the timer facility can be made available to run code after a certain period of inactivity. Inactivity would have to be defined in the code though, as I don't believe Access provides anything built-in. It would mean every action within the project would have to log activity when it executed, thereby resetting any timer.

Further searching looking at ODBC as a possible culprit, I found this link

Good luck.

 

[whdyck]

Further searching looking at ODBC as a possible culprit, I found this link

The link suggests this might be an ODBC issue/setting. I tried disabling the "Enable Query Timeout" in ODBC. No joy.

Wayne

 

[The_Doc_Man]

As a crazy solution, if you are keeping a connection open for the purpose of preventing Access from having to open and close connections, see if there is a form you can keep open (even if minimized and not visible) into which you can put an OnTimer event to query something silly, or perhaps to update a login table with last activity. If the problem is a ten-minute time-out, do a nine-minute benign query on a timer.

 

[whdyck]

SOLVED: Dropped Back-end Oracle Sessions In Ms Access, Access 2003

As a crazy solution

I tried that, and it does seem to work!

However, I also found another way:
Remove the PK column specification from the linked table in Access.

The form's RecordSource is an Oracle view, to which we have an Access table link. Our standard practice is to specify a PK in the Access table link so that Access will be able to update the underlying data when necessary. However, since this form is read-only, Access doesn't need to have a PK for uniqueness. After opening the linked table in design view and ensuring there was no PK defined, the problem went away.

Go figure.