Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: SET IDENTITY_INSERT question

    I've used SET IDENTITY_INSERT with SQL server but I'm not sure how to trigger it within Access.

    There are many articles on the Internet on this subject but most of them use SQL Server as an example and just a few mention that this is possible in Access too - but how?

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you mean while using Access as an interface to SQL Server, or are you inserting into an MS Access table?

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    2

    Question Identity_Insert ON

    Originally posted by blindman
    Do you mean while using Access as an interface to SQL Server, or are you inserting into an MS Access table?

    blindman
    Thank you for replying.

    I'm interested in both cases.
    As I have mentioned I know how to do it in SQL Server.
    My wife's company uses Access & they want to upsize their Access web module to MSDE with Access as an interface.

    For now I use just the INSERT statement in Access and it works - it inserts old, deleted IDs into autonum columns, but it doesn't works for all tables for some reason.
    I'd like to know what object can be used for setting Identity_insert ON in Access

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think there is an Access command that will do this.

    I found this in the help system. Good luck!
    ------------------------------------------------------------
    About appending records with AutoNumber fields
    Depending on how you design an append query, Microsoft Access can automatically add new AutoNumber values, or retain the values from the original table.

    To have Microsoft Access add AutoNumber values automatically, don't drag the AutoNumber field to the query design grid when you create the query.
    With this method, Microsoft Access appends records and automatically inserts AutoNumber values. The first record appended has a value that is one larger than the largest entry that was ever entered in the AutoNumber field (even if the record that contained the largest AutoNumber value has been deleted).

    To keep the AutoNumber values from the original table, drag its AutoNumber field to the query design grid when you create the query.
    If the AutoNumber field in the table you're appending to is a primary key, and the original table and the table you're appending to contain duplicate AutoNumber values, use the first method instead.

Posting Permissions

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