Append query - Yes at the prompts

[Adam]

Hi All,

I have a database which has started duplicating records to a reason
unknown to me. Whilst I investigate this issue I want users to still be
able to use the database.

As a temporary solution I have setup a 'Key' field in a table, and
setup an append query to append the records from one table to another,
and append to the 'Key' field a field which is unique. It is the
'AuditID' field and the 'QuestionID' consolidated into the one field.

On the table it is appending too the key field has been set Index with
no duplicates.

When i run the append query myself i have to press Yes at the prompt
which tells me it is validating an Index rule and would i like to run
the query for the remaining records anyway. When I select Yes it runs
as i wish, and brings in no duplicate records.

However I have a macro setup that runs this append query. I cannot see
a setting anywhere that will select Yes at the Index prompt screen.

Does anyone know how to get around this?

Many Thanks!!

Adam

 

[Stumac]

In the line before the query is run on the macro you cn use the SetWarnings
command to turn the warnings off.

Hth

Stu

 

[Adam]

Yes, but it selects No at the key prompt by default so turning off the
warnings doesnt help.

 

[John Vinson]

Hi All,

I have a database which has started duplicating records to a reason
unknown to me. Whilst I investigate this issue I want users to still be
able to use the database.

As a temporary solution I have setup a 'Key' field in a table, and
setup an append query to append the records from one table to another,
and append to the 'Key' field a field which is unique. It is the
'AuditID' field and the 'QuestionID' consolidated into the one field.

Be aware that you should *NOT* be creating a redundant composite field
for this purpose! You can - and probably should - create a unique
Index (either as a Primary Key or as a separate index) on the two
fields. It's not necessary to consolidate the fields into one; just
use the Indexes tool on the toolbar; type a unique name for the index
in the left column of the indexes window; and select the two
fieldnames on that line and the next line in the grid. Specify that
the index should be unique.

On the table it is appending too the key field has been set Index with
no duplicates.

Reasonable, but you do need the other index.

When i run the append query myself i have to press Yes at the prompt
which tells me it is validating an Index rule and would i like to run
the query for the remaining records anyway. When I select Yes it runs
as i wish, and brings in no duplicate records.

What is the actual error message you're getting???

However I have a macro setup that runs this append query. I cannot see
a setting anywhere that will select Yes at the Index prompt screen.

Does anyone know how to get around this?

Correct the indexes so you don't get the warning, would be my first
step! Or, execute the query from VBA code, not from a macro, and
handle the error there.

John W. Vinson[MVP]