Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Arrow Unanswered: Append Query to Attached SQL table with an Autonuber Field is not Working

    Hello - I'd really appreciate some help with this....

    I have a table that is attached from our web server SQL database into an MsAccess 2003 database.

    When the table was created in SQL, the ID field was created as an int then specified as an identity and given an increment amount.

    As an attached table in MsAccess, the design view looks like this:
    ID - Autonumber - Primary Key
    Name - text
    Address - text
    ProjectNo - text
    OrderDate - date

    Here's the sql view of the Ms Access query:
    INSERT INTO dbo_tblOrders ( Name, Address, ProjectNo, OrderDate )
    SELECT tblOrders.Name, tblOrders.Address, tblOrders.ProjectNo, tblOrders.OrderDate
    FROM tblOrders;

    When I try to run the query, the error I get is just a generic Access error without details of what is wrong. "Microsoft Office cannot append all the records... set (0) fields to null .... didn't add (1) record(s) to the table due to key violations... etc. That's as much as I know of the error.

    Since the only key I have is on ID, then my guess is that it doesn't like that I'm not appending a value for ID. But I want it to increment the value itself.

    I did try appending a value in ID - but that gave me the same error.

    Is there a way to append from Ms Access to SQL where the table has an autonumber field or not?

    Is it possible that this used to work in previous MsAccess versions but has changed? I know I've done this before... so it is driving me crazy.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    AutoNumbers should work the way you're trying...
    Have you checked the field lengths in the source and destination tables?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2008
    Posts
    4
    I had not looked at every field, but I went back and did that this morning. The fields all match, but I cannot append from Ms Access to this SQL table.

    But it sounds like at least one person says I should be able to do it...

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you changed the ID field to an Autonumber field in SQL Server, you will need to relink the table. Access will not know the table has an Autonumber field until you relink.

  5. #5
    Join Date
    Jan 2008
    Posts
    4
    From what I understand, SQL doesn't have an autonumber data type - but uses identity / autoincrement to accomplish the same thing. When I attach the table from SQL in the Access database, Access automatically defines the type as an Autonumber. The datatype hasn't changed.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You are correct. The terminaology is different in SQL Server but the function is the same. Have you tried relinking the table?

    Also, can you add a record in the table using Access? (Open Access, open the table, add a record). Does the ID get generated?

    Can you add a record in SQL Server? Does it generate the ID field?

  7. #7
    Join Date
    Jan 2008
    Posts
    4

    Smile

    The suggested task of adding a record in SQL Server solved the mystery. When I typed in the record, I discovered that a field I didn't realize was required was not being included in my append query. I doesn't appear as a "key" field in the design of the Access table. But when I typed in the exact record I was trying to append and skipped a field - that's when I got the valuable error message that told me the problem! Such a silly error - but I won't make it again! Thank you for your help.

Posting Permissions

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