Compact and Repair causing errors?

[Petrol]

My Access database appears to run correctly, and it recompiles without error. However, if I run a Compact and Repair, when the database reopens I get the error message "Invalid databse object reference" - without, unfortunately, mentioning which object it is referring to. See attached screenshot. Then it says either "The expression contains an ambiguous name" or "The expression you entered has a function name that EmmausDBMS can't find", or even "The VBA modules in this database appear to have been saved with errors ..." - in each case without any indication of what expression or function it's talking about or where to find it. The promised 'attempt to recover the errors' doesn't help.

Furthermore, once C&R has encountered (or generated?) the error, it will continue to get it whenever the DB is opened, even if both the Autoexec macro and the module containing the Function Startup() are deleted.

It's Access in MS365 running in Windows 11. Presumably there is an error in the VBA somewhere, but can anyone suggest how to find it? Visual examination fails to find it. This has been happening for about the last week, and all my efforts to find the cause have been fruitless.

Twice now I have copied all the objects to a new database. The C&R and reopen of the new copy runs OK without the macros but with all the tables, forms, reports and modules included, but as soon as I add the Autoexec macro the problem reappears. The Autoexedc macro contains only a RunCode Startup() statement.
I have replaced the original Function Startup() with a dummy one containing only a MsgBox statement, and the error still occurs. Code for both versions is below.

I have no passthrough queries of objects with the same name as a table (both of which were suggestions on another website for this problem).

Public Function startup()
MsgBox "Starting"
End Function

Public Function WasStartup()
'
' 1. initialises global variables, then checks to see if the back end is linked; if so, open Switchboard; if not, open F74

' Process:
' Set global variables for the current user, the computer he is using, and the current back end
' Confirm that the DBMS is in a trusted location
' Check the Windows directory for the specified path (found by function WhichBackEnd)
' If it is currently valid, open the main menu and log the fact that the database has been opened;
' if not, open F74 (which is the only unbound form) to find an available back end
'

gblComputerName = VBA.Environ("USERDOMAIN")
gblComputerUserName = VBA.Environ("USERNAME") ' User name as recorded in the operating system
gblUserName = gblComputerUserName ' User name as recorded in the database, after he has logged on in F01
gblCurrentUser = 0 ' PersonID of current user (initialised to 0 so F74 can tell whether a user has logged in)

Call WhichBackEnd ' WhichBackEnd will find the path to the BE from the connect string and set it in gblBEPath
' (At this stage the connect string only contains the path to the last BE that was opened; it may not currently be open)

If Not CurrentProject.IsTrusted Then
MsgBox "The database file EmmausDBMS.accdb needs to be in a trusted location"
Pause (5)
Else
If Len(Dir(gblBEPath, vbHidden)) > 0 Then ' If there is a linked back end, ...
DoCmd.OpenForm "01: Login page" ' then proceed to let the user log in, and ...
Call LogActivity("opened", "DBMS on " & Currently & "data") ' ... log the fact that the system has been opened.
Else
DoCmd.OpenForm "74: Link to back end" ' otherwise, go find a back end; F74 will then open F01 (because gblCurrentUser= 0)
End If
End If

End Function

(Please ignore any illegal space characters in the above code. They are not there in the original, which does compile without error).

[NeoPa]

While all sorts of special characters are indeed allowed by Access in the names of Forms & Reports, most experienced developers & experts will advise caution with the use of any sort of punctuation characters as these can cause confusion when referring to the items outside of VBA - as well as having a tendency to confuse when reading the VBA. You're absolutely correct to conclude that this should not be any part of the problems you're experiencing though.

If I'm honest, from what you describe, this sounds like some form of corruption. Particularly something left over from an earlier version that was removed - but somehow not removed thoroughly by Access. My advice would be to create a new database completely - from scratch - and import items into it from the dodgy one very carefully making copies of the new databases as you go until you either :
Find that the new one has everything imported & works without the problem you've reported.
Or you notice the problem again so you have an idea of what particular object was being imported between a working copy & a broken one.

You can include multiple objects between versions if that makes getting there quicker, but eventually - assuming an imported object is seen to be the root of the problem - you'll need to identify it precisely by breaking up the batch that included it into smaller (& smaller) batches.

This approach can be slow & painful at times, but is highly reliable for producing error-free systems - even when you have to recreate a complicated object from scratch when it turns out to be the cause of the issues. More work & effort but you end up with a solid & reliable database (that you obviously keep copious backups of as you progress).

Good luck with this knotty problem.

[twinnyfo]

Hi Petrol,

I don’t know how this may or may not be affecting things, but with your OpenForm commands, it appears that your Form names include a colon, which I always thought was a restricted character for Form names. It would be odd that MS Access would allow your names to actually have a colon, and then cause problems when using those names.

This is just a hunch, but that may be a starting point.

Hope that hepps!

[jimatqsi]

Can you not break into debug mode when the first error appears? If not, you could set debug stops and then run the startup code. That should help point to something.

[Petrol]

Thank you both.
Twinnyfo: When I was creating the database I(and learning about Access as I went) I tried several formats for the form nmes. Several were rejected, but the colon was allowed. support.microso ft.com says that "Names of fields, controls, and objects in Access desktop databases ... Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ])."
JimatQSI: No, now that I have removed the Autoexec macro there is no code running at startup, so nothing to break into. When I open the database all I get is a blank screen. (I can display the Navigation panel and hence all the code, but none of it is run ning.) Then I do File>Info>Compa ct and Repair. It asks for the password, and when that's entered I immediately get the "Invalid database object reference" message. If I click on that, it asks for the password again (twice) and we go round in circles.

