Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Primary Key Question

Discussion in 'Information Technology' started by Alastair MacFarlane, Oct 31, 2005.

  1. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Dear all,

    Is there any way in access that I can switch of the PrimaryKey constraint
    off and Append data through an action query and start the PrimaryKey
    constraint again.

    If I have a table that has:

    ID (PK) - Autonumber
    Field1

    with values of :

    ID: 1; Field1: A
    ID: 2; Field1: B
    ID: 4; Field1: C

    How can insert an ID of 3 and any filed value into the Field1 column? If you
    add another record you will get the next autonumber (5 in this case) but I
    want it to be 3. Can this be achieved? You can remove the PrimaryKey
    constraint, add the data but you then can't save the table.

    Thanks again for any thoughts.

    Alastair
     
  2. Loading...

    Similar Threads Forum Date
    Controversial Surat school puts primary kids to agni pariksha Hard Talk Apr 22, 2010
    Singh Teaches Primary Pupils About Sikh Culture Sikh Sikhi Sikhism Jan 31, 2009
    Sikh News Cold wave shuts Punjab primary schools for a week (New Kerala) Breaking News Jan 31, 2008
    A Group Of Scientists Placed 5 Monkeys In A Cage And In The Middle, A Ladder With Bananas Inspirational Stories Apr 6, 2015
    Gurus When Guru Gobind Singh Ji played Hockey (ਪੰਜਾਬੀ) History of Sikhism Apr 13, 2014

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    You did say "any thoughts"...

    The Access Autonumber field type is designed to be used as a unique row
    identifier, and is generally unfit for human consumption. If you are
    displaying the Autonumber as an ID to a user, that user needs to understand
    that there will be gaps, and that the Autonumber ID is not (always)
    sequential.

    Moreover, if you were able to alter Autonumbers as a Primary Key in a parent
    table, you'd be, at best, orphaning any child table records that pointed
    back to the parent table with that ID. At worst, you'll be irrevocably
    corrupting the connection between the two.

    If you'll describe what you are trying to accomplish (what business need are
    you trying to satisfy), the 'group readers may be able to offer alternative
    approaches.

    --
    Regards

    Jeff Boyce
    <Office/Access MVP>

    "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    > Dear all,
    >
    > Is there any way in access that I can switch of the PrimaryKey constraint
    > off and Append data through an action query and start the PrimaryKey
    > constraint again.
    >
    > If I have a table that has:
    >
    > ID (PK) - Autonumber
    > Field1
    >
    > with values of :
    >
    > ID: 1; Field1: A
    > ID: 2; Field1: B
    > ID: 4; Field1: C
    >
    > How can insert an ID of 3 and any filed value into the Field1 column? If

    you
    > add another record you will get the next autonumber (5 in this case) but I
    > want it to be 3. Can this be achieved? You can remove the PrimaryKey
    > constraint, add the data but you then can't save the table.
    >
    > Thanks again for any thoughts.
    >
    > Alastair
    >
    >
     
  4. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    While I agree whole-heartedly with Jeff's comments, you can use INSERT INTO
    queries, providing a value for the Autonumber field, and it'll work.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    > Dear all,
    >
    > Is there any way in access that I can switch of the PrimaryKey constraint
    > off and Append data through an action query and start the PrimaryKey
    > constraint again.
    >
    > If I have a table that has:
    >
    > ID (PK) - Autonumber
    > Field1
    >
    > with values of :
    >
    > ID: 1; Field1: A
    > ID: 2; Field1: B
    > ID: 4; Field1: C
    >
    > How can insert an ID of 3 and any filed value into the Field1 column? If
    > you add another record you will get the next autonumber (5 in this case)
    > but I
    > want it to be 3. Can this be achieved? You can remove the PrimaryKey
    > constraint, add the data but you then can't save the table.
    >
    > Thanks again for any thoughts.
    >
    > Alastair
    >
    >
     
  5. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Douglas and Jeff,

    I have 2 databases, one live and one backup. The contents of backup is made
    up of exported and deleted records from the live system. The client states
    that they would like to be able to un-archive some these records after a
    couple of years. I am trying to create this utility in a database
    application with a VB front end. I have tried the INSERT INTO and this does
    work (I was not sure until I tested it!). I would obviously like the records
    to be inserted into the system painlessly.

    From your wealth of experience do you see any problems with this approach?
    There are about 10 tables that may be un-archived and as long as I start
    with the one side, do you foresee any problems? It is the volume of data and
    speed that makes me think that this approach would be the best way.

    Thanks again for the group's comments and support.

    Alastair


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    > While I agree whole-heartedly with Jeff's comments, you can use INSERT
    > INTO queries, providing a value for the Autonumber field, and it'll work.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    > news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >> Dear all,
    >>
    >> Is there any way in access that I can switch of the PrimaryKey constraint
    >> off and Append data through an action query and start the PrimaryKey
    >> constraint again.
    >>
    >> If I have a table that has:
    >>
    >> ID (PK) - Autonumber
    >> Field1
    >>
    >> with values of :
    >>
    >> ID: 1; Field1: A
    >> ID: 2; Field1: B
    >> ID: 4; Field1: C
    >>
    >> How can insert an ID of 3 and any filed value into the Field1 column? If
    >> you add another record you will get the next autonumber (5 in this case)
    >> but I
    >> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >> constraint, add the data but you then can't save the table.
    >>
    >> Thanks again for any thoughts.
    >>
    >> Alastair
    >>
    >>

    >
    >
     
  6. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Re: Primary Key Question (supplemtary)

    Dear All,

    Is there a way of running an INSERT INTO query as below that does not treat
    the whole INSERT as a transaction, whereby if one insert fails the whole
    batch fails. The last thing I want to do is loop through the table to insert
    the rows one-by-one?

    INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) IN 'C:\Mydb.mdb'
    SELECT tmpWBRestore.WBID, tmpWBRestore.PKid, tmpWBRestore.WeekBegin
    FROM tmpWBRestore;

    Thanks

    Alastair



    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    > While I agree whole-heartedly with Jeff's comments, you can use INSERT
    > INTO queries, providing a value for the Autonumber field, and it'll work.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    > news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >> Dear all,
    >>
    >> Is there any way in access that I can switch of the PrimaryKey constraint
    >> off and Append data through an action query and start the PrimaryKey
    >> constraint again.
    >>
    >> If I have a table that has:
    >>
    >> ID (PK) - Autonumber
    >> Field1
    >>
    >> with values of :
    >>
    >> ID: 1; Field1: A
    >> ID: 2; Field1: B
    >> ID: 4; Field1: C
    >>
    >> How can insert an ID of 3 and any filed value into the Field1 column? If
    >> you add another record you will get the next autonumber (5 in this case)
    >> but I
    >> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >> constraint, add the data but you then can't save the table.
    >>
    >> Thanks again for any thoughts.
    >>
    >> Alastair
    >>
    >>

    >
    >
     
  7. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Alastair MacFarlane wrote:
    > Douglas and Jeff,
    >
    > I have 2 databases, one live and one backup. The contents of backup
    > is made up of exported and deleted records from the live system. The
    > client states that they would like to be able to un-archive some
    > these records after a couple of years. I am trying to create this
    > utility in a database application with a VB front end. I have tried
    > the INSERT INTO and this does work (I was not sure until I tested
    > it!). I would obviously like the records to be inserted into the
    > system painlessly.
    > From your wealth of experience do you see any problems with this
    > approach? There are about 10 tables that may be un-archived and as
    > long as I start with the one side, do you foresee any problems? It is
    > the volume of data and speed that makes me think that this approach
    > would be the best way.
    > Thanks again for the group's comments and support.
    >
    > Alastair


    It sounds like you are doing it the hard way. Normally when one needs
    to "archive" data in Access on the chance that it may be needed later or
    someone may want to view it a simple binary filed us used to indicate
    archive and all regular accesses of data simply filter out the "archived"
    data. It makes doing what you want to do far easier.

    >
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
    > message news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    >> While I agree whole-heartedly with Jeff's comments, you can use
    >> INSERT INTO queries, providing a value for the Autonumber field, and
    >> it'll work.
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    >> news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >>> Dear all,
    >>>
    >>> Is there any way in access that I can switch of the PrimaryKey
    >>> constraint off and Append data through an action query and start
    >>> the PrimaryKey constraint again.
    >>>
    >>> If I have a table that has:
    >>>
    >>> ID (PK) - Autonumber
    >>> Field1
    >>>
    >>> with values of :
    >>>
    >>> ID: 1; Field1: A
    >>> ID: 2; Field1: B
    >>> ID: 4; Field1: C
    >>>
    >>> How can insert an ID of 3 and any filed value into the Field1
    >>> column? If you add another record you will get the next autonumber
    >>> (5 in this case) but I
    >>> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >>> constraint, add the data but you then can't save the table.
    >>>
    >>> Thanks again for any thoughts.
    >>>
    >>> Alastair


    --
    Joseph Meehan

    Dia duit
     
  8. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Thanks again Joseph,

    I quite agree that this is the hard way, but in this system I think it is
    the most effective way. I will take your advice on board for smaller
    systems. Thanks again.

    Alastair MacFarlane

    "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:si59f.77467$Hs.24924@tornado.ohiordc.rr.com...
    > Alastair MacFarlane wrote:
    >> Douglas and Jeff,
    >>
    >> I have 2 databases, one live and one backup. The contents of backup
    >> is made up of exported and deleted records from the live system. The
    >> client states that they would like to be able to un-archive some
    >> these records after a couple of years. I am trying to create this
    >> utility in a database application with a VB front end. I have tried
    >> the INSERT INTO and this does work (I was not sure until I tested
    >> it!). I would obviously like the records to be inserted into the
    >> system painlessly.
    >> From your wealth of experience do you see any problems with this
    >> approach? There are about 10 tables that may be un-archived and as
    >> long as I start with the one side, do you foresee any problems? It is
    >> the volume of data and speed that makes me think that this approach
    >> would be the best way.
    >> Thanks again for the group's comments and support.
    >>
    >> Alastair

    >
    > It sounds like you are doing it the hard way. Normally when one needs
    > to "archive" data in Access on the chance that it may be needed later or
    > someone may want to view it a simple binary filed us used to indicate
    > archive and all regular accesses of data simply filter out the "archived"
    > data. It makes doing what you want to do far easier.
    >
    >>
    >>
    >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
    >> message news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    >>> While I agree whole-heartedly with Jeff's comments, you can use
    >>> INSERT INTO queries, providing a value for the Autonumber field, and
    >>> it'll work.
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no e-mails, please!)
    >>>
    >>>
    >>>
    >>> "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    >>> news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >>>> Dear all,
    >>>>
    >>>> Is there any way in access that I can switch of the PrimaryKey
    >>>> constraint off and Append data through an action query and start
    >>>> the PrimaryKey constraint again.
    >>>>
    >>>> If I have a table that has:
    >>>>
    >>>> ID (PK) - Autonumber
    >>>> Field1
    >>>>
    >>>> with values of :
    >>>>
    >>>> ID: 1; Field1: A
    >>>> ID: 2; Field1: B
    >>>> ID: 4; Field1: C
    >>>>
    >>>> How can insert an ID of 3 and any filed value into the Field1
    >>>> column? If you add another record you will get the next autonumber
    >>>> (5 in this case) but I
    >>>> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >>>> constraint, add the data but you then can't save the table.
    >>>>
    >>>> Thanks again for any thoughts.
    >>>>
    >>>> Alastair

    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Re: Primary Key Question (supplemtary)

    The only way would be to set up a loop, and insert the rows one by one:

    Dim dbCurr As DAO.Database
    Dim rsCurr As DAO.Recordset
    Dim strSQL As String

    Set dbCurr = CurrentDb()
    Set rsCurr = dbCurr.OpenRecordset("SELECT WBID, PKid, WeekBegin FROM
    tmpWBRestore")
    With rsCurr
    Do While .EOF = False
    strSQL = "INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) "
    & _
    "IN 'C:\Mydb.mdb' " & _
    "VALUES(" & !WBID & ", " & !PKid & ", " & _
    Format(!WeekBegin, "\#mm\/dd\/yyyy\#") & ")"
    dbCurr.Execute strSQL, dbFailOnError
    Loop
    .Close
    End With

    Set rsCurr = Nothing
    Set dbCurr = Nothing

    This assumes that WBID and PKid are both numeric fields, and that WeekBegin
    is a date field.

    BTW, I agree with Joseph: this really doesn't seem to be very efficient.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    news:eaP1C1V3FHA.2640@TK2MSFTNGP09.phx.gbl...
    > Dear All,
    >
    > Is there a way of running an INSERT INTO query as below that does not
    > treat the whole INSERT as a transaction, whereby if one insert fails the
    > whole batch fails. The last thing I want to do is loop through the table
    > to insert the rows one-by-one?
    >
    > INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) IN 'C:\Mydb.mdb'
    > SELECT tmpWBRestore.WBID, tmpWBRestore.PKid, tmpWBRestore.WeekBegin
    > FROM tmpWBRestore;
    >
    > Thanks
    >
    > Alastair
    >
    >
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    >> While I agree whole-heartedly with Jeff's comments, you can use INSERT
    >> INTO queries, providing a value for the Autonumber field, and it'll work.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    >> news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >>> Dear all,
    >>>
    >>> Is there any way in access that I can switch of the PrimaryKey
    >>> constraint off and Append data through an action query and start the
    >>> PrimaryKey constraint again.
    >>>
    >>> If I have a table that has:
    >>>
    >>> ID (PK) - Autonumber
    >>> Field1
    >>>
    >>> with values of :
    >>>
    >>> ID: 1; Field1: A
    >>> ID: 2; Field1: B
    >>> ID: 4; Field1: C
    >>>
    >>> How can insert an ID of 3 and any filed value into the Field1 column? If
    >>> you add another record you will get the next autonumber (5 in this case)
    >>> but I
    >>> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >>> constraint, add the data but you then can't save the table.
    >>>
    >>> Thanks again for any thoughts.
    >>>
    >>> Alastair
    >>>
    >>>

    >>
    >>

    >
    >
     
  10. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Re: Primary Key Question (supplemtary)

    Douglas,

    I have learnt my lesson and won't do it this way again. I will alter my
    strategy for future projects, and I appreciate your comments and sample
    code.

    Alastair


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23OgZV5W3FHA.400@TK2MSFTNGP09.phx.gbl...
    > The only way would be to set up a loop, and insert the rows one by one:
    >
    > Dim dbCurr As DAO.Database
    > Dim rsCurr As DAO.Recordset
    > Dim strSQL As String
    >
    > Set dbCurr = CurrentDb()
    > Set rsCurr = dbCurr.OpenRecordset("SELECT WBID, PKid, WeekBegin FROM
    > tmpWBRestore")
    > With rsCurr
    > Do While .EOF = False
    > strSQL = "INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) "
    > & _
    > "IN 'C:\Mydb.mdb' " & _
    > "VALUES(" & !WBID & ", " & !PKid & ", " & _
    > Format(!WeekBegin, "\#mm\/dd\/yyyy\#") & ")"
    > dbCurr.Execute strSQL, dbFailOnError
    > Loop
    > .Close
    > End With
    >
    > Set rsCurr = Nothing
    > Set dbCurr = Nothing
    >
    > This assumes that WBID and PKid are both numeric fields, and that
    > WeekBegin is a date field.
    >
    > BTW, I agree with Joseph: this really doesn't seem to be very efficient.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    > news:eaP1C1V3FHA.2640@TK2MSFTNGP09.phx.gbl...
    >> Dear All,
    >>
    >> Is there a way of running an INSERT INTO query as below that does not
    >> treat the whole INSERT as a transaction, whereby if one insert fails the
    >> whole batch fails. The last thing I want to do is loop through the table
    >> to insert the rows one-by-one?
    >>
    >> INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) IN 'C:\Mydb.mdb'
    >> SELECT tmpWBRestore.WBID, tmpWBRestore.PKid, tmpWBRestore.WeekBegin
    >> FROM tmpWBRestore;
    >>
    >> Thanks
    >>
    >> Alastair
    >>
    >>
    >>
    >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    >>> While I agree whole-heartedly with Jeff's comments, you can use INSERT
    >>> INTO queries, providing a value for the Autonumber field, and it'll
    >>> work.
    >>>
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no e-mails, please!)
    >>>
    >>>
    >>>
    >>> "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    >>> news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    >>>> Dear all,
    >>>>
    >>>> Is there any way in access that I can switch of the PrimaryKey
    >>>> constraint off and Append data through an action query and start the
    >>>> PrimaryKey constraint again.
    >>>>
    >>>> If I have a table that has:
    >>>>
    >>>> ID (PK) - Autonumber
    >>>> Field1
    >>>>
    >>>> with values of :
    >>>>
    >>>> ID: 1; Field1: A
    >>>> ID: 2; Field1: B
    >>>> ID: 4; Field1: C
    >>>>
    >>>> How can insert an ID of 3 and any filed value into the Field1 column?
    >>>> If you add another record you will get the next autonumber (5 in this
    >>>> case) but I
    >>>> want it to be 3. Can this be achieved? You can remove the PrimaryKey
    >>>> constraint, add the data but you then can't save the table.
    >>>>
    >>>> Thanks again for any thoughts.
    >>>>
    >>>> Alastair
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  11. Guest

    Guest
    Expand Collapse
    Guest

    BTW, you have a slight mis-understanding of "Primary Key
    Constraint" and "Autonumber".

    The Constraint, which in Access/Jet is part of an Index, can
    be switched on and off: you can drop the index, then re-create
    the index later.

    The data type (Autonumber), can be changed to a number,
    but can't be changed back to Autonumber.

    You can have an Autonumber field which is not indexed,
    does not have constraints, and is not a primary key. You
    can have duplicate values in the field if you use an append
    query to append records.

    You can also have a number field, not Autonumber, with a
    Primary Key Index, (which includes the constraint that values
    must be non-null and unique). The primary key index/constraint
    won't let you append duplicate values.

    (david)


    "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    > Dear all,
    >
    > Is there any way in access that I can switch of the PrimaryKey constraint
    > off and Append data through an action query and start the PrimaryKey
    > constraint again.
    >
    > If I have a table that has:
    >
    > ID (PK) - Autonumber
    > Field1
    >
    > with values of :
    >
    > ID: 1; Field1: A
    > ID: 2; Field1: B
    > ID: 4; Field1: C
    >
    > How can insert an ID of 3 and any filed value into the Field1 column? If

    you
    > add another record you will get the next autonumber (5 in this case) but I
    > want it to be 3. Can this be achieved? You can remove the PrimaryKey
    > constraint, add the data but you then can't save the table.
    >
    > Thanks again for any thoughts.
    >
    > Alastair
    >
    >
     
  12. Guest

    Guest
    Expand Collapse
    Guest

    The current version of Jet (Jet 4 Sp8) corrupts the
    autonumber seed when you append to linked tables.

    http://support.microsoft.com/?scid=kb;en-us;884185&spid=2509&sid=106

    If you use INSERT INTO to insert values into an Autonumber
    field in a linked table you will probably meet this bug.

    (david)


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23q5GnPV3FHA.1416@TK2MSFTNGP09.phx.gbl...
    > While I agree whole-heartedly with Jeff's comments, you can use INSERT

    INTO
    > queries, providing a value for the Autonumber field, and it'll work.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    > news:ufRm3IU3FHA.3868@TK2MSFTNGP12.phx.gbl...
    > > Dear all,
    > >
    > > Is there any way in access that I can switch of the PrimaryKey

    constraint
    > > off and Append data through an action query and start the PrimaryKey
    > > constraint again.
    > >
    > > If I have a table that has:
    > >
    > > ID (PK) - Autonumber
    > > Field1
    > >
    > > with values of :
    > >
    > > ID: 1; Field1: A
    > > ID: 2; Field1: B
    > > ID: 4; Field1: C
    > >
    > > How can insert an ID of 3 and any filed value into the Field1 column? If
    > > you add another record you will get the next autonumber (5 in this case)
    > > but I
    > > want it to be 3. Can this be achieved? You can remove the PrimaryKey
    > > constraint, add the data but you then can't save the table.
    > >
    > > Thanks again for any thoughts.
    > >
    > > Alastair
    > >
    > >

    >
    >
     

Share This Page