Access Crashes When Using VBA function in Query

Question

If I create a new Access database using the blank template in Access 2016 and then immediately attempt to run the following query it will crash Access every time.

SELECT instr("ABCDE", "E") FROM msysobjects

The offending portion is the use of the instr function (or any other function that requires VBA to process).

I have one working Access database. This query works in that database, but if I compact my working database then after that the query will crash Access.

I have tried everything I can think of including completely uninstalling and reinstalled Office.

Any ideas?

Answer 1

Does the Instr function work with non system tables or does it crash only when querying a system table? I typically recommend against attempting to open system tables since corrupting one can render the database unusable or damaged.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

The table has nothing to do with it. I used that table in an otherwise empty database to show that it is not the table design or any other database objects that were causing the issue.

Answer 3

First, you are not using the InStr function properly. Second, you don't specify which field in the table to query.

If I want to locate the position of the first instance of "s" in the Name field for each record starting at the 1st letter of the field, then:

SELECT DISTINCT InStr(1,[Name],"s",3) AS Expr1
FROM MSysObjects
WHERE (((InStr(1,[Name],"s",3)) Is Not Null));

Answer 4

SELECT instr("ABCDE", "E") FROM msysobjects

That works fine in my test DB (Access 2013 under Windows 7).  You say it crashes for you with any VBA function?  Just verify, when you say "crash", you mean that Access stops working and (probably) displays a system error dialog?

Does VBA work in this database otherwise?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

It works for me too under version 2007 Windows 7, but all it's telling you is that "E" is in the 5th position within "ABCDE". Is that what you were looking for? I compacted it and then ran the query again and it ran fine. I have had problems in the past using the InStr function in a query running it using a .accde or .accdr file, but not an .accdb file.

Answer 6

The table has nothing to do with it. I used that table in an otherwise empty database to show that it is not the table design or any other database objects that were causing the issue.

Can't really compare databases since they would typically have different objects and therefore different data in the MSysObjects table.

I can't really tell from your example which column you are searching.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 7

Thanks for all of your replies, but almost all of you are completely missing the point.

Yes, I know that the query is complete nonsense. It was designed to provide a simple example of what it takes to make Access crash. I deliberately used a system table and only literals so that it would be easy for others to copy/paste into any application. So, yes, I know that if it works correctly it returns the number 5 for every table record.

It doesn't matter what field, table, or VBA function I use Access will crash completely. If I use a non-VBA function (one processed directly by the Jet engine such as ABS) then it works perfectly. And, yes,VBA works on its own perfectly, just not when being called from a query.

What does CRASH mean? After executing the query Access processes for a while (Windows 10 indicates that the app is not responding) and then the Access shuts down and restarts. There are no messages of any kind.

I am using Access 2016 and accdb files, so what works on other versions of Access is an amusing, but pointless observation.

Answer 8

Hello SlimJimPoisson,

I test your code in Access 2016, it works for me too.

What's the build number of your office? Have you tried to upgrade your office to last version or repair your office?

Have you tried to test it on another PC with Access?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 9

How is the query being executed? Is it a QueryDef? Is it a statement being run through DAO?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 10

Thanks, Terry. I am not surprised that it works for you. My sense is that there is something wrong with my installation.

I am running Access 16.0.9126.2190 version 1803 (Build 9126.2116) 64-bit

I checked for updates, but it appears that this is the latest version.

I also performed both a quick and online repair. I then uninstalled Office entirely and reinstalled it. None of this had any effect, but I am wondering if uninstalling was enough. It seems that my settings were retained. Is there a further "scorched earth" uninstall step I should try. Also, is the blank database template based on a file? If so, where is that, maybe that is corrupted somehow.

And I have not tried to install Access on another machine, but given that you have tested it I will submit that it would also work on another machine here.

Answer 11

Hello SlimJimPoisson,

Would the issue be related to specific database file or any database? If you create a new database for testing, will the issue still occur?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 12

Yes, if I create a new DB it happens immediately. I don't have to create any other objects (not that it matters if I do). It also happens if I import an XLS. As I mentioned in my OP, if I compact the one database I do have it will start the exact same behavior, but right now in its overly bloated form it works as it should.

Answer 13

Hi SlimJimPosisson,

This issue is also not able to reproduce this issue on my side, test in Access 2016( 16.0.9126.2116 and 16.0.9226.2059. Here is the SQL for test:

Option Compare Database
Sub test()
sSQL = "SELECT instr('ABCDE','E'),* FROM msysobjects"

Set rs = CurrentDb.OpenRecordset(sSQL)
strText = rs.Fields(0)
Debug.Print strText
End Sub

Would you mind sharing the code you were developing if it is different? Also it depends on the Office update channels to update the Office to latest version if you were using Office 365. You can refer the link below to switch to Monthly Channel for the Office 365 suite to update the latest version:

How to switch from Semi-Annual Channel to Monthly Channel for the Office 365 suite

Regards & Fei

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 14

I was able to resolve this issue. While I had uninstalled and reinstalled before posting this I uninstalled again and then used the Microsoft Fix it tool to completely uninstall Access and then reinstalled it. That seems to have cleared everything up.

Answer 15

I can confirm this issue -- it crops up from time to time for me, also with Instr, incidentally.

I have a demonstration database I use to test if I have the issue. I'm happy to post it somewhere if you'd like.

g.