Primary key incrementation

Question

Primary key incrementation

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

sgmunson - Wednesday, June 13, 2018 6:57 AM
Matt Miller (4) - Friday, June 8, 2018 5:08 PM
sgmunson - Friday, June 8, 2018 6:35 AM
xboxown - Thursday, June 7, 2018 12:24 PM
Luis Cazares - Thursday, June 7, 2018 12:16 PM
xboxown - Thursday, June 7, 2018 11:21 AM
OK. I will explain how this is working out. I have Microsoft Access front end linked to Microsoft SQL. At the top form it is coming from the table tblCompany. At the bottom form are bunch of tab subforms each are linked to different tables. For example, tables named tblContacts, tblStock, tblProducts, etc. Before the migration to the Microsoft SQL when I enter any field in the top form whether it being sale type, company name, etc it automatically buts Cust Account # (which is the primary key from the tblCompany) in there. At the bottom I can add values or select from dropdownlist. Now that I have migrated to Microsoft SQL the cust account # remains to be blank. I can no longer select anything at the sub form because it complains the CompanyID is null and this is a primary key and it cannot be null. This is the reason why I am here.
I have a suggestion to do but that is almost a hack kind of thing this is why I am coming here in hopes there is away around this without me going through a hack. Now my original suggestion was when a person click New on the front end it opens a window asking the user to fill everything at the top end and click insert. It inserts it in the table in the SQL and refresh the form and thus companyID have being generated and now the person can do any modification at the bottom of the form. My problem with this suggestion is that it is not guaranteed it will work since they are all inter connected in the diagram of Microsoft SQL Server Management. If there is an alternative to this I am all ears.
If you were using SQL Server 2012 or something more recent, I would have suggested to use a Sequence. Since you're posting in the 2008 forum, I guess that you're on that version. One option can be to use a dummy value (like 0 or -1) to assign before saving the whole form into the database. Your "hack" is actually a good way to handle it as well.
Can you please elaborate how does the squence works?
Thanks!
A sequence object is not possible in SQL Server until you at least have SQL Server 2012 or higher. As this is a SQL 2008 forum, we were assuming that 2008 or 2008R2 is your SQL Server version. On the other hand, if you DO have a SQL Server 2012 (or higher, e.g. 2014, 2016, or 2017), then a sequence is available. You CREATE SEQUENCE (you can look up the syntax) and specify a starting number as well as an integer data type. You get new numbers from the sequence object by doing a FETCH NEXT FROM SequenceObjectName, and the number returned can then be used for an insert. Again, look up the information on CREATE SEQUENCE and just remember that it applies ONLY to SQL Server 2012 and above.
The Access "autoincrement" feature is equivalent to the Identity attribute for an int column within a SQL table.
While the sequence could no doubt be made to work, I think simply enabling an identity column is pretty much what he is used to seeing from the Access side.
Might be what he's used to seeing, but he may have a massive amount of VBA code that would have to change in a major way to accommodate not knowing the next row number in advance, which an IDENTITY column will NOT provide, whereas Access doesn't have that problem. That's one of the major pieces of grief in converting Access to SQL Server, as too many developers skilled at MS Access relied heavily on it's "bad behavior", and are now somewhat stuck, in that fixing the problem the right way is too costly in terms of development of the changes needed, so usually, some kind of hack or workaround becomes necessary. A Sequence Object can make the workaround a lot easier, but it's still a lot of fixing.

Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.

Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Matt Miller (4) - Wednesday, June 13, 2018 7:34 AM
Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.
Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.

From what I thought, the problem is that his form is a master-detail form that allows them to add new items in the master section. It's been many many years since I've done any forklifting of Access programs to a SQL Server back end so I don't recall how I handled those situations, but I agree that tons of VBA code is probably the wrong answer.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Chris Harshman - Wednesday, June 13, 2018 8:13 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:34 AM
Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.
Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.
From what I thought, the problem is that his form is a master-detail form that allows them to add new items in the master section. It's been many many years since I've done any forklifting of Access programs to a SQL Server back end so I don't recall how I handled those situations, but I agree that tons of VBA code is probably the wrong answer.

That's fair, and admittedly me Access-Fu is definitely a bit rusty. That said - the issue OP is describing sounds like he lost the reference to the PK when he relinked the local table to a linked table form SQL (in some cases you had to remind Access what the primary key was once you create the linked table, even IF SQL had it defined). That - and giving that particular column appropriate SQL identity settings.

Hopefully our friendly OP will chime back in shortly and see if any of these items help.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Matt Miller (4) - Wednesday, June 13, 2018 7:14 PM
Chris Harshman - Wednesday, June 13, 2018 8:13 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:34 AM
Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.
Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.
From what I thought, the problem is that his form is a master-detail form that allows them to add new items in the master section. It's been many many years since I've done any forklifting of Access programs to a SQL Server back end so I don't recall how I handled those situations, but I agree that tons of VBA code is probably the wrong answer.
That's fair, and admittedly me Access-Fu is definitely a bit rusty. That said - the issue OP is describing sounds like he lost the reference to the PK when he relinked the local table to a linked table form SQL (in some cases you had to remind Access what the primary key was once you create the linked table, even IF SQL had it defined). That - and giving that particular column appropriate SQL identity settings.
Hopefully our friendly OP will chime back in shortly and see if any of these items help.

