Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Unanswered: Append query from Access Table to Linked SQL Server Table Failing

    Strange one here - I am posting this in both SQL Server and Access forums

    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

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    I think the problem is you NEED a primary key or index on the SQL table.

    The reason for this error will vary depending on the data provider. In the case of SQL Server OLE DB Provider, this error will occur only because the base table does not have a Primary Key, a Unique Constraint or a Unique Index. With the Jet OLE DB Provider, this error will also occur because the base table does not have a Primary Key or because the Jet Provider cannot determine the Key column(s) (In Access the Primary key MUST be included in the Select statement).
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Jun 2004
    Posts
    8
    SCIROCCO,

    Yes that does make sense. Access asks what the unique key is when you try to link a table without one. I tried to add a unique index to Colors in SQL and relinked. But still no joy.

    My query does include the primary key.

    Also, I don't think Access is using OLE DB but ODBC. I am still on Access 2000.

    Does that give any more clues?

    Thanks,
    Brad

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    What about security issues? When you linked the SQL table to your Access database did you save the password? Does Access have read/write permissions on the SQL table? Could that be it?
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Jun 2004
    Posts
    8
    SCIROCCO,

    I think I figured it 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •