Access shuts down when doing a simple search

[JeanMarc22]

So, I'm analyzing data and I'm building a query where I can see all of table B and matching table A. It's like an "Unmatched Query" without the "WHERE A.MAINCODE = Null;". Here's a simplified version of my query:

SELECT B.*, A.*
FROM A RIGHT JOIN B ON A.MAINCODE = B.MAINCODE
WHERE (((B.LANG_CD)="ENG"));

Table A and B are linked table on the same local drive.

So far so good. I can see my data. But I'm looking for a pattern of data within 500k rows. So in the column where the Nulls are, A.MAINCODE, I search using Ctrl-F and I enter "*" (without the quotes). Wham, Access closes on me.

So I re-open it, do a compact and repair, then rebuild my query. I can see the data just fine. I wait until I see the total number of rows in the navigator bar. This time I first search for "c*". I found the row. I scroll down to the next blank and search for "*". Wham, Access closes on me again. I tried setting the search to "Start of Field", "Whole Field" or "Any Part of Field" with the same result.

I was able to replicate this behaviour in another desktop databases. We currently have Office 365, Access version 16.0, on Windows 10 with a large SSD drive. This behaviour did not occur in our previous version: Office 2013. Has anyone come across this before?

 

[CJ_London]

you haven’t said whether you are searching the entire table or a single column

I haven’t had any issues but as access matures it has become less tolerant of poor code and processes. In this case when you enter a * what do you expect to be found ? Values which include a * or all records?

if the former then try enclosing the * in square brackets which is what you would do with a like criteria if that is what you are searching for

if the latter it may be because there is nothing to actually find since it should stay on the current record or if you have nulls that may be creating an irrecoverable error

 

[The_Doc_Man]

Performing a Compact & Repair doesn't always work to uncorrupt a corrupted DB if the problem is in the compiled code. You might need to decompile and recompile.

The /decompile switch for Microsoft Access

Background information on VBA code compilation and the /decompile switch.

codekabinett.com

When using ANY approach to try to fix potentially corrupted databases, you need to ALWAYS ALWAYS ALWAYS make a good simple copy of the DB first so that if something fails, you can restore the original copy and try again or try something else. A simple copy is made from the Windows explorer window where you right-click on the file, copy, and then find a convenient directory and paste it.

When Access closes like you described, two things come to mind. I just described the first thing - corruption of the compiled code. The OTHER thing is that you might be running with errors disabled because Access doesn't normally do what you described and just shut down - without first issuing an error message that might help you figure what went wrong. The correct way to prevent error messages from disrupting your project isn't to turn off error messages, but the fix the errors being reported so that Access DOESN'T have to say anything.

 

[Mike Krailo]

I was able to replicate this behaviour in another desktop databases. We currently have Office 365, Access version 16.0, on Windows 10 with a large SSD drive. This behaviour did not occur in our previous version: Office 2013. Has anyone come across this before?

Are you using the same exact query and database to test on other installed versions of Access? I would simplify the query to see if this is happening because of the query or is it something else all together. For example, what happens when you create a new query on just one table and do the search? What happens when you use a query with half of the records or a small number of records. Narrow down the cause a little more, if it only occurs on that one database only, then as Doc said, you might be dealing with corruption.

At least the database opens for you, yesterday the database I was working on wouldn't open (even in safe mode) and I just had to revert to a known good backup.

 

[JeanMarc22]

you haven’t said whether you are searching the entire table or a single column

I haven’t had any issues but as access matures it has become less tolerant of poor code and processes. In this case when you enter a * what do you expect to be found ? Values which include a * or all records?

if the former then try enclosing the * in square brackets which is what you would do with a like criteria if that is what you are searching for

if the latter it may be because there is nothing to actually find since it should stay on the current record or if you have nulls that may be creating an irrecoverable error

To clarify, I'm using the regular interface search not using code. And using the asterisk means any character (0 or more). If it was in the query I would enter Like 'A*' or Like '*'. That works.
I'm talking about a glitch using the simple search, ie from the menu or ctrl-F.

 

[JeanMarc22]

Are you using the same exact query and database to test on other installed versions of Access? I would simplify the query to see if this is happening because of the query or is it something else all together. For example, what happens when you create a new query on just one table and do the search? What happens when you use a query with half of the records or a small number of records. Narrow down the cause a little more, if it only occurs on that one database only, then as Doc said, you might be dealing with corruption.

At least the database opens for you, yesterday the database I was working on wouldn't open (even in safe mode) and I just had to revert to a known good backup.

Good ideas about simplifying the query. I will try that.