Error #5 after compact and repairing (1 Viewer)

[biofaku]

Hi, I just found an error on my db and was looking for some advice.

I was working on my forms, looking to improve some things, when by mere accident I clicked the compact & repair button. After that, the welcome form started giving me error #5.
Looking through, the problem was the Select case statement, some thing like this:

Dim str as String

str = function() 'funcion gives back a "F" or a "M"

Select case Str
        case "M" ' HERE COMES THE ERROR'

Then I found the same error on other forms, but decided it was too much to investigate so I gave up.

But then, just for curiosity, tried to repair another backup, and the errors also appeared, so it was not an isolated problem.

I have tried to repair the db againt, tried to import the same forms from a backup, tried reinstalling the references but nothing seems to work so far.

Any idea what could have happened?

Meanwhile I am going to try to save all the forms that I do not have on the back up, but I'm afraid that this error could appear again any time on the future.

 

[The_Doc_Man]

There is a problem that has the generic name "corruption" - referencing an issue that normally is fixed by a Compact & Repair, but that sometimes is CAUSED by a C&R that went wonky.

Make a backup copy of the DB just in case things REALLY get wonky.

Read and implement the "decompile" process described in this link:

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

Go back into your code and compile.

While in the VBA window to do the recompile, do one more thing: Follow Tools >> References and see if any are marked "Missing" or "Broken". IF SO, you will have to repair your references.

After the recompile and reference check, try your routine again.

 

[Isaac]

Another few thoughts https://www.access-programmers.co.uk/forums/threads/unexpected-closure.316590/post-1752254

I never knew those problems were so prevalent solely interacting with people online, until I worked in person with a few newer VBA coders and realized a lot of people simply do not know that you can corrupt your entire Access or Excel file in about 10 minutes flat by not following a proper sequence of design...debug..compile..save..use.

 

[biofaku]

Thanks guys, gonna check those links and come back to tell you how went

 

[biofaku]

Well, tried to compile, recompile but still the error is happening. If I use a backup and copy all the content doesn't happen... But At the moment that I press compact and repair, it just goes all over again giving me error 5 for the most dumb select case you can find.

It even happens with other databases that doesn't have even one table on common. Maybe it's access that is corrupted? I tried it from a different PC and it kept happening there too.

 

[arnelgp]

check the "function" and investigate the returning value from the function.

 

[gemma-the-husky]

str = function() ??

Error 5 is invalid procedure call.
I think it would be easier to see the real code.

 

[Isaac]

Looking back over this thread more closely, I think I agree with Dave. If I'm reading your OP correctly, this is one specific VBA error, occurring on one specific line of code.

Why do you think this is corruption again? It may well be the code itself? Share?

 

[biofaku]

Looking back over this thread more closely, I think I agree with Dave. If I'm reading your OP correctly, this is one specific VBA error, occurring on one specific line of code.

Why do you think this is corruption again? It may well be the code itself? Share?

Hi Isaac. I am thinking this is a case of corruption because before using "compact and repairing" the client worked as intended. It was only after compacting and repairing that the error started appearing.

It's not on one specific line, it's on every line that includes a select case, more precisely the select cases that uses strings as part of the variable.

When I tracked the error, it highlights the variable as it was not supposed to allow using a string value. Weird, because before compacting and repairing there was not such issue.

 

[Isaac]

Ok, fair enough. And what if you take the first place the code balks, and comment out that select case. Does the error just move on to.....another random place?

 

[biofaku]

It depends, on some cases the code works just fine.
On others it looks to another problem that should not appear, like this:

Set drs = Me.Recordset

Giving me error 13.

Again, this code worked perfectly before compact & repair

 

[The_Doc_Man]

Error 13 is a "type mismatch."

The last time I saw this kind of behavior (odd-balll inexplicable errors), it was a references error. This is how I fixed it.

FIrst, I went to a code view so that I would see the VBA tool bar.
I clicked Tools>>References and wrote down the name of every reference that was checked.
Then, I unchecked all references. I DID NOT try to compile at this point - because it would never have done so.
Next, I saved & closed the code page, then closed the project.
Next, I reopened the project and opened the view into the VBA code.
After that, I went back and manually checked each reference that had been checked before.
Finally, I saved it and recompiled it.
This had the effect of resetting all reference links. If you couldn't find one of the named references, that one was the problem. And if you found all the links but this didn't solve it, it was still a relatively quick & dirty attempt.

 

[MajP]

It even happens with other databases that doesn't have even one table on common.

Can you clarify that? Are you saying other unrelated databases are experiencing the same issue? If that is the case then it is obviously not corruption in your database, but a problem with Access or windows.

@The_Doc_Man
What you describe above is done quicker and much cleaner by simply creating a blank database. Import all objects. Then decompile recompile.

 

[arnelgp]

Me refers to the current Class (can be a custom class, a Form class, or a report class).
if the sub where Me is mentioned is not inside that Class, then it would fail.

 

[The_Doc_Man]

@MajP - the OP imports from a backup copy, does a compact & repair, and reproduces the error. What you suggest (IF I read the discussion correctly) didn't seem to work.

It COULD be that we are reading it differently. Each of us of course reads things through the filter of our own experience. The last time I saw this, it was a corrupted library reference that wasn't labeled as "missing" or "broken" - but you couldn't use it. And it affected syntax in bizarre ways. When removed the reference and saved everything with NO references, then went back and tried to establish them, that is when the problem library revealed itself. In the worst case scenario, this might require a re-install of Access.

 

[MajP]

What I am saying

FIrst, I went to a code view so that I would see the VBA tool bar.
I clicked Tools>>References and wrote down the name of every reference that was checked.
Then, I unchecked all references. I DID NOT try to compile at this point - because it would never have done so.
Next, I saved & closed the code page, then closed the project.
Next, I reopened the project and opened the view into the VBA code.
After that, I went back and manually checked each reference that had been checked before.
Finally, I saved it and recompiled it.

Is that is better solved by a clean import into a new DB then decompile and recompile.

 

[The_Doc_Man]

It is faster as a test. If it happens to work (which it did in my case) then fine. If not, bring out the big guns with a full-on import of the world to new, blank DB.

 

[gemma-the-husky]

Set drs = Me.Recordset

given this statement, it may be a references problem. If ADO has been elevated above DAO references in the order of code references then maybe this statement is intended for a DAO response, but is getting an ADO response, which fails.

I don't think it helps giving us snippets of code that isn't even the real code. You may be missing something important by not showing the full code. Unless you are prepared to show us the full code, it's just very hard to assist.

 

[biofaku]

@MajP - the OP imports from a backup copy, does a compact & repair, and reproduces the error. What you suggest (IF I read the discussion correctly) didn't seem to work.

It COULD be that we are reading it differently. Each of us of course reads things through the filter of our own experience. The last time I saw this, it was a corrupted library reference that wasn't labeled as "missing" or "broken" - but you couldn't use it. And it affected syntax in bizarre ways. When removed the reference and saved everything with NO references, then went back and tried to establish them, that is when the problem library revealed itself. In the worst case scenario, this might require a re-install of Access.

I tried that, but after all, it ended up the same. Removed all the references except visual basic for applications and Microsoft access 16.0 Object Library. After saving, exiting vba editor and access, then opening all again and reinstating the references, the error persisted. No reference was labeled as "missing" or "broken".

 

[The_Doc_Man]

I'm leaning towards a faulty installation of Access and/or Office (depending on which library is the culprit). Uninstall and reinstall Office if you have the installation disk.