If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New Database - Primary Key
Hello,
I've read a lot of the threads referencing Primary Key and I need a little help. I'm creating a database for my company to track changes to channels. This database will be accessed in different regions throughout the company (possibly sometimes at the same time). I need to figure out how to create a Primary Key for one of my tables. I've figure out how to use the DMax function to increment #s, but if two users try to enter a new record at the same exact time, one of them won't be able to save that record because the one before got the #. I know this because its happened before. Thanks. |
#2
|
|||
|
|||
New Database - Primary Key
On Fri, 5 Jun 2009 11:34:01 -0700, Trini Gal
wrote: I've read a lot of the threads referencing Primary Key and I need a little help. I'm creating a database for my company to track changes to channels. This database will be accessed in different regions throughout the company (possibly sometimes at the same time). I need to figure out how to create a Primary Key for one of my tables. I've figure out how to use the DMax function to increment #s, but if two users try to enter a new record at the same exact time, one of them won't be able to save that record because the one before got the #. I know this because its happened before. Hi Trini, Is there a reason that you aren't using an AutoNumber? Then Access will manage it for you. However, you need to be okay with having gaps in the sequence. If you need your own method, then as you discovered the DMax method does have this concurrency problem. Alternatively, you can keep a "Next Available" number in another configuration table, then write a small VBA function that opens the recordset for update (locking it), incrementing the number, then updating the record again. This ensures that only one user can increment at a time. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#3
|
|||
|
|||
New Database - Primary Key
In message , Trini
Gal writes Hello, I've read a lot of the threads referencing Primary Key and I need a little help. I'm creating a database for my company to track changes to channels. This database will be accessed in different regions throughout the company (possibly sometimes at the same time). I need to figure out how to create a Primary Key for one of my tables. I've figure out how to use the DMax function to increment #s, but if two users try to enter a new record at the same exact time, one of them won't be able to save that record because the one before got the #. I know this because its happened before. If your database is going to be accessed by lots of people simultaneously then it's worth considering whether Access is the right tool for the job. Depending on the usage pattern you may want to consider using a client-server database like SQL Server which can wrap multiple updates into a transaction. If all you want is a unique ID then an autonumber field will do the job. Don't your tables have natural keys? -- Bernard Peek |
#4
|
|||
|
|||
New Database - Primary Key
Bernard and Armen,
Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. I don't know anything about the SQL Server, I only know a little Access. I don't know what you mean by "natural key". This is what I have right now: Channel_ID (incrementing #, say 9 is the next #) Division (NC, SC) Region (Manhattan, Bronx, Brookly, Queens) So when the user selects the Division and Region, the File_Name eg SC_Bronx_9, which is a concatenation of all three fields, which I have as the Primary Key. Was that a good decision? Thats where I need your input. I read some threads where there are like 2 and 3 primary keys, but I didn't quite understand that. Thanks, "Bernard Peek" wrote: In message , Trini Gal writes Hello, I've read a lot of the threads referencing Primary Key and I need a little help. I'm creating a database for my company to track changes to channels. This database will be accessed in different regions throughout the company (possibly sometimes at the same time). I need to figure out how to create a Primary Key for one of my tables. I've figure out how to use the DMax function to increment #s, but if two users try to enter a new record at the same exact time, one of them won't be able to save that record because the one before got the #. I know this because its happened before. If your database is going to be accessed by lots of people simultaneously then it's worth considering whether Access is the right tool for the job. Depending on the usage pattern you may want to consider using a client-server database like SQL Server which can wrap multiple updates into a transaction. If all you want is a unique ID then an autonumber field will do the job. Don't your tables have natural keys? -- Bernard Peek |
#5
|
|||
|
|||
New Database - Primary Key
On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal wrote:
Bernard and Armen, Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. I don't know anything about the SQL Server, I only know a little Access. I don't know what you mean by "natural key". This is what I have right now: Channel_ID (incrementing #, say 9 is the next #) Division (NC, SC) Region (Manhattan, Bronx, Brookly, Queens) So when the user selects the Division and Region, the File_Name eg SC_Bronx_9, which is a concatenation of all three fields, which I have as the Primary Key. Was that a good decision? Thats where I need your input. I read some threads where there are like 2 and 3 primary keys, but I didn't quite understand that. Thanks, "Bernard Peek" wrote: In message , Trini Gal writes Hello, I've read a lot of the threads referencing Primary Key and I need a little help. I'm creating a database for my company to track changes to channels. This database will be accessed in different regions throughout the company (possibly sometimes at the same time). I need to figure out how to create a Primary Key for one of my tables. I've figure out how to use the DMax function to increment #s, but if two users try to enter a new record at the same exact time, one of them won't be able to save that record because the one before got the #. I know this because its happened before. If your database is going to be accessed by lots of people simultaneously then it's worth considering whether Access is the right tool for the job. Depending on the usage pattern you may want to consider using a client-server database like SQL Server which can wrap multiple updates into a transaction. If all you want is a unique ID then an autonumber field will do the job. Don't your tables have natural keys? -- Bernard Peek Is channel_id something real, as perhaps a television channel number? In that case the natural primary key is PRIMARY KEY (channel_id, division, region). If you go with an autonumber as a primary key, then make that UNIQUE (channel_id, division, region). Do not concatenate the three values together in the table. if you need then concatenated, then do it in a query. |
#6
|
|||
|
|||
New Database - Primary Key
Trini Gal wrote:
Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. FWIW I've been using autonumber keys for three decades both in Access and previous environments. Some of my systems are in use by 25 and 30 users all day long. And I can't recall any problems with using autonumbers. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#7
|
|||
|
|||
New Database - Primary Key
On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal
wrote: Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. Whether to use AutoNumber keys (aka Identity in SQL Server) is somewhat of a religious debate. They are "meaningless surrogate keys", as opposed to "natural keys". There are proponents on both sides. I am on the AutoNumber side, and like Tony and others I've been on that side for decades. My shop has built hundreds of databases using this approach. I don't know anything about the SQL Server, I only know a little Access. I don't know what you mean by "natural key". A natural key is a field (or group of fields) of real world data that uniquely identify one row in the table. No duplicates allowed. And the field(s) must always have a value - no nulls allowed. Also, it's best if the key value never changes. Proponents of AutoNumber (like me) say that it is difficult to find truly unique natural keys like that, and that combination keys are unwieldy to work with when relationships to child tables are involved. So we stick with a single AutoNumber key in every table - problem solved. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#8
|
|||
|
|||
New Database - Primary Key
Armen Stein wrote in
: On Fri, 5 Jun 2009 12:58:01 -0700, Trini Gal wrote: Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. Whether to use AutoNumber keys (aka Identity in SQL Server) is somewhat of a religious debate. They are "meaningless surrogate keys", as opposed to "natural keys". There are proponents on both sides. I am on the AutoNumber side, and like Tony and others I've been on that side for decades. My shop has built hundreds of databases using this approach. A programmically-incremented PK is no less a surrogate key than an Autonumber PK. It's just more work to make reliable. There is really no need to programmatically increment your PK unless you really do need the numbers to be incremented because you are exposing them to the users (e.g., an invoice number). Most properly-designed Access applications don't need that, so Autonumbers are fine. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#9
|
|||
|
|||
New Database - Primary Key
In message , Trini
Gal writes Bernard and Armen, Thank you for answering. Honestly, I have read so much about why I should and shouldn't be using the AutoNumber, that I just didn't use it and used the DMax function instead. I don't know anything about the SQL Server, I only know a little Access. I don't know what you mean by "natural key". A natural key is a unique identifier that is derived from the data itself rather than being added later. If there is a field or combination of fields that uniquely identifies a record then that is a natural key. If you don't have a natural key in each of your tables then you may have to do some more analysis. Access is a very nice tool for some jobs but it has limitations. It's not suitable for applications where lots of people need frequent access to the data. For those types of applications SQL Server is more appropriate. This is what I have right now: Channel_ID (incrementing #, say 9 is the next #) Division (NC, SC) Region (Manhattan, Bronx, Brookly, Queens) So when the user selects the Division and Region, the File_Name eg SC_Bronx_9, which is a concatenation of all three fields, which I have as the Primary Key. Was that a good decision? Thats where I need your input. I read some threads where there are like 2 and 3 primary keys, but I didn't quite understand that. There must be only one primary key in any table but it can be made up from more than one column. The primary key has to be unique but that doesn't mean that the individual components have to be unique. It just means that any possible combination of the values in the component fields must not occur more than once and none of the components can ever be null. In database theory the field or combination of fields that uniquely identify the record are referred to as the primary key. If you add an autonumber field you would call it a surrogate key. But Access refers to an autonumber field as a primary key, which I consider to be a serious flaw in its design. So in any table you need a primary key. You can add a surrogate key as well if you want, but you still need that real primary key as well. -- Bernard Peek |
#10
|
|||
|
|||
New Database - Primary Key
On 8 Jun 2009 00:35:46 GMT, "David W. Fenton"
wrote: A programmically-incremented PK is no less a surrogate key than an Autonumber PK. It's just more work to make reliable. That's true. But often one's desire to control the key means that one is attributing some meaning to it. There is really no need to programmatically increment your PK unless you really do need the numbers to be incremented because you are exposing them to the users (e.g., an invoice number). Most properly-designed Access applications don't need that, so Autonumbers are fine. I agree. And even in the rare instance when we expose the PK to the user, we call it something like a "reference number" and let them know that it's merely a unique number, and not to worry about gaps. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|