Primary key incrementation
-
Hello,
I know from Microsoft Access when you enter any value in any column it automatically adds a new row and increments a primary key. With Microsoft SQL however it does not do that. It waits until all values have being met and goes to the new line before adding a new primary key. Is there way to change this?
Thanks in advance.
-
If you're using a column with the IDENTITY property, then yes it gets a value when the row is inserted. If you need to know the value before you do the INSERT, then you may need something like a SEQUENCE:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017 - xboxown - Wednesday, June 6, 2018 1:02 PM
Please, don't use Management Studio option to Edit Rows as if it were Excel. That's a recipe for disaster. Write proper INSERT statements to handle tables.
- Luis Cazares - Wednesday, June 6, 2018 1:53 PM
THIS! THIS! THIS!
Trying to use SSMS to edit data is fraught with danger. T-SQL is your buddy, your pal, and your bestest friend. Learn it, live it, love it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Wow, I didn't even realize that's what the OP was saying they were doing. I often forget that ability is in SSMS because I wish it wasn't there. 😉
- Chris Harshman - Thursday, June 7, 2018 6:45 AM
I had to check if it still existed. It's attractive for new users that don't know better and are used to other options as Access or Excel.
-
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.
-
Think about the business process for this. How on earth can you expect to edit detail records for a master row that doesn't even exist yet? Just converting your Access database to SQL Server isn't the only thing that might have needed doing. Access handles a lot of things that SQL Server does rather differently, so if the code behind your forms is designed around dealing with MS Access's capabilities when the data is local to the Access database, then you need to change the code behind the forms to work with the new back end more appropriately. There are some basic database principles that Access doesn't quite embrace. Also, your migration to SQL Server may have preserved the table relationships by using foreign keys, but now that you have them, you may find that your VBA code design isn't really very good as a result. Things that are drop downs should not require access to the base table in order to find values from the related table. That's likely where your grief is. You should always make drop-downs solely dependent on the table from which they get their data, and NOT necessarily be BOUND CONTROLS. You may well need an Access expert to help you solve this problem. If you can operate outside of business hours for Eastern Time in the USA (GMT - 5, usually, but may be affected by daylight savings time), I may be able to assist on a contract basis. You can private message me here if needed. This could get complicated, depending on the number of controls that are bound, as there may not be all that much existing code, where now there's a strong need for a fair amount of it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - xboxown - Thursday, June 7, 2018 11:21 AMOK. 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.
- Luis Cazares - Thursday, June 7, 2018 12:16 PMxboxown - Thursday, June 7, 2018 11:21 AMOK. 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!
-
Here are some pages where you can read about Sequences:
https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/
http://www.tech-recipes.com/rx/60072/beginners-guide-sequence-objects-sql-server/ - xboxown - Thursday, June 7, 2018 12:24 PMLuis Cazares - Thursday, June 7, 2018 12:16 PMxboxown - Thursday, June 7, 2018 11:21 AMOK. 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Friday, June 8, 2018 6:35 AMxboxown - Thursday, June 7, 2018 12:24 PMLuis Cazares - Thursday, June 7, 2018 12:16 PMxboxown - Thursday, June 7, 2018 11:21 AMOK. 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.
----------------------------------------------------------------------------------
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? - Matt Miller (4) - Friday, June 8, 2018 5:08 PMsgmunson - Friday, June 8, 2018 6:35 AMxboxown - Thursday, June 7, 2018 12:24 PMLuis Cazares - Thursday, June 7, 2018 12:16 PMxboxown - Thursday, June 7, 2018 11:21 AMOK. 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I wonder if the OP was able to use a version still under mainstream support and used the sequence to solve his problem. Or maybe the previous option was kept (save header first and make the details available after).
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply