Table Field Types with Mark Edwards

Mike Wolfe -

Making the wrong choice when selecting field types can lead to data integrity issues, performance problems, and frustrating bugs that are difficult to track down.

In this Access Beginners User Group presentation, Senior Access Consultant Mark Edwards provides a detailed walkthrough of every field type available in Microsoft Access tables. Drawing from decades of experience developing complex Access and SQL Server applications, Mark explains the proper use cases for each field type while highlighting potential pitfalls and compatibility considerations when designing databases that may eventually be upsized to SQL Server.

Whether you're new to Access development or looking to deepen your understanding of database design best practices, this comprehensive overview of field types will help you make better decisions when creating your table structures.

Presenter Background

Mark Edwards brings extensive experience to the topic:

  • Started programming in BASIC on IBM AT machines
  • Developed early desktop satellite tracking software for the Navy
  • Began working with Microsoft Office, Excel 5, and Access 2.0 in the mid-1990s
  • Currently develops complex Access/SQL Server applications at IT Impact
  • Specializes in helping companies transition from complex Excel solutions to Access

Text Fields

Short Text

  • Maximum 255 alphanumeric characters
  • Default field size of 255 is generally fine for modern systems
  • Best choice for phone numbers, zip codes, and other non-calculated numeric data
  • Consider setting "Allow Zero Length" to No to prevent data filtering issues

Long Text (Memo)

  • Available in both plain text and rich text formats
  • Rich text allows formatting but includes hidden HTML tags
  • Coordinate control properties with table field properties to avoid HTML display issues
  • Use Shift+F2 to view complete contents in the zoom window

Numeric Fields

  • Long Integer - Safe choice for most whole numbers
  • Double - For large floating point numbers
  • Decimal - Best for financial calculations requiring precision
  • Avoid Byte and Integer types due to overflow risks

Key Considerations

  • Use text fields for numbers that won't be calculated (phone numbers, SSN, etc.)
  • Understand precision vs. scale for decimal fields
  • Consider SQL Server compatibility when choosing numeric types

Date/Time Fields

Standard Date/Time

  • Displays just date if no time component entered
  • Shows both date and time when time is included
  • Differs from SQL Server behavior

Date/Time Extended

  • Added for SQL Server compatibility
  • Requires enabling extended data type support
  • Makes database incompatible with older Access versions

Special Field Types

AutoNumber

  • Automatically assigns unique sequential values
  • Starts at 1 and increases by 1
  • Deleted numbers are not reused
  • Useful for tracking record creation order
  • Recommended even when using other primary keys

Yes/No Fields

  • Stored as -1 (Yes) or 0 (No)
  • Set default values to avoid null issues
  • Consider null handling requirements

Attachment and Object Fields

Attachments

  • Stores files directly in database
  • Increases database size significantly
  • Consider storing file paths instead
  • Requires application support on client machines

OLE Objects

  • Good for embedding images and documents
  • Requires supporting applications on client machines
  • Can cause compatibility issues

Best Practices and Recommendations

Field Naming

  • Avoid spaces in field names
  • Consider using prefixes (TXT, LNG, DTE)
  • Avoid reserved words
  • Make names descriptive but concise

Design Considerations

  • Understand data requirements before choosing types
  • Default to text fields when unsure
  • Consider SQL Server compatibility if planning to upsize
  • Set appropriate required/optional field properties

Limitations and Compatibility

SQL Server Considerations

  • Not all Access field types are SQL Server compatible
  • Avoid calculated fields if planning to upsize
  • Understanding type mapping between platforms
  • Different behavior with null values and formatting

Resources and References

Available Tools

  • F1 help on field properties
  • Design view for detailed field configuration
  • Field size property documentation
  • Data type support options

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

  • Interact directly with presenters during Q&A sessions
  • Network with other Access developers
  • Share your own experiences and challenges
  • Get immediate answers to your specific questions
  • Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

  • Base cover image generated by FLUX-schnell
  • Initial draft generated by Claude-3.5-Sonnet