[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How do I keep the Pervasive tables linked via ODBC from making my Access databases crash?

Posted on 2007-12-05
2
Medium Priority
?
316 Views
Last Modified: 2013-11-29
We have implemented Sage/Timberline software that runs on a Pervasive db platform.  We have several Access databases that cull information from our old system, Intuit Master Builder.  In order to produce accurate information and reports, I must combine the data from both systems.  The most practical way of using the Pervasive tables is to link them via ODBC and we have the drivers to do that.  In all of the databases that use the linked tables, I created a macro to delete that table, then re-link via ODBC to refresh the tables prior to the queries.  After I created this macro, my Access databases started to crash either in the middle of being used or following their closing.  I prompt all of my Access databases to compact on close.  I need to know how to stop this problem.  Thank you
0
Comment
Question by:chefpablo2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 20417738
chefpablo2000,

1. Use VBA code, not macros.
Macros in 2003 and earlier have no error handling or breakpoints to show you where the error is occurring.
Convert your macro to VBA Code:
Select the macro in the database window.
Click: Tools-->Macro-->Convert Macro to Visual Basic.
Click the "Modules" tab in the Database window.
Open the "Convertedxxx" Macro.
Copy this code and paste it wherever the Macro was.

2. Why delete the table, why not simply "Refresh the links"?
See here:
http://search.experts-exchange.com/simpleSearch.jsp?q=refresh+table+links&searchSubmit=1&cid=315&redirectURL=%2F&gSearch=0

3. Turn off the "compact on close" option for now, until you work this out.
If the DB gets corrupt during one of these "Crashes" you could end up compacting a corrupt DB and not be able to open it!
The first thing every Data Corruption article mentions is "Back Up the database", BEFORE you attempt any recovery.
(Compact repair is Access' "Recovery tool", so you should turn it off for now)

Hope this helps

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20423650
Glad I could help!
:)

Jeff
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 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