I'm majorly confused about Primary Keys and Access' 'Autonumber' data type.
I've always used 'Autonumber' as seemingly the best and easiest way for unique ID's to be created for PK fields in Access tables. I was aware of some issue with the common SELECT MAX(ID) SQL operation you end up doing to get the ID of something you just inserted. I found this at http://www.defusion.com/articles/index.cfm?ArticleID=72 The guy seems to be responding to someone posting the Fusebox technique of using SELECT MAX(ID) and <cftransaction> as a universal method of creating ID's for PKs, so that apps are portable between RDMS's. He seems to think the problem he mentions only happens on SQL Server 6.5/7, though. There was a response to this at http://www.defusion.com/articles/index.cfm?ArticleID=77 Here it was advised to use stored procedures, "isolation level" on <cftransaction>, or an insert trigger. Then I grab the Max_ID.cfm custom tag by Steve Nelson from fusebox.org, which has a nice suggestion in the documentation, to use an endless loop and <cftry> around the <cftransaction>, to keep trying to get the new ID *and* insert the new record until it gets it right. Questions: - Am I right in thinking SQL Server doesn't have the 'Autonumber' data type? Why not? Is there a grave disadvantage to it unless you're doing kid's stuff in Access? - Is it cool to use the SELECT MAX(ID) with SQL Server 2000 (i.e. do the 6.5/7 problems above not happen now)? - If I put <cftransaction isolation="serializable"> around the SELECT MAX(ID) and INSERT queries (in whatever order), are there any hidden dangers? Does this prevent anything else from changing the table concerned until the new ID is calculated and the nice new record is INSERTed? - You can't use stored procedures or triggers on Access, right? I'm basically trying to get this process *right*, and trying to construct app templates that will be easily portable between different DB systems - so even though I'm still mostly working with Access, I might drop the whole 'Autonumber' thing. Any advice/tips greatly appreciated on this! - Gyrus ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
