Insert Into fails for Access

[Pete Davis]

I have the following query, the ... represents a bunch of fields:

INSERT INTO PropertyDetails ( PropertyTypeID, ListingAgent, Address, City,
....)
SELECT [PropertyDetails].[PropertyTypeID], [PropertyDetails].[ListingAgent],
[PropertyDetails].[Address], [PropertyDetails].[City], ...
FROM PropertyDetails WHERE PropertyDetailsID=1

Now, PropertyDetailsID is an autonumber field, so it's excluded from the
field list (and it's the only field excluded). I also have some memo fields,
but I'm not sure if that matters.

When I try to execute this from a query window, I get:

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations and 0 record(s) due to validation rule
violations.

So, it's a key violation, it looks like. As a test, I removed all
relationships from the table, so the only key left was the primary key and I
still get the error.

I duplicated this table in MS SQL Server and performed the exact same query
and it works fine.

Any ideas?

Pete

 

[Cheryl Fischer]

You likely have another field with the Indexed Property set to "Yes (no
duplicates)". I was able to reproduce your error with that condition.


hth,

 

[Pete Davis]

Okay, I'm even more confused.

I backed up my database. I removed every form and table in the database
except this table.

It has only the primary key (autonumber). No matter what I do to try to add
new record (even from the DataSheet view, I get the following error:


The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
blah blah blah.

What other kind of relationship could possibly exist that could be
preventing me from adding a record and how can I possibly track it down?

Thanks.

Pete

 

[Pete Davis]

I found the problem. My indexes were corrupt apparently. I had to create a
new database and transfer everything over and then it worked.

Thanks.
Pete
--
http://www.petedavis.net

Okay, I'm even more confused.

I backed up my database. I removed every form and table in the database
except this table.

It has only the primary key (autonumber). No matter what I do to try to add
new record (even from the DataSheet view, I get the following error:


The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
blah blah blah.

What other kind of relationship could possibly exist that could be
preventing me from adding a record and how can I possibly track it down?

Thanks.

Pete

--
http://www.petedavis.net

I have the following query, the ... represents a bunch of fields:

INSERT INTO PropertyDetails ( PropertyTypeID, ListingAgent, Address, City,
...)
SELECT [PropertyDetails].[PropertyTypeID], [PropertyDetails].[ListingAgent],
[PropertyDetails].[Address], [PropertyDetails].[City], ...
FROM PropertyDetails WHERE PropertyDetailsID=1

Now, PropertyDetailsID is an autonumber field, so it's excluded from the
field list (and it's the only field excluded). I also have some memo fields,
but I'm not sure if that matters.

When I try to execute this from a query window, I get:

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations and 0 record(s) due to validation rule
violations.

So, it's a key violation, it looks like. As a test, I removed all
relationships from the table, so the only key left was the primary key

and

I
still get the error.

I duplicated this table in MS SQL Server and performed the exact same query
and it works fine.

Any ideas?

Pete