Solved

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

Posted on 2007-12-05
2
260 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
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now