A bad day with Access, is it only me? (1 Viewer)

[twgonder]

The last 24 hours have been hell day with Access 2021. I had burps, crashes, and contaminated data and tables, queries and forms. In multiple dbs.

It all reminds me of my first experience with Access almost thirty years ago, when I first walked away from it. Maybe I'm doing things all wrong, but if that's the case, why does Access permit it?

First, I had another, maybe the 10th this week, where the db(s) just hung and nothing would move, just a bell that kept dinging.
So I had to end it with task manager. But then I opened it up and ran the compact/repair and things seemed okay.
A little later, as it's done many times recently, it just shuts down after a pause and forces a backup, no explanation given.
After that I saw some data in a table that had been corrupted with strange characters in several records.

I had changed some field names from within the relationship view, and everything seemed good. I exported tables, queries and reports to another db. Things ran, then I made a .accde, it went through its steps, but when I tried to run the .accde, there were errors asking for parameter values. Digging deeper, I found that some of the field names I changed had corrupted the queries and forms with new sources for the data source properties.

I know how to reset and stop a running VBA procedure that's stopped for an error, and that wasn't the case in these problems.

Is it just me, or are others seeing these kinds of errors? It's not a complicated db, I'm just doing some simple reports and coding.

 

[Pat Hartman]

I found that some of the field names I changed had corrupted the queries and forms with new sources for the data source properties.

You might want to tighten up your testing. How about compiling?

Are your users working on a wireless connection? If they are, your best option is to convert the BE to SQL Server tomorrow. Jet/ACE are like the canary in the coal mine. They are very susceptible to "blips" in a network and Access cannot recover. You still get the "blips" with SQL Server, but the BE is unlikely to corrupt and if you use a batch file or loader to start the FE, it can always download a fresh copy of the FE so even if the FE got corrupted, downloading a fresh copy would overlay the bad version.

 

[CJ_London]

Maybe I'm doing things all wrong, but if that's the case, why does Access permit it?

given the subjects of your recent posts on this and other forums where you appear to want to drive a round peg into a square hole, my guess is you are doing things wrong. As to access permitting it, seems to me it's not. There is nothing to stop you blindfolding yourself and attempting to cross a busy road - but there is a good chance something will go wrong. Perhaps there should be a big sign somewhere saying 'don't cross the road if blindfolded'. Ah but that won't work, you're wearing a blindfold so wont see the sign.

there were errors asking for parameter values... found that some of the field names I changed had corrupted the queries and forms

perhaps autocorrect is turned off.
perhaps you have made changes in the BE and not accounted for them in the FE, or the other way round
And when you say corrupted do you mean you get an error along the lines of 'field not found'?

just a bell that kept dinging.

implies you have code in an infinite loop - perhaps looping through a recordset and not included a .movenext

 

[twgonder]

Let me clarify. Access is running on a stand-alone laptop, not with any other user or a backend.
I'm just creating tables and changing/adding field names as I go. I created three reports in the database that keeps failing.
I did use some old code for multi-language, it loops through all the db objects and updates the tag and captions properties.
There were some bugs in there, but the code wasn't active that I know of, it seems to finish or falls into the code where I inspect the problem.
I'm not doing anything I haven't seen in past and present training on Access.
I can't say exactly what happens just before each crash, but normally it's just closing a table in design mode or when a report finishes.
I don't know what blindfold you think I'm wearing, can you elaborate please?
As far as the square peg analogy, I'm just trying to use my experience to make my apps look and act a bit more professional than the typical toy apps I see built in the templates and training videos. But nothing too exotic, in fact quite basic really.

If you looked at my sample in the other forum, you can see I successfully created a report with two tables that others said was impossible or stupid, and I didn't violate any design rules or square pegs that I know of inside or outside of Access.

One table to create a hierarchy instead of many tables. How to create link logic?

Here is a little puzzler that's been in my mind for years. I don't do much with Access, other than poke around and look at samples. I've never seen anything like my solution (in an old super-mini database application) in Access. The idea is to create one table that can replace dozens or...

www.accessforums.net

 

[cheekybuddha]

Are you using Windows 11?

 

[twgonder]

Are you using Windows 11?

Nope it's Windows 10 all updates and Access updates applied.

 

[Minty]

Over the years the AutoCorrect option has been a culprit for strange crashes and corruption.
Personally, I always turn it off and only rarely have any of the issues you are describing.

Sometimes I have experienced a "rogue" database that seems beset with unexplained crashing and locking up. If I get one of those I create a backup and then import everything into a new blank database. This is similar in effect to decompiling. Decompiling a database

 

[cheekybuddha]

Nope it's Windows 10 all updates and Access updates applied.

