Append query from Access Table to Linked SQL Server Table Failing

[bradmathews]

Strange one here - I hope there are some SQL Server gurus around as well.

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
Brad

 

[PHV]

Doesn't have the table linker manager created a virtual primary key for dbo_Colors ?
If yes, you may consider a one shot action query like this:
DoCmd.RunSQL "DROP INDEX PrimaryKey ON dbo_Colors;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bradmathews]

Thanks for your quick reply!

I tried your suggestion but I get a message that there is no such index. I also tried the individual field indexes (Colors_Access has a compound index) and it didn't like those either.

I had already tried adding an index to the SQL table and re-linking. I also added the same index to my test copy of Colors_Access and it works there just as it did without the index.

Any other ideas?
- Brad

 

[PHV]

When you go in design view to dbo_Colors, display the index windows to be sure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MsMope]

have you tried setting your identity_insert to OFF/ON? I have found if I turn it off in SQL I am able to append records.

 

[bradmathews]

I got it figured out.

Apparently a bit field in SQL server can be Null!?!?!?

There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.

When I checked Allow Nulls on the bit field and re-linked, my query worked.

I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.

Thanks for your help,
Brad