dmcafee at pacbell.net
dmcafee at pacbell.net
Mon Aug 29 13:47:39 CDT 2005
I believe when upsizing, text fields are converted to Char(). The only weird conversion is when dealing with Memo Fields, they become "text" in SQL :) so don't get the two "text" types confused between SQL & Access. I prefer SQL Servers import wizard over Access' Upsize wizard. you can save the import script and modify the fields if one is converted incorrectly. D -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Sent: Monday, August 29, 2005 10:59 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Field Sizes Oh, OK, I'm a bit weary this morning, have a blistering headache and even after 4 advil and a pot of coffee didn't catch the kidding part, sorry, now that I look back it was obvious!. Good point about the padding with spaces though, so I guess it was worth it ;o) I originally wanted to discuss if upsizing from an access db to a SQL Server db was any more of a hassle based on needed field lengths or standard 255 field lengths. We know Access doesn't use fixed length storage for strings and you don't save any space by defining text fields as less than the maximum allowable. But IIRC SQL Server does so when upsizing you now have all 255 field lengths (which isn't terrible but not optimal) or do you go in and resize all the fields lengths? NOTE: I'm not taking sides here. I generally size fields similar to what Jim mentioned but I also have taken the easier route and done as Stuart mentioned - resetting the default field length size and (knowing how Access deals with text fields) not really caring if it was set smaller. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of dmcafee at pacbell.net Sent: Monday, August 29, 2005 12:39 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Field Sizes I "was" kidding when I said to make them all varcahr(8000). I tend to use 50. I prefer Varchars to chars, unless when dealing with a specific charcter length datatypes. Char's tend to pad the vales with spaces. So if you have a field titled fname, a varchar(50) would store the name Joe as "Joe" a char(50) would store the name Joe as "Joe " I tend to sometimes forget to RTRIM() my chars when selecting :( D -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Sent: Monday, August 29, 2005 10:01 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Field Sizes On that note, is it good advice to make all text fields VarCHar in SQL Server? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of dmcafee at pacbell.net In SQL you can make everything a VARCHAR(8000) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Q: Does it matter when upsizing to SQL Server? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan 255 characters. Access doesn't use fixed length storage for strings and you don't save any space by defining text fields as less than the maximum allowable. I change the default field size for text strings to 255 in Options and never change it for individual filed definitions. All that restricting field sizes does for you is increase the risk of truncating data or throwing up error messages at the user sometime in the future. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com