I think my database is corrupted

[Kronix]

When opening my main form or changing records on my main form I get an error from the Form_Current event. It says there is an error communicating with the OLE Server or ActiveX Control. I've pinpointed that the error only appears when the Private Sub Form_Current() event exists for the main form module in VBA. If I remove (or comment out) the Form_Current routine it no longer gives an error. Even if I put a blank Form_Current routine in it still gives me an error, so I can only assume whatever it is is beyond my control.

 

[Minty]

Take a back-up.
Have you already tried a compact and repair, after removing the offending code? Sometime this will work.

If not the next stage is to import all you objects into a new blank database. I would leave out the form that has the problem, and if possible either recreate from scratch - or import a working older version from a previous backup that you obviously have lots off ... ?

 

[isladogs]

Two more things to try after doing a compact & repair and before importing all items into a new database.

BACKUP FIRST..

1. Check the VBE object list for any items called '~CLP...
If so remove by following approach in this thread
https://www.access-programmers.co.uk/forums/showpost.php?p=1531325&postcount=1

2. Try decompiling. This will remove any corrupted compile code.
Then recompile and compact again.
This link gives more info on decompiling.
http://www.fmsinc.com/MicrosoftAccess/Errors/Bad_DLL_Calling_Convention.asp


If that also fails then import all items into a new clean database

 

[CJ_London]

it is also possible you have dropped a required library. As minimum you should have

Visual Basic for Applications
Microsoft Access xx.0 Object Library
OLE Automation

the xx will be a version number which varies depending on which version of access you are running. (2010= 14.0 for example)

 

[Kronix]

Thanks guys, I tried decompiling as well as overwriting the offensive Form with a copy, to no avail. Luckily I did have a 1 day old copy.

I am quickly discovering how buggy this piece of Microsoft software is. Sometimes I get one-time errors out of nowhere that seem to fix themselves. Other times....this happens. Thanks for letting me think it's not all my fault

Oh btw I think this happened when I was using a button with a delete record macro and I pressed the button 2 times fast. I found that a necessary record from a different table than the one that was supposed to be erased had been erased. But even after replacing that record the OLE/ActiveX error never went away. oh well....

 

[CJ_London]

It can happen, but are you working across a wireless network by any chance? In my experience that is the main reason for corruption, that and multiple users using the same front end.

 

[Minty]

Oh btw I think this happened when I was using a button with a delete record macro and I pressed the button 2 times fast. I found that a necessary record from a different table than the one that was supposed to be erased had been erased. But even after replacing that record the OLE/ActiveX error never went away. oh well....

In addition to the advice from CJ, always add a confirmation for a record delete. It avoids these types of problems.

In many situations its's better to not actually delete the record just mark them as expired or archived.

 

[Lightwave]

I must say I've only had a couple of issues in 2 years and find it incredibly stable compared to the frozen tissue paper of dependency that are web applications. Nonetheless I sleep better when I implement rolling backups on important applications. For all applications I will have.

1.a Back End
2.a Development front end – (this is used to adjust and create runtimes 3 )
3.a Front end run time

Of the above two I backup both the back-end and the development copy of the front end. I DO NOT back up run times (No.3) , personally if I have the others backed up I can re-create a run-time from whatever point I desire. If for some reason I can’t create a run-time from a development copy it is time to roll back on the development copy – this should exist because of this very post.

In terms of the two types of copies I consider both equally important. Back-ends are easy to create but very difficult to re-create increasing in value with time. Front ends have no value in the data but take an extra ordinary amount of time to figure out and likewise are very awkward to recreate increasing in value with time. Both are digital and can be copied multiple times often with little to no cost (size dependent for back ends). Many front ends are actually tiny but their value comes from the way in which things flow and the speed with which they execute.

So I backup both to date I’ve had some brushes with almost disaster but never actually lost an application. Yes there have been hiccups mainly due to my own error – getting confused with versioning and deleting the most up to date version rather than yesterdays version. Forgetting a password on an encrypted USB and having it wipe itself requiring recovery from backup – that kind of thing annoying but not really a problem. I now have a daily backup routine that at least means that I cannot really loose more than a days work. I use it all the time.

This can be edited so backups are every minute / second etc if you are really cautious. Its quick to implement. I also use it for long term backups as well. In the example generic code I am only backing up the backend daily and monthly. In production I typically would have an option for minute by minute backup daily monthly and yearly.

Option Explicit
Dim FSO
Dim vardatefile
Dim varmonthfile
Dim BDayFilePath
Dim BMonthFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "YourDatabaseBackEndCopy-Weekday-" & day(varnow) & ".accdb"
varmonthfile = "YourDatabaseEndCopy-Month-" & Month(varnow) & ".accdb"
BDayFilePath = "C:" & vardatefile
BMonthFilePath = "C:" & varmonthfile

FSO.CopyFile "C:\DatabaseTarget.accdb", BDayFilePath, "True"
FSO.CopyFile "C:\DatabaseTarget.accdb", BMonthFilePath, "True"
Set FSO = nothing

msgbox "Backup Complete" ,0, "Backup Script"

Save in simple word editor and change to vbs suffix , double click to run.
Note I have made this Option Explicit which is good practice although it is entirely possible to remove Option explicit and dynamically set the variables.

 

[Kronix]

You are, of course, talking about the the back-end and front-end files that result from splitting the database in the Access Splitter wizard, right?

It looks like your program makes copies of both the weekly and monthly database at the same time. Isn't there supposed to be a time difference? It looks like it's doing the same thing twice every time.

BTW, are .vbs extension files executable by all versions of vanilla Windows, or since when? Do they require that any extensions be installed?

I thought that .bat extensions were still the standard.

btw, Minty, marking deleted records as expired/archived instead is a good idea, I'll try that.

 

