Rx_
Nothing In Moderation
- Local time
- Today, 12:57
- Joined
- Oct 22, 2009
- Messages
- 2,803
My development copy of MSACCESS 2010 became corrupt. While it was recovered successfully, it was a Friday afternoon and a step was forgotten.
This method also applies to upgrading an Access 2007 to Access 2010.
Review of Recovery of Corrupted DB:
How does one realize a DB is corrupted? It can be one of several things.
For what ever reason, I suspect it happens when too many objects (form, listbox, text box...) are copied and pasted. Sometimes the VBA code just won't work properly or work the way it use to work. In this last case, the form would open and run. When opening up the form in Design View, the MS Access would halt with an error and close down.
The Locking file was still open. The compress / repair would appear to work. However, the next Design View would just crash the application again.
My preference is to make a copy of the Database under design each day and then version it about each hour of coding / design. Hard drive is cheap.
Save format: Path/<YR-MO>/Day-of-Month + A (A, B, C...) + DB Name + short description of effort. (e.g. F:\2013-05\07C Environmental - Add Flare tracking)
This database under design on my local PC has a few local tables and around 120 Linked tables to SQL Server.
Steps to recover:
1. Check to see if the Linking DB is still open. It will typically be in the same directory as the database under design with a designation of Microsoft Access Record-Locking Information. If the database under design is not open, just delete it.
2. Power down the PC. Get a fresh start.
3. Create a new Blank Access 2010. Delete the Table1 if it is automatically there. Go into Options and change them to match the old Database. For example the Tabbed view Vs Window view for objects.
4. Save the Access DB AS - the new recovered DB.
5. Import all DB objects. This is a good reference. There are many more that can be found.
http://msdn.microsoft.com/en-us/office/cc907897.aspx
Note: the Linked Tables with names over 512 characters and other items that can be a problem.
On step 5, be sure to go across each tab and import Forms, Queries, Macro and the rest.
6. In the old DB code window under Tools - Options - References
Be sure to re-set the References and order them in the same order.
(e.g. Microsoft Excel 14.0 Object Library, Microsoft Visual Basic for Applications Extensibility 5.0, Microsoft ActiveX Data Object 6.0 Library...)
For Step 6. After the save - use the Debug - Compile (name of the DB project)
Any objects that are not properly referenced will show up as an error.
In my haste on Friday, I forgot step 6. Some objects were referenced so the form under test did work.
In review. Make a versioned copy often. Keep a screen shot of the References.
This method also applies to upgrading an Access 2007 to Access 2010.
Review of Recovery of Corrupted DB:
How does one realize a DB is corrupted? It can be one of several things.
For what ever reason, I suspect it happens when too many objects (form, listbox, text box...) are copied and pasted. Sometimes the VBA code just won't work properly or work the way it use to work. In this last case, the form would open and run. When opening up the form in Design View, the MS Access would halt with an error and close down.
The Locking file was still open. The compress / repair would appear to work. However, the next Design View would just crash the application again.
My preference is to make a copy of the Database under design each day and then version it about each hour of coding / design. Hard drive is cheap.
Save format: Path/<YR-MO>/Day-of-Month + A (A, B, C...) + DB Name + short description of effort. (e.g. F:\2013-05\07C Environmental - Add Flare tracking)
This database under design on my local PC has a few local tables and around 120 Linked tables to SQL Server.
Steps to recover:
1. Check to see if the Linking DB is still open. It will typically be in the same directory as the database under design with a designation of Microsoft Access Record-Locking Information. If the database under design is not open, just delete it.
2. Power down the PC. Get a fresh start.
3. Create a new Blank Access 2010. Delete the Table1 if it is automatically there. Go into Options and change them to match the old Database. For example the Tabbed view Vs Window view for objects.
4. Save the Access DB AS - the new recovered DB.
5. Import all DB objects. This is a good reference. There are many more that can be found.
http://msdn.microsoft.com/en-us/office/cc907897.aspx
Note: the Linked Tables with names over 512 characters and other items that can be a problem.
On step 5, be sure to go across each tab and import Forms, Queries, Macro and the rest.
6. In the old DB code window under Tools - Options - References
Be sure to re-set the References and order them in the same order.
(e.g. Microsoft Excel 14.0 Object Library, Microsoft Visual Basic for Applications Extensibility 5.0, Microsoft ActiveX Data Object 6.0 Library...)
For Step 6. After the save - use the Debug - Compile (name of the DB project)
Any objects that are not properly referenced will show up as an error.
In my haste on Friday, I forgot step 6. Some objects were referenced so the form under test did work.
In review. Make a versioned copy often. Keep a screen shot of the References.