Ultimately, most of the more skilled Access developers realized that continuing to use bound controls just made life infinitely more challenging, because the moment you actually needed any decent functionality or better control over what was happening on your form, the only good solution was to abandon bound controls. Yes, that meant a lot more VBA code, but the flexibility increase you got out of that effort was usually orders of magnitude in size, and directly related to the amount of code you were willing to write. Every few lines was usually well worth the productivity increase you could get from it. Bound controls just weren't worth the alleged convenience...

I can;t begin to tell you how useful it was to be able to escape the bound controls and get Access to really hum along and do all kinds of cool things. Bound controls just seemed to do a better job of getting in the way than anything else.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

sgmunson - Thursday, June 14, 2018 6:39 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:14 PM
Chris Harshman - Wednesday, June 13, 2018 8:13 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:34 AM
Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.
Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.
From what I thought, the problem is that his form is a master-detail form that allows them to add new items in the master section. It's been many many years since I've done any forklifting of Access programs to a SQL Server back end so I don't recall how I handled those situations, but I agree that tons of VBA code is probably the wrong answer.
That's fair, and admittedly me Access-Fu is definitely a bit rusty. That said - the issue OP is describing sounds like he lost the reference to the PK when he relinked the local table to a linked table form SQL (in some cases you had to remind Access what the primary key was once you create the linked table, even IF SQL had it defined). That - and giving that particular column appropriate SQL identity settings.
Hopefully our friendly OP will chime back in shortly and see if any of these items help.
Ultimately, most of the more skilled Access developers realized that continuing to use bound controls just made life infinitely more challenging, because the moment you actually needed any decent functionality or better control over what was happening on your form, the only good solution was to abandon bound controls. Yes, that meant a lot more VBA code, but the flexibility increase you got out of that effort was usually orders of magnitude in size, and directly related to the amount of code you were willing to write. Every few lines was usually well worth the productivity increase you could get from it. Bound controls just weren't worth the alleged convenience...
I can;t begin to tell you how useful it was to be able to escape the bound controls and get Access to really hum along and do all kinds of cool things. Bound controls just seemed to do a better job of getting in the way than anything else.

At some point we can swap war stories about the creative thing we've done with Access. I've done a lot of bound, unbound, late and early bound, and dynamically bound forms in Access, so I do understand the gamut. That said for basic CRUD forms, giving up data bound controls is a hefty cost. and the behavior the OP was describing is the default bound data behavior.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Matt Miller (4) - Thursday, June 14, 2018 8:37 AM
sgmunson - Thursday, June 14, 2018 6:39 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:14 PM
Chris Harshman - Wednesday, June 13, 2018 8:13 AM
Matt Miller (4) - Wednesday, June 13, 2018 7:34 AM
Perhaps I missed that part, but as a former Access developer, I never once needed to know anything about the new identity. No VBA code needed at all to manage ID's. This is actually one of those cases where it's better to just let the engines (both SQL and ACCESS) collaborate instead of forcing a code solution. You trigger a new record in the Access UI, and you WILL get the ID reserved in SQL waiting for the commit. It even shows up. in the UI. If it doesn't then you haven't bound the data correctly.
Even within Access there never was or will be a guarantee of an unbroken chain of ID's: optimistic locking killed that dream two decades ago. If that's what we're fighting, that battle is lost and has been for a long time.
From what I thought, the problem is that his form is a master-detail form that allows them to add new items in the master section. It's been many many years since I've done any forklifting of Access programs to a SQL Server back end so I don't recall how I handled those situations, but I agree that tons of VBA code is probably the wrong answer.
That's fair, and admittedly me Access-Fu is definitely a bit rusty. That said - the issue OP is describing sounds like he lost the reference to the PK when he relinked the local table to a linked table form SQL (in some cases you had to remind Access what the primary key was once you create the linked table, even IF SQL had it defined). That - and giving that particular column appropriate SQL identity settings.
Hopefully our friendly OP will chime back in shortly and see if any of these items help.
Ultimately, most of the more skilled Access developers realized that continuing to use bound controls just made life infinitely more challenging, because the moment you actually needed any decent functionality or better control over what was happening on your form, the only good solution was to abandon bound controls. Yes, that meant a lot more VBA code, but the flexibility increase you got out of that effort was usually orders of magnitude in size, and directly related to the amount of code you were willing to write. Every few lines was usually well worth the productivity increase you could get from it. Bound controls just weren't worth the alleged convenience...
I can;t begin to tell you how useful it was to be able to escape the bound controls and get Access to really hum along and do all kinds of cool things. Bound controls just seemed to do a better job of getting in the way than anything else.
At some point we can swap war stories about the creative thing we've done with Access. I've done a lot of bound, unbound, late and early bound, and dynamically bound forms in Access, so I do understand the gamut. That said for basic CRUD forms, giving up data bound controls is a hefty cost. and the behavior the OP was describing is the default bound data behavior.

Yeah, we should do that sometime... I got frustrated with bound controls right away - way too many restrictions on what you could do, and far too inconvenient for my taste - even for the basic CRUD. Yep, it was a fair chunk of effort to replace the bound control with an unbound one and a bunch of code, but sooooo worth it. I can't begin to tell you how much happier users are when the form works the way THEY like it to. The users tended to be a good 10 times more productive because they stopped getting frustrated by the limitations of the bound control.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)