SELECT..INTO

[scasystems]

In MsAccess trying to do an append query. but getting cannot do due to duplicates.
The table I am selecting into has no records. It has no validation rules except primary key.
The table I am selecting from is only indexed on the primary key.
The weird and wonderful nature of the beast is that after extensive analysis the append query drops out at different
records (i.e. if say yes then compare diffrences between two tables diffrent records are affected each time)
Anybody help. This is too much.

P.S The source records are from a linked foxpro table

 

[Scoty]

Scasystems,
Does you source table have the same uid?. If so and if you are trying to copy all this maybe part of the append problem. I have had several situation in which the uid from one table would match the uid on another table. thus causing dups (not dup records but dup on an Indexed uid. If this is the case rather than runing your append query with the * (all fields) just run it with every field but the uid. Give it a try and let me know how it turns. Also on the secon part of your question this is just the way access is. For the most part it is a great program but it is buggy as all get out. And just try to find answers from uncle billy.


PS.
Next time please post in correct forum for a faster response.

Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[scasystems]

Will try what you suggest. But here's another problem I have found. Using the design query wizard from access (ok it's an access problem but here goes) to find duplicates on the source table it finds about 10 duplicates each time it is run.
But each run produces diffrent duplicates.
When these are checked there are no duplicates.
There are about 20,000 records in the source table.

 

[Scoty]

scasystems,
Please post the sql from your query. I'll mull it over

Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[scasystems]

"INSERT INTO Macs_Key(id, asset_rec, site_no, posn_ref, date_in) " & _
"SELECT id, assetid_id, site_id, posn_ref, date_in " & _
"FROM macs WHERE id>17"

The fields are defined the same. The only difference is that macs table is a foxpro table linked within access.
The only field that has any kind of validation is the
macs.id which is an access table with primary key.

Done loads, and loads of tests, running the append query within access provides different results ALL the time.

Would much appreciate any help as this is causing major headache (from the simples of simples queries)
cheers

 

[CCLINT]

Try repairing and compacting the MS Mdb first.

What happens then if you create a table in the MDB called Macs_Key, and it is identical to the Fox table, and then you append to that instead? Do you also get a duplication error?

Are you using ACESS '97 or 2000? [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[scasystems]

Access Database (Access Tables Only)
====================================
Macs_Key

Access Database (Reports, Queries etc)
=====================================
-> Macs_Key (Acces Link)
-> Macs (external Foxpro link)

This is the situation. Wanting to read key fields from the macs table and place in macs_key.
Using access only when I run the append query its comes up with (5) due to key violations. Update Yes/No NO
Run query again and may get (6) or (8) and this is on the same data set.
So using VB code decided to update each record at a time
with two recordsets and .addnew .update.
Similar kind of error situation it would do so many then produce a key violation error at record 2004 say.
Run again (after emptying the macs_key table) and it would
produce the same error but at a different record.

I have compacted and repaired. same result.

Used the sql wizard to find duplicates on the macs table it would give duplicates but these would change every time it was run.

Have been unable to find any mention of this as a bug.

Help! Or may end using postal service to update records