Relationships and Indexes with Crystal Long, Kent Gorrell, & Adrian Bell

Mike Wolfe -

A deep dive into database relationships and indexing across Access and SQL Server that reveals key differences developers need to understand when working with either platform.

In this comprehensive Access User Group presentation, Crystal Long and Kent Gorrell explore how relationships and indexes work differently in Microsoft Access versus SQL Server, with additional insights from Adrian Bell on binary operations. The presentation focuses on practical aspects of working with relationships and indexes, including hidden indexes in Access, migration considerations, and best practices for maintaining data integrity.

Whether you're working exclusively in Access, planning a SQL Server migration, or maintaining hybrid solutions, this detailed comparison of relationship and index handling across platforms offers valuable insights for database developers.

Access Relationships & Indexes

Relationship Fundamentals

  • Relationships in Access require indexes on both sides when enforcing referential integrity
  • Access automatically creates hidden indexes for foreign keys
  • Relationship properties stored in MSysRelationships table
  • grbit field contains relationship attributes as a bitmask

Crystal's Demo Tool

  • Custom form and module for exploring relationships
  • Lists all relationships including hidden ones
  • Shows parent/child table relationships
  • Displays relationship properties like cascade options
  • Code available for download and import into other databases

Index Handling

  • Access creates hidden indexes automatically for referential integrity
  • Some indexes not visible in table design view
  • Can have both visible and hidden indexes on same fields
  • Index required on both sides of relationships

SQL Server Differences

Key Differences from Access

  • No automatic index creation for foreign keys
  • Must manually create indexes on foreign keys
  • Foreign keys belong to database, not tables
  • Primary keys automatically get clustered indexes

Relationship Options

  • Has additional "cascade to default" option not in Access
  • More explicit control over cascade behaviors
  • Separate columns for cascade delete and update options
  • Different system views for exploring relationships

Migration Considerations

  • Need to handle Access's hidden indexes
  • Data type matching requirements
  • Date compatibility issues
  • Fixing orphaned records before migration
  • Importance of proper planning and testing

Best Practices

Database Design

  • Use appropriate data types for keys
  • Consider indexing strategy carefully
  • Plan for data integrity
  • Document relationships and indexes

Migration Strategy

  • Fix data issues in source database
  • Create proper schema before migrating data
  • Test thoroughly before final migration
  • Use transactions for data integrity

Understanding Bit Flags

Added based on Adrian Bell's technical explanation

Binary Operations Explained

  • Bit flags store multiple yes/no values in a single number
  • Powers of 2 represent individual flags (1, 2, 4, 8, 16, etc.)
  • AND operations check if specific bits are set
  • Hexadecimal notation makes binary values more readable

Practical Example

  • Used 4353 AND 4096 to demonstrate bit flag checking
  • Showed how to convert between decimal and hexadecimal
  • Demonstrated how SQL Server uses bit flags for relationship properties
  • Explained how VBA handles different number bases

Working with Bit Flags

  • Use &H prefix in VBA to specify hexadecimal values
  • Hex() function converts decimal to hexadecimal display
  • Different notations needed for VBA vs SQL
  • Boolean values handled differently in queries vs VBA

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