[CJ_London]

You are, of course, talking about the the back-end and front-end files that result from splitting the database in the Access Splitter wizard, right?

yes - but I'm also asking is your development front end on the server or another machine and you are editing it from a wirelessly connected laptop/pc?

 

[Kronix]

No, not wireless. Not split, either. And the same error occurred on 2 different machines, copied from a USB stick.

 

[isladogs]

No, not wireless. Not split, either. And the same error occurred on 2 different machines, copied from a USB stick.

Copied from the same USB stick?
Perhaps that's your problem?

 

[Lightwave]

You are, of course, talking about the the back-end and front-end files that result from splitting the database in the Access Splitter wizard, right?

Yes

It looks like your program makes copies of both the weekly and monthly database at the same time.  Isn't there supposed to be a time difference?  It looks like it's doing the same thing twice every time.

Its copying one file and putting a month suffix on it and copying the same file and putting a daily suffix on it. This is the rolling part of a rolling back up.

BTW, are .vbs extension files executable by all versions of vanilla Windows, or since when?  Do they require that any extensions be installed?

I thought that .bat extensions were still the standard.

.vbs files are executable by all versions of windows as are bat file. The same can be done by either - I just happen to have implemented it using a vbs script.

The reason I have split it into daily and weekly is that every time you make a new copy on a new day it will create a new copy however when you hit the end of the week it will start back at the beginning. You will therefore end up with a backup on the 1st 2nd 3rd all the way to the end of the month copies. If you find that Sunday version is corrupt you go back a day if that is corrupt you go back another day. Hopefully you will have discovered the corruption before you have to resort to going back a whole month.In theory this means you should never have more than 31 + 12 backups. But that will cover you a period going back a year. And if you do years as well it will go back forever.

 

[Kronix]

Copied from the same USB stick?
Perhaps that's your problem?

I'm not sure what you mean. I was working on the database directly from the USB stick when the error occurred. I tried using it on a different machine and copying it to the hard drive and it still didn't work. Do you mean to say working directly from USB sticks makes it more prone to corruption?

 

[missinglinq]

Yes! I've been in forced retirement for over a decade, now, and work this and four or five other forums 8-10 hours a day, and over the years, I've seen literally dozens and dozens of reports of databases being copied and transferred to a home PC and not working, and the culprit is almost always corruption caused by the thumb drive transfer! Apparently, when copying an Access db to a flash drive, the system will prematurely signal that the copying is complete, and if the thumb drive is immediately unplugged, the db will become corrupted...so give it an extra few seconds before pulling the drive out...or better yet, don't use this method of transferring Access files!

Linq ;0)>

 

[isladogs]

@Kronix
Linq beat me to it...

Only run Access files from a hard drive (local or network)
NEVER run them from any source which can suffer connection issues e.g. wireless connection, USB stick or even ... floppy disks (remember them...)

You can use USB sticks to TRANSFER files if you're careful but make sure you correctly eject them

=============================================
@Linq
Sounds like you spend even more time on forums than I do!

 

[Kronix]

I had a GoSub error on the Form_OnCurrent sub when I tried reopening my database today. That scared me so I immediately made a copy of the database. When I open that database, the error is gone, but it appears the changes that I last made (before I reopened it with the error) were not saved. This was weird and leads me to believe the database was not full saved when I tried to reopen it, resulting in the error and potential corruption. Oh yeah, it was all directly from a USB Stick

I also was playing around with the old corrupted database, and tried creating a brand new database and simply copied every object from the navigation pane into the new database. And it worked! No more error from the Form OnCurrent event either, and the VBA code also appears to have come with the modules. So my question is, is there anything lost in translation when copying this way, or is this an effective way to fully recover a corrupted database?

 

[Lightwave]

I also was playing around with the old corrupted database, and tried creating a brand new database and simply copied every object from the navigation pane into the new database. And it worked! No more error from the corruption error either, and the VBA code also appears to have come with the modules. So my question is, is there anything lost in translation when copying this way, or is this an effective way to fully recover a corrupted database?

This is a standard option for cleaning corrupt databases - you can lose the relationship diagrams but that is it.

I like to periodically compile the VBA in my databases I also like to periodically make them into runtime and perform compact and repair. If I can't conduct these procedures on a database I would consider it corrupt.

To date I have never had to copy over the objects from one database to a blank one.

 

[isladogs]

I had a GoSub error on the Form_OnCurrent sub when I tried reopening my database today. That scared me so I immediately made a copy of the database. When I open that database, the error is gone, but it appears the changes that I last made (before I reopened it with the error) were not saved. This was weird and leads me to believe the database was not full saved when I tried to reopen it, resulting in the error and potential corruption. Oh yeah, it was all directly from a USB Stick

I also was playing around with the old corrupted database, and tried creating a brand new database and simply copied every object from the navigation pane into the new database. And it worked! No more error from the Form OnCurrent event either, and the VBA code also appears to have come with the modules. So my question is, is there anything lost in translation when copying this way, or is this an effective way to fully recover a corrupted database?

Well that confirms the point about USB sticks.

The only things that are lost when you copy across objects are system tables like MSysObjects.
This is because those are automatically created in the new database.
As a result any deleted objects that still appeared in the old db MSysObjects are not transferred across.
It's a win win situation.

Having said that, if corruption reaches a certain level you can't import objects from the old db at all.

So in summary, if you can do it at all, it should work perfectly AFAIK

 

[CJ_London]

sometimes you can have one corrupted form (or report) which cannot be opened or transferred, so these would have to be recreated from scratch or go back to an uncorrupted copy.

there is also the undocumented savetotext and loadfromtext functions where all objects other than tables can be saved as or loaded from text files - see this link for more info https://access-programmers.co.uk/forums/showthread.php?t=99179