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

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
chefpablo2000Asked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help!
:)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.