Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: Setting new Identity Column Value from Access

    I was using an AutoNumber field to generate new Job numbers in my Access system. I'm in the process of converting the Access backend to SQL Server.

    In the Access only system, when it was time to start a new year of Job numbers, I would run the following code, using (for this year) "20070000" as the value for 'Me!CurrentJobRecordNumber'. I did not think this would work with a SQL Server Identity Column table. The JobID is the Identity column. Here is my concern, it works! But I'm thinking it won't keep working, because I don't think it should work in the first place.

    Any comments? (besides, "It works, leave it alone") What I would really like to know is why it works.

    Code:
      QryTxt = "INSERT INTO tblJob (JobID) " & _
                "SELECT " & Me!CurrentJobRecordNumber & " AS CurrJobID;"
      DoCmd.SetWarnings False
      DoCmd.RunSQL QryTxt
      DoCmd.SetWarnings True

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It shouldn't work What happens if you remove the setwarnings stuff? Have you set IDENTITY_INSERT anywhere?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Ops. Please skip this one response
    Last edited by GolferGuy; 08-22-07 at 15:31. Reason: Response got away from me. ?????

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    OK, this is wierd! I have both SQL 2000 and 2005 installed on my machine. The instance I'm using is VICRAUCH, on machine VicRauch. So, when I look at it with 2005, I see: VICRAUCH\VICRAUCH(SQL Server 8.0.2039 - VicRauch\Vic) I'm signed on as Vic. And, I believe the 8.0.2039 means this instance is 2000. In Query Analyzer (2000) I have this at the top of the Object Browser: VICRAUCH\VICRAUCH(VICRRAUCH\Vic)

    When I look at my table that holds the next JobID, in 2005 I see whatever I have changed it to in my normal form (the one the executes the code I showed in the first post). But when I look at this same table with the 2000 Query Analyzer, I see an old number, but still a number that has been manipulated with this same code.

    Any ideas what SQL is doing to me, or better yet, what am I doing to myself here?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Vic, you asked for comments, so here's my 50p worth (you know its worth that much ).

    Primary keys should have no meaning - if you're setting primary keys to meaningful values, then you're going wrong somewhere!

    Why are you using an IDENTITY column? You should really use PRIMARY KEY as this is standard, portable and not SQL Server proprietary.

    Someone correct me if I'm wrong please
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Someone correct me if I'm wrong please
    Ok.

    Sorry George but that is total nonsense. Try googling surrogate keys vs natural keys. There are differering opinions & and an astonishing amount amount of text dedicated to the discussion but it is certainly not accepted wisdom that primary keys should be meaningless. I don't accept it anyhoo.


    Quote Originally Posted by georgev
    Why are you using an IDENTITY column? You should really use PRIMARY KEY as this is standard, portable and not SQL Server proprietary.
    I don't quite understand this one. Identities & primary keys are not mutually exclusive. In fact, it would be a rare circumstance that you would have an identity column that was not the sole primary key field. As such, you don't have an option of one over the other.

    Agreed - identities are propietry. So is sp_addlinkedsvr & TOP Sometimes you have to balance portability against functionality. Celko wouldn't agree but I think most would.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    George and Pootle,
    Thanks, that was fun.

    George,
    I'm not designing this thing from scratch, I'm converting from Access to SQL Server. And, the client is not wanting to invest in a whole new design of their database. They are OK with the conversion, but not really in a whole new design. Right now I have an autonumber field, and the Identity column is the closest thing to that. I really don't think there is ever going to be any concern about being portable to any other system. SQL Server will be just fine for the rest of our lives. Famous Last Words! But, that is the clients thinking, so that is where we are.

    Anyone: I'm still wondering why SQL Server is allowing this change to an Identity column. Any ideas what is going on?

    I have set IDENTITY_INSERT to ON for another table, but this still works. I have removed SetWarnings False, and the only difference is that I get a "warning" that my code is about to do an update on a table.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Vic - Is the question here why you're able to set the autonumber value to any number you designate as long as it doesn't duplicate an existing number? Maybe I'm misunderstanding but if I run an append query to append records and populate the autonumber field (SQL Server or MSAccess), I can populate the autonumber field with any number as long as I don't break the rules by duplicating the value (and make sure to populate it with a number.)

    I can't recall though if I was able to do this with Access 97 (I'm using Access 2000/2003 SQL Server 2000/2005). But I may have mis-interpreted your question.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Vic

    Can you post complete DDL for your SQL Server table (see the SQL Server FAQ for more info on how to do this)? Is this in 2000 or 2005? You are using linked tables from an mdb right? Or is it in adp?
    And you are executing something like:
    Code:
    INSERT INTO tblJob (JobID) 
    SELECT 20070000 AS CurrJobID
    ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by GolferGuy
    I have set IDENTITY_INSERT to ON for another table
    Did you turn it off after you used it?
    I've not dabbled in IDENTITY_INSERT often, but something in the back of my head is telling me that this is a global setting, not just a single table setting.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is per table but you can only have it set to on for one table within the session.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Paul, I'm using Access XP (2002) and the DB itself is in 2000 format. What you have to say makes sense as I read it, then when I do some pondering, I start wondering why it works. Identity columns have been very confusing to me.

    pootle, I have attached the DDL. I'm using SQL 2000, Access XP (2002) and the DB is in 2000 format. The FE is an Access MDB, and the SQL tables are linked using the Link Table manager within Access. The code you posted is the code I'm using (please see my first post in this thread). What Paul said makes sense, but I still thought that I would have to do this in SQL, rather than Access. I like that it works in Access as this is only done by the client once a year. As far as IDENTITY_INSERT goes, I have not messed with it with this situation at all. I DID set IDENTITY_INSERT to on for another table while I had the Query Analyzer open, just to be sure it was NOT on for tblJob. But within the Access routine that I'm currently working with, IDENTITY_INSERT is not mentioned at all.

    All, Thanks for your replies.
    Attached Files Attached Files

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - edited (simplified) your DDL. If you run this in QA then it fails as expected:
    Code:
    
    CREATE TABLE [dbo].[tblJob] (
    [JobID] [int] IDENTITY(20071800, 1)NOTNULL,
    [ProjectID] [int] NULL,
    [PlanID] [int] NULL,
    )ON [PRIMARY]-- TEXTIMAGE_ON [PRIMARY]
    GO
    INSERT INTO [dbo].[tblJob] (ProjectID)
    SELECT 1
     
    INSERT INTO [dbo].[tblJob] (JobID)
    SELECT 20071805
     
    SELECT *
    FROM [dbo].[tblJob]
     
    DROP TABLE [dbo].[tblJob]
    I'll try via a linked table tonight at home. I guess access must be setting IDENTITY_INSERT in the background but I'll need to check profiler to be sure.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not to change the subject Vic but I went to your website and downloaded the samplepopup.mdb database. I wanted to take a look at it but when I click on it, Access comes up with an error - "Cannot open this file."

    - Update - disregard - I had to "Unblock it" first in the properties.

    Your website says it's designed to run Application without Access showing. Is there something the mdb is supposed to do that I'm not seeing other than the popup form?

    I added a little something to your samplepopup.mdb (I hope you don't mind) - check out this example if you get a chance - I hope you like it.
    Attached Files Attached Files
    Last edited by pkstormy; 08-23-07 at 22:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Paul,
    The popup form, on the right hand side, has a graphic of the shortcut properties window and how to setup the shortcut's properties to keep Access minimized when it opens. When I first wrote this, it was VERY clear to me. But, having just reopened it, I now see there is nothing to tells a new person to create a shortcut, setup the shortcut as the graphic shows, and then Access will stay minimized. Bad boy Vic!

    I had seen the routine you put in, and thought someday when I have nothing else to do, AND remember that is something I could do, I would change it. So thank you for saving me the time. Is this your own code, or if not, do you remember where you got it?

    PS: I don't mind!

Posting Permissions

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