Solved

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

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

777 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