[twinnyfo]

Have you tried debugging from the VBA editor? If there is anything wrong with the Comapct/Repair, it should show up when you debug/compile.

[Petrol]

Yes, I've tried that. One of the first things I did was decompile (after starting with Shift to prevent Startup from running) and recompile, which it does without errors.
And if I reinstate Auroexec (with only a RunCode Startup() in it), the database opens and appears to do everything normally. The only issue is when I do a C&R.

[NeoPa]

While all sorts of special characters are indeed allowed by Access in the names of Forms & Reports, most experienced developers & experts will advise caution with the use of any sort of punctuation characters as these can cause confusion when referring to the items outside of VBA - as well as having a tendency to confuse when reading the VBA. You're absolutely correct to conclude that this should not be any part of the problems you're experiencing though.

If I'm honest, from what you describe, this sounds like some form of corruption. Particularly something left over from an earlier version that was removed - but somehow not removed thoroughly by Access. My advice would be to create a new database completely - from scratch - and import items into it from the dodgy one very carefully making copies of the new databases as you go until you either :
Find that the new one has everything imported & works without the problem you've reported.
Or you notice the problem again so you have an idea of what particular object was being imported between a working copy & a broken one.

You can include multiple objects between versions if that makes getting there quicker, but eventually - assuming an imported object is seen to be the root of the problem - you'll need to identify it precisely by breaking up the batch that included it into smaller (& smaller) batches.

This approach can be slow & painful at times, but is highly reliable for producing error-free systems - even when you have to recreate a complicated object from scratch when it turns out to be the cause of the issues. More work & effort but you end up with a solid & reliable database (that you obviously keep copious backups of as you progress).

Good luck with this knotty problem.

[Petrol]

Thanks for that advice, NeoPa (and others). Re form names - I take that point, and will avouid special characters in future databases. But this one has been in production for 6 or 7 years with about 60 forms and reports with names like nn:xxxxx, and nothing in that area chanegs about the time this C&R problem started, so it would be a massive job to change all the names and all the references to them and all the documentation, and as you said, I'm sure it wouldn't make any difference in this case.

As stated above, I actuially have rebuilt the database from scratch (twice), by copying all objects in batches and trying C&R after each batch. With all tables, forms, reports and modules copied to the new DB - in fact, everything except the Autoexec macro - C&R gave no problem; when I added my one-line Autoexec, it failed.

However, I'll do it all once a gain to make sure.

[NeoPa]

I just revisited your OP (mainly in order to fix the extra spaces) & saw that you'd already tried all that (pretty much). I'd forgotten those details before my reply, so apologies for that.

If you take the existing database that has the problems, & remove the Autoexec macro completely, does it return fundamentally to a working form - or do the problem(s) persist?

[Petrol]

No, it runs fine with no problems, except when I try C&R. As far as I can tell (and I hven't tested every form and every report) it operates perfectly normally.
Even if I put the Autoxec macro back in (or create a new one, to be precise) the database starts and runs OK. Unless I try C&R.

[isladogs]

You mentioned decompiling after opening with the shift bypass. Not sure exactly what you mean by that.
Decompiling should be done as a command line switch (or from a shortcut) - see Access Command Line Switches

e.g. "C:\Full\Pa th\T o\Your\Database .accdb" /decompile

[Petrol]

I meant I initiate the Decompile and hold Shift down when I enter the password, so that the Autoexec and startup procedure are not executed and are included in the decompile and recompiloe.

[NeoPa]

Let's try this again. When I say test the issue again, I'm referring to the issue where it crashes after a Compact & Repair.

So, my question is referring to that. From the answer you gave I assume that means that 'the issue' is still with the database, even after removing the AutoExec macro.

So, if you create a brand new database again & give it a dummy AutoExec (that does little but exists & gets run), you can first see if that has the issue (by testing after a Compact&Repair) . If it does then I would probably suggest your Access install needs to be scrapped & redone, but try on another PC/setup first to be sure.

Assuming it works fine as we'd expect, try again with the AutoExec that you know has the issue. It's hard to imagine it would, but if that behaves differently then we have a particular issue that almost defies description. Something to look at more closely if it is the case.

Otherwise, continue by following the earlier instructions of batch-importing until the issue is detected again. Tests for this must include that it continues to work flawlessly even after the Compact&Repair. Working without that is a test that is 100% useless in this situation. Surely very important in many others, but not this one.

If it continues to work without error (even after the C&R) then we're all good & your very important database can continue to proivide the value I know it does to all your users (obviously with lots of reliable backups going forward too of course).

If it fails at any time then the item you identify as the culprit needs to be isolated & probably reworked from scratch. Whatever you find to work for you, make sure you keep copious backups of working versions as you go forward as it's always much easier to recover from a recent known-good version when anything goes awry.

[NeoPa]

I should add that when you use something else in place of your original Startup() code it's important that you do *NOT* include the old Startup() in there with it - even if it isn't invoked from anywhere. It must be replaced completely otherwise it's still included in the file that you're testing.

[Petrol]

Well, as foreshadowed in Post #8, I have again reconstructed the database by copying all objects in the navigation pane into a fresh empty database, and it now works - that is, I can run a Compact and Repair without getting error messages, and the copy itself opens and runs OK. I can only assume that in the previous copy I inadvertantly copied whatever was causing the error, and this time I didn't. This is rather frustrating (I would prefer to have been able to track down the actual error), but I don't want to waste any more of your time (or mine) on it.
I appreciate all the suggestions that have been made to resolve the issue.