Solved

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

Posted on 2007-12-05
2
270 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Close Print Preview button not active 12 36
Sub Reports 8 22
format date field on certain entries 8 29
Exchange 2010 Database wont mount 3 27
This article describes some very basic things about SQL Server filegroups.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

16 Experts available now in Live!

Get 1:1 Help Now