Bizarre Table/Query Behavior in Access 2013

[WilliamW]

This issues started last week in a reporting frontend database that had been working. I can't think of anything to call it other than bizarre but maybe someone has some ideas.



The database is running on 2013 on a Windows Server 2012R2 machine with multiple users attaching to it. The database is split with a Backend containing the tables, a frontend database used in a production environment for data input and another reporting front end that currently only I am using. The front end machines are running either Win 7 or Win 10, all with local installs of Access 2013 32bit.

Last week I encounter a problem running a report on the reporting backend with the "This expression is typed incorrectly or too complex to be evaluated" error. I have encountered this in other databases in the past and have found that it is almost always caused by some type of error in the data that is creating an error in the calculations (when things were working before). I created a query to begin exploring the data to look for errors by looking only at dates since I had last run the report successfully, thinking I would be able to spot the data error as this should have been a relatively small dataset. Instead, I received an error when running the query that the database either exceeded 2GB or there was insufficient space on the drive for temporary files. Given that the database is 47MB and there are 179GB free on the drive that seemed rather unlikely.

I then went to the table in question PRODUCTION DB, and opened a datasheet view. (this is all being done in the front end on the linked table). I begin scrolling through the table and realized that it seemed very large compared to what it should have been given the amount of data we have collected over the 2 months this has been in use. Clicking the last record button gave me the same error as above, and when I cleared the error the record counter was showing over 237 billion records.

I then open the backend database and found the same thing happened in backend with the datasheet view, i.e. not using the linked tables. Now is when things get really strange, I ran a make table query using Production DB as a record source and no parameters and the query runs fine, creating a table of approximately 2000 records. (Since the front ends are working for collecting data we are still adding records to the data table so the number of records is growing, but around 2000 is correct).

Sorting the table on any column in datasheet view will give you a view with the correct number of records, but removing the sort goes back to the 237 Billion+ records showing.

Viewing the linked table with datasheet view from the front end used for product input shows the correct number of records, but viewing it either directly from the backend or from the link in the reporting front end gives the error.

I made a copy of the backend database and tried running compact and repair, but abandoned it after the process went on for 7 hours without completing.

Sorry for the long and probably convoluted explanation, but I wanted to try to cover as much as possible. Anyone have any ideas as to what might be going on?

[Micron]

a frontend database used in a production environment for data input

What does this mean? Is it used by only one person, or are several people sharing one fe - perhaps even concurrently?

Sounds like corruption; especially if it is one fe shared by many. When was the last time you compacted/repaired? See what happens if you backup first, then try compact/repair the current db's. You have backups of fe and be that you can test with? 47 Mb is for what, the fe or be? Sounds overly large to me if that is the fe.

[CJ_London]

frontend database used in a production environment for data input

as Micron says - each user should have their own copy. If not, you almost certainly have corruption. If you can still get into the backend, I would create a new database and copy the records across - do this on a table by table basis and copy and paste the records, not the table, from what you say, perhaps you need to sort to see the right ones. you will no doubt need to recreate your indexes and relationships.

Sorting the table on any column in datasheet view will give you a view with the correct number of records

not clear if you are referring to the original table or the one you created with the make table query. If the latter, chances are you have a corrupt record somewhere.


If you have a backup, keep it safe, you may need it.