Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Autonumber in Sql Server (2005)

    Hi, I've got a table in sql server 2005 that I need the equiv. of the autonumber in Access. From what I've read it appears that the timestamp is used for this purpose as it autogenerates. This is working fine until I go to edit or delete a rown in the database. The I get the error:

    Operand type clash: timestamp is incompatible with sql_variant

    I've done a search of the internet for this error with no luck. Only one post that never seemed to be answered. Does this make sense to anyone, or is there another way to generate an autonumber other than a timestamp that may not produce an error?

    Thanks!
    Leesha

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber in Sql Server (2005)

    Hi Leesha,
    Open the table in design view. Right click on the table in question and then click on design. Click on the field you want your autonumber and in the bottom right hand pane you will see something similar to the attached jpg. Set the datatype to int and expand Identity Specification. Change the value for (Is Identity) to Yes. Set the Identity Increment and Identity Seed to 1 and Save your way out.
    Per SQL Books OnLine:
    The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.
    Per Me: <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    When using Access as a front end, the TimeStamp datatype is used by Access to help it distinguish if Access may modify a record or not. I always make sure a timestamp field is present, as well as ensuring there is a primary key, in each table I use with Access.
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber in Sql Server (2005)

    Thank you! That worked beautifully! And I really appreciate the detail so I can understand and visualize it.
    Now, re the timestamp and Access using it to determine if a field can be modified or not. How does the timestamp affect this?
    Thanks!
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber in Sql Server (2005)

    When you try to update a record in a SQL server table from Access, Access compares the record in the database with the record in memory to check whether the record in the database has been changed since it was loaded into memory (presumably by another user); if it detects a difference, the update is aborted and you get a message that "The record has been changed by another user since you started editing it..."
    How Access compares the records depends on the presence of a TIMESTAMP field in the table.
    If the table contains a TIMESTAMP field, Access only looks at this field - fast, efficient and accurate.
    If the table does *not* contain a TIMESTAMP field, Access compares the values of *all* fields, which is much slower, and what's worse, often causes the error message mentioned above to appear even if the record in the database hasn't been changed. This is because Access and SQL Server store some types of data in a different way, and small rounding errors may occur in the conversion. So Access thinks that the record in the database has changed while it actually hasn't.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber in Sql Server (2005)

    Thanks Hans! That makes perfect sense!

    Appreciate it,
    Leesha

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber in Sql Server (2005)

    A further note on this subject and the way the Identity property works in SQL Server which is a little different from the way Access does AutoNumbers. SQL Server doesn't actually assign the value to the field until Access tells it to save the record. In an Access table, the value is established when you actually start the entry of a new record. So DAO code that was written using native Access tables can determine the value of a key before the record is actually saved, but with SQL Server attached tables, you will have to go back and find the value after the record is saved. A particular nusiance if you are using unbound forms and saving a header type record and one or more linked records, but that's the nature of the beast.
    Wendell

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber in Sql Server (2005)

    Oh WOW! That is a royal pain. Thanks for the heads up!

    Leesha

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Autonumber in Sql Server (2005)

    When you say to go back and find the value after the record is saved, what do you do to get the autonumber value?
    Do you use a SELECT or DLOOKUP and if so, what criteria do you use?
    A prblem may be that another user has written one while you are going back to read the last record?

    Would you just update a table prior t writing the record away with a number that can be used to read the record back again?

    Any other suggestions ?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber in Sql Server (2005)

    You can use

    SELECT SCOPE_IDENTITY()

    to retrieve the most recently added identity (autonumber) value within the current scope.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Autonumber in Sql Server (2005)

    What is the current scope?
    I'm still learning about SQL server?

    I am referring to VBA in Access as a front end. Sorry i should have said that earlier.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber in Sql Server (2005)

    I don't use SQL Server so the following is air code, I have no way to test it.

    Dim strSQL As String
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.Open ...

    strSQL = "INSERT INTO Employees (LastName, FirstName) VALUES ('Thorn', 'Pat')"
    cnn.Execute strSQL
    strSQL = "SELECT SCOPE_IDENTITY()"
    Set rst = cnn.Execute(strSQL)
    lngID = rst.Fields(0)

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Autonumber in Sql Server (2005)

    Thanks Hans, but can another user add a record before the SELECT SCOPE_IDENTITY?

    I appreciate the code snippet.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber in Sql Server (2005)

    See here

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Autonumber in Sql Server (2005)

    Thanks Hans

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonumber in Sql Server (2005)

    >>When you say to go back and find the value after the record is saved, what do you do to get the autonumber value?
    Do you use a SELECT or DLOOKUP and if so, what criteria do you use?<<

    We usually use a DLookup or DMax (depending on the situation), and base it on several fields. For example, if writing a billing transaction, we might use the Customer ID and a Date/Time field; figuring that no-one else has written a record for the same Customer at the same exact time. Just make sure that whatever fields you typically use are indexed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Page 1 of 2 12 LastLast

Posting Permissions

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