VBA Debug, Compile, issue

Question

Hello,

Over the years I have noticed that when I add VBA to an Access Databases and either rename the file name, compact and repair, move the access database from one folder to another or from one computer to another VBA stops working, example: I open an Access database and on the Switchboard screen I click a switchboard button to open a form and nothing happens. It is not until I go into the Forms Design View, "View Code" VBA area then click Debug, then Compile, then go back into Form view then everything works fine. This can happen on any form not just the switchboard.

Could you tell me why this happens and how to prevent the need to have to Compile and Debug or how to fix this so that this doesn't happen anymore.

Thanks.

Iram

Answer 1

Does the corruption occur during development, or during daily use?

If during development:

-- Work on a local copy of the application only. Don't open it from a network resource

-- Don't modify code in Break mode. Stop the code, open the object in Design view, and make the changes.

-- Debug - Compile frequently

-- Compact frequently

If during daily use:

-- Run a split configuration. Create a Backend with nothing but Tables, and a Frontend with everything else. Install a COPY of the FE on each local machine, and link all of those copies to the same BE.

-- Check your network. Faulty networks are a common cause of corruption.

Here's a link to a few resources that might help:

http://allenbrowne.com/ser-25.html

http://www.techrepublic.com/blog/10-things/10-ways-to-prevent-access-database-corruption/https://www.access-programmers.com/corruption-issues.aspx

You might also perform a Decompile. To do that, create a shortcut with a Target like this:

"full path to msaccess.exe" /decompile

Run that shortcut, then open your database. Access will decompile it, and you can then use Debug - Compile to recompile.

Answer 2

Hello Scott,

Thanks for your response. I have been doing what you suggested, but would you or anyone else know why I need to debug all the time and how to prevent it?

Thanks.

Iram

Answer 3

Sounds like troubles with your database. Make a copy of it, then do this:

1. Create a new, blank database
2. From that new database, import everything from the old database
3. Open the VBA Editor in the new database
4. Click Debug - Compile. Fix any errors, and continue doing this until the Compile menu item is disabled.
5. Compact the database

It would also be a very good idea to ensure that your Office and Windows installations are fully up to date.