Instr() function crashes MS Access after 4/4/2024 update to office 16.0.17425.20146

Anonymous
2024-04-05T16:32:38+00:00

After Office updated to version 16.0.17425.20146 my MS Access crashes when running very simple functions. I created a new database with one table and one query.

Table1.Text1
Zebra
x-ray
4x4
Posted to X
Next
axe
None
extreme
wax

This works:

SELECT Table1.Text1, InStr(1,[Text1],"x") AS Field2

FROM Table1;

Query1
Text1 Field2
Zebra 0
x-ray 1
4x4 2
Posted to X 11
Next 3
axe 2
None 0
extreme 2
wax 3

This crashes MS Access before returning any results:

SELECT Table1.Text1, InStr(1,[Text1],"x") AS Field2

FROM Table1

WHERE (((InStr(1,[Text1],"x"))>0));

I tried compact/repair. Tried recreating the database from scratch. Rebooted my computer. I uninstalled and re-installed MS Office.

Any ideas??

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

54 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2024-04-10T21:42:43+00:00

    I ran into this issue and I've spent last week thinking about all kind of possibilities, even having a RAM issue (passing long lasting tests), some kind of undetected malware... you name it. Behaviour is really odd because i had a managed PC in office, running Office 365 64-bit, without issues, but in my personal home PC same .accdb files crashed in simple queries sometimes, but at first without being able to find a pattern. If I did a compact and repair in my office laptop, then it worked on my personal PC. But after some time I had the crashes.

    Now I now the crash happens when a Compact and Repair is performed in my personal PC. Also when I create a new blank database and import a table and a query to test, it crashed. So somehow the new .accdb created is "wrong". I thought I had some malware, but I passed every king of antivirus and nothing was detected. Because Compact and Repair starts creating a blank database, compacting it makes the good one where query works into a "wrong" one that produced the crash. I was opening .accdb in safe mode (Access does this after a crash), but as suggested in many places, also decompiling and compacting, and today I've found that when opened in Safe Mode, then the new database created is "good", and also Compact and Repair generates an .accdb that do not crashes. Which lasts until next compacting done out of safe mode which was what I was doing in my tests so I didn't noticed the first compacting in safe mode "fixed" the .accdb.

    After seeing this thread (by the way there are almost no findings of it, specifically for "Faulting module name: ACEES.DLL") I checked Microsoft 365 versions, not being conscious that are different in office and home PC, because office one is managed and set to Semiannual Enterprise channel. So I changed channel in my home PC and voilà, now everything works. Still amazed that a released version of Access has such an issue, being myself an Access user since it was released decades ago.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-04-11T03:52:54+00:00

    How did you run the sql?

    I did not meet any problem with VBA.

    Set conn = CreateObject("ADODB.Connection")

    Set Rst=CreateObject("ADODB.Recordset")

    strPath = "D:\wwwroot\WEB\Bsbm\stu60\vba\up\upload"

    ' Update this with your actual folder path

    With conn

    .Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & strPath & "update\_two\_tbl.db"

.Open

    End With

    '

    sql="create table Table1 (Text1 varchar(255))"

    'Conn.Execute(sql)

    sql="insert into Table1 (Text1) values ('Posted to X')"

    Conn.Execute(sql)

    sql="select instr(Text1,'X'),* from Table1 where instr(Text1,'X')"

    Set Rst=Conn.Execute(sql)

    ActiveCell.CopyFromRecordset Rst

    conn.Close

    Set conn = Nothing

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-04-11T08:33:12+00:00

    I was just executing a query. I cannot try anymore because I switched to Microsoft 365 Semi Annual Enterprise channel and the problem is gone now.

    Microsoft® Access® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20176) 64-bit

    -> had mentioned issues

    Microsoft® Access® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20542) 64-bit

    -> no issues, version I have now

    What I find really odd is that a new database created in Safe Mode did not have the problem, and a new blank database in "normal" mode had the problem. I don't get why a new blank database should be created diferent in safe vs normal mode.

    Was this answer helpful?

    0 comments