Access 2010 Intermittent Data Loss

[JustSteve120]

I have an Access Front End/Back End application that gathers data from multiple questionnaires. This is a single user application. There is a questions table that different questionnaires pull from using junction tables. Questions are loaded to a temporary table where responses are gathered, then saved to two tables (header record and detail records). The application also allows to update responses by copied from the save table to the temporary table to update scores.

We are noticing an intermittent problem where we are experiencing data loss during the copying back to the temporary file. Not only does the data not get to the temporary file, but the detail data from the save table is also gone.

I'm assuming it has something to do with an indexing issue, and I have banged on the data to replicate it, and cannot find any meaningful combination of steps that causes it.

My initial thought is to run Compact on Close in the application, since it is a single user application, and it does open and close relatively frequently, but I have read enough of the evils of CoC to think twice about that.

Any thoughts?

[twinnyfo]

JustSteve120,

I try to avoid temporary Tables if at all possible because of some of the bloat problems this can cause. What is the structure of the tables and data in your project that requires temporary tables, as it was not clear from your description.

The CoC problems can cause headaches, but I have been able to avoid those problems when the DB is compacted from a Network location (doesn't make any sense to me, either).

[JustSteve120]

The application has a large number of survey types (around 30), and the surveys are lists of questions that can be scored yes, no, or na. There is a separate table holds the questions. Questions can be used in multiple surveys, so it's not a 1:1 relationship. The temporary table is used to manage the questions that are being displayed from the questions table so they can be displayed on a continuous form. Once the questions are answered, they're saved to a Saved Data table and the Temporary table is deleted.

What complicates things a little bit is that the user has the ability to score the survey multiple times (multiple records) of the same survey for a surveyed party. So the there is also a record counter built into the process.

Regarding the Coc, the problem is that each application is standalone, so until the data is downloaded at the end of the month it doesn't hit a network really at all. So the CoC operation would happen on the local machine.

[twinnyfo]

Could you include a list of your table structures? I think you can do this without any temp tables. it sounds like your data may need some normalization....

[JustSteve120]

Hopefully this makes sense...

Questions Table
IDQuestion Number
Question Text Memo
TextResponse Text
Non-scored Yes/No

Survey Tool Table
IDSurveyTool Number
ToolName Text
ToolType Text

ToolQuestionJctTable

IDQuestion
IDSurveyTool

SurveyTypeJctTable
IDSurveyType
IDSurveyTool

SurveyHeaderTable
IDAuditNumber AutoNumber
SurveyNumber Text
IDProvider Number
SurveyDate Date
IDSurveyor Text
IDSurveyType Number


SurveyToolHeaderTable
SurveyNumber Text
IDSurveyTool Number
RecordNumber Number
ToolScore Number


SurveyResultsTemp
IDSurveyTool
RecordNumber
IDSurveyQuestion
DisplayNumber (for ordering questions on the continuous list)
SurveyQuestion
SurveyNumber
IDProvider
ProviderName
TextResponse
NonScored


SurveyResponseSaveTable

IDSurveyTool
RecordNumber
IDAuditQuestion
DisplayNumber
SurveyNumber
TextResponse

[twinnyfo]

Can you explain the process (brief overview) of how you generate a survey and save the results?

Although when I have copied records, I've not had problems with data loss--but what makes your problem so much trickier is that it is intermittent. I can only assume that when you do your surveys, sometimes the data copies, and other times it does not?

This is where is we can get around the Temp Table and simply create an append query that consolidates everything into your final table, we might avoid some of the problems you are having.

Sorry for asking for even more information, but we like to understand the problem a bit better before we provide any specific recommendations.

[JustSteve120]

From the provider record (who is being surveyed), the survey is launched. A header form is launched to set the header info -- date and survey type. Once survey type is selected, the survey tools are returned that can be selected from.

Selecting the tool launches a continuous form with the identified questions in a list of 50 or more questions. There is a dropdown box that allows for selection of Yes/No/NA.

The surveyor scores, and chooses to either save and close the tool or select "Next" which saves score then reloads same tool. Both close and Next moves data from Temp to Save table and clears the temp table.

The application also allows to reload a previously completed survey, including the multiple records of a tool, to edit or update a score.

[twinnyfo]

Steve,

Just on the surface, your description seems to make it clear that you can do this without using Temp Tables (unless I am missing something).

My thought is that whatever process you use to make the Temp Table records and then copy them to the permanent Table, you should be able to simple append those records to the permanent table, and then complete the surveys from that table.

Of course, this still does not "explain" your data loss. It could be a data validation issue, but it does not seem you have identified any "patterns" in your data loss.

Of course, there may be reasons why you would want to use this temp table arrangement, but based on how you use the data, it does not appear you "need" to.

[JustSteve120]

Thanks for your assistance on this. I'm going to take a look at seeing how the application can go straight to the permanent table.

Regarding the data loss, I have added a couple of data refreshes in click actions, which has seemingly stopped the problem. Not really sure why.

[twinnyfo]

Yes, your problem does sound strange-- not one that I have every encountered, before.