?
Solved

ODBC Oracle connection loses connectivity overnight

Posted on 2009-02-10
5
Medium Priority
?
632 Views
Last Modified: 2012-05-06
Hi this is a non urgent question but one which has foxed me for a long time...

I'm maintaining a certain number of Access databases for a department in a large company.  Most of these databases link to Oracle via ODBC using Oracle in OraHome 9201 or 901.  What sometimes happens is that when these databases are left on overnight, the next day when the user attempts some kind of processing the user gets a connectivity problem and attempting to reconnect the Oracle tables either via code or the 'get external data' feaure in Access results in some kind of access denied error.  This seems especially true if the account is read/write.  Even recreating the DSN has no effect, and the only cure seems to be to re-boot the PC.

Non-ODBC applications e.g. TOAD remain unaffected.

My theory is that because the Oracle database undergoes data import overnight it locks out read-write accounts and somehow ODBC doesn't have the nounce to realise that the lock has been released ... but I'm just clutching at straws.  Does anyone else have any experience of this phenomenon or have any explanation why it occurs or how it may be circumvented ?
0
Comment
Question by:Natchiket
  • 3
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
dds110 earned 1500 total points
ID: 23600278
I have no experience with the phenom or no explanation to give.  But maybe you could circumvent by setting a form timer to close the access application about 10 minutes before the data import takes place.  I used to support a company with several large DB's and I feel your pain about the users leaving the DB's open.

HTH
0
 
LVL 17

Author Comment

by:Natchiket
ID: 23600471
yeah i was thinking about doing that ...  although I'm not sure the data load occurs (the oracle db is a data warehouse).  Some people seem to have an attitude about rebooting their PCs ... and the coporate policy is to keep them running overnight ... daft imo
0
 
LVL 8

Expert Comment

by:dds110
ID: 23600557
Hmmmmm, I wouldn't write anything to reboot the computers.  Just shut down the Access DB's.  If you don't want to shut down the DB's then you could write something to "disconnect" the db's from oracle while the import takes place.

Just my thoughts.
0
 
LVL 17

Author Comment

by:Natchiket
ID: 23620517
Time to wrap this one up.  Just for the record dds110 when I said people had an attitude towards rebooting PCs I meant rebooting them normally, I wasn't intending for my application to do it.
0
 
LVL 8

Expert Comment

by:dds110
ID: 23621783
I wish I could've been more help to you.  Good luck with your issue.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question