I only asked because I have seen reports recently where in Windows 11 new windows opened from Access are opened underneath the Access application and are not immediately noticed.

You said:

the db(s) just hung and nothing would move, just a bell that kept dinging.

This is normally a symptom of a modal or [error] message box needing to be dismissed before you can proceed using your application.

Do you ever connect your laptop to a second monitor? Sometimes Access 'remembers' positions of opened windows and if the second monitor is not connected the windows/modals/message boxes can be opened in an unreachable part of the screen. Without being able to dismiss the other window/message box you will just here the 'bell' when trying to interact with your application.

I found that some of the field names I changed had corrupted the queries and forms with new sources for the data source properties.

Do you make code changes while your forms are in Form View? Whilst Access allows this, in my experience, it is often a cause of corruption

Whenever you want to make code changes, try to make sure your form is in Design View first. Then compile your code before switching into Form View.

 

[twgonder]

I only asked because I have seen reports recently where in Windows 11 new windows opened from Access are opened underneath the Access application and are not immediately noticed.

You said:

This is normally a symptom of a modal or [error] message box needing to be dismissed before you can proceed using your application.

Do you ever connect your laptop to a second monitor? Sometimes Access 'remembers' positions of opened windows and if the second monitor is not connected the windows/modals/message boxes can be opened in an unreachable part of the screen. Without being able to dismiss the other window/message box you will just here the 'bell' when trying to interact with your application.


Do you make code changes while your forms are in Form View? Whilst Access allows this, in my experience, it is often a cause of corruption

Whenever you want to make code changes, try to make sure your form is in Design View first. Then compile your code before switching into Form View.

No auto correct, no second monitors. Have done decompiling and exports to a new db. Same problem.
The last line is a good clue, kinda silly though seeing as Access drops into the code on an error while in the form view, and supposedly allows for rewrite and continued execution. I'll keep an eye out if that is what's happening. Thanks. As a solution, when you trap into an error or for debugging in the VBE, do you reset and close VBE, go back and change form views, and then return to the offending code to edit? If so, that kills the idea of Access being an IDE.

 

[cheekybuddha]

kinda silly though seeing as Access drops into the code on an error while in the form view, and supposedly allows for rewrite and continued execution.

It offers the option to drop in to code view, but it's better not to change the actual code without stopping the code from running.

It is good however for inspecting the current values of variables, and changing those via the Immediate Window (Ctrl+G) (slightly different from changing the actual code)

 

[Pat Hartman]

No auto correct

Does that mean that you specifically turned it off or you just don't see the log table? Leaving Name AutoCorrect on but not understanding how it actually works can cause really bad problems like wrong tables getting updated, that don't necessarily raise errors
Do you have any compile errors?
Do you have any missing references?
Do you have any timer events in the db? Editing code while a timer event is running on a different form will cause changes you make to code to be silently discarded as well as lead to corruption. ALWAYS ensure that no timer is running if you are making changes to objects or you will be very sorry. If you use timers, and there are valid reasons to do so, do yourself a favor and add code to your start up form to ask if you want to disable timers so you don't ever forget. Then in the close event of your login or whatever form stays open throughout the app so it is the last to close, if the timers are off, ask to turn them back on.

Does C&R fix the problem so you can continue?
Does Decompile fix the problem so you can continue?
Have you rebuilt the app from scratch? The best way to do this is to export all objects to text. Then in a new, empty database, link the external tables and import any local tables from the old database. Then import all the exported objects. Fix your startup settings. Compile. C&R

Corruption can be very difficult to weed out.

 

[Pat Hartman]

@twgonder "liking" all responses is very polite but not very useful. It is much more helpful to people who find this post later if you use your words. Tell us what you tried. What worked, what didn't work, what was just useful information for a later project, etc.

 

[twgonder]

If by timers you mean pauses like this:
Call mySleep(1, True)
---
Public Sub mySleep(Optional Seconds As Double = 1, Optional AllowDoEvents As Boolean = True)
' this function will break up seconds into quarters of a second

Dim SecFrac As Double
On Error GoTo ErrCd
SecFrac = 0
While SecFrac < Seconds
Sleep 250
If AllowDoEvents Then DoEvents
SecFrac = SecFrac + 0.25
Wend
ExitCd:
Exit Sub
ErrCd:
' Select Case Err
' Case 998, 999: command
' Case Else
Stop
MyError = ErrUnhandled(Err, Error, "mySleep")
' End Select
Resume ExitCd
End Sub

Then yes, I have one for 1 second. If you mean something else for timer, then probably not since I don't know what they are.
If you mean autocorrect as in this, then I guess it's on by default:

Are you saying to uncheck all the options? or something else?

The normal compile errors but I fix them before running again if possible, or drop into the code to inspect. As stated, I did fix some errors and try to rerun from the code window without resetting.
Missing references? Where would I find those?
I looked for a Access log file in the directory for the .exe of Access, lots of files, but I didn't see a log file. What's it name and I'll do an everywhere search?
What is C&R? it's crashed/hung, so not much I can do. I can usually continue after Access restarts without doing anything special, but one time I did see some corrupted data in a table. There may be more that I haven't found yet.

 

[CJ_London]

If you mean autocorrect as in this

no - Pat is talking about Name autocorrect

Missing references? Where would I find those?

in the vba editor under tools>references

I looked for a Access log file

Pat said table - see image above - you need to tick the log name autocorrect option for the table to be created

What is C&R?

Compact and Repair - something you should do on a regular basis when developing and occasionally in production as you would with any rdbms although it might go under a different name

You probably also need to think about decompiling on a regular basis during development

Think you have been told about the importance of Option Explicit and compiling code before executing it to check forand eliminate compile errors

 

[twgonder]

no - Pat is talking about Name autocorrect
...

Yep, been doing most of those things (decompile, compact & repair, etc. I'll check out what auto correct is supposed to do, and turn it off.
Thanks!

 

[twgonder]

I may have found one cause. After working in Access I tried to go to Outlook, it was hung with an hour glass.
Then I went back to Access, and it was hung too, and I see this...

What is Access trying to do to Outlook that hangs them both?

 

[CJ_London]

no idea - access doesn't do anything you don't tell it to do so if you have code in access that does something with outlook, check there -or visa versa if you have code in outlook that does something with access. Alternatively might be a setting on your machine - as suggested, click to see settings. You appear to be wanting to do something in a controlled folder.

without knowing your setup these links might be of help - most recent first

How to Configure Controlled Folder Access to Stop “Unauthorized changes blocked” Notifications

How to Configure and Use Controlled Folder Access in Windows 10. Adding apps to the Allow list manually or using PowerShell. Adding folders for protection.

www.winhelponline.com

Redirecting

Unauthorized changes blocked: 3 ways to remove these notifications

If you want to disable 'Unauthorized changes blocked' alerts, you need to disable Controlled Folder Access via Windows Defender or PowerShell.

windowsreport.com

 

[Uncle Gizmo]

I've popped into your thread a couple of times just to see what's going on. I realised early on that I didn't have any useful information to impart.

However following from Chris's observation above, in my experience 99% of the problems I have had, have been in errors I have made.

As a rule of thumb, if I find myself thinking MS Access has got a bug, MS Access has got some sort of corruption, thinking, there's something wrong with MS Access, and I can't find the issue, then you can pretty well guarantee it's something you've done wrong yourself.

How to find the issue?

If you've tried stepping through the code, if you asked lots of questions on the forums and you are getting conflicting and confusing answers, then I'm afraid it falls back on you to plan a sensible approach to solving your problem.

The first step is to create a temporary database and construct one single piece of your database in that. Test that individual piece of your database, for instance send it information which you know should make it Error, see what happens.

Once you've got that first part working as expected then separate out another piece and do the same.

Put all the pieces back together into one database and you should have solved your problem.

 

[Pat Hartman]

Yes, I was talking about timers AND autocorrect.

Sleep is not a timer event. Look in your list of events for forms.

 

[twgonder]

...

However following from Chris's observation above, in my experience 99% of the problems I have had, have been in errors I have made.

As a rule of thumb, if I find myself thinking MS Access has got a bug, MS Access has got some sort of corruption, thinking, there's something wrong with MS Access, and I can't find the issue, then you can pretty well guarantee it's something you've done wrong yourself.

...

To follow up on a new day, hopefully a better one with Access, some new observations.
This is a fairly new laptop, with Windows 10, all updates and a new install of Office Pro 2021.
That said, there was some old Office stuff that came on the laptop from HP.
I've done my best to uninstall the old stuff, but lots of files and folders from Office15 (current version in Program Files is Office 16). Many Mb of old stuff. I don't want to manually remove the old Office15 folders, for fear that somehow MS still uses those for some unfathomable reason.
In addition, Office 365 seems to have some code on the laptop, if that's not what's in Office15 folders.

I'm just building simple tables and reports for multi-language and changing colors in the properties.
I've reviewed the code I've downloaded from forums and training sites. Most of it looks good, nothing too exotic.
The most exotic is the sleep function which does an API call for 1 second.
I'm not doing anything with Outlook from Access, despite that Access seems to be "pinging" Outlook a lot on its own. I have no idea why other than they are both part of Office. Microsoft is considering this pinging a threat and blocking it as best as I can tell.