jackcess-rb

A JRuby interface to the Jackcess library for reading and writing Microsoft Access databases.

jackcess-rb provides a Ruby-friendly interface to the powerful Jackcess Java library, enabling programmatic interaction with Access database files (.mdb and .accdb) directly from JRuby. This gem is designed with Durable Programming principles: pragmatic solutions, sustainability, quality, and customer-centric development.

Features

• Pure JRuby: No native dependencies—runs on any platform with JRuby
• Read and Write Access Databases: Full support for both .mdb (Jet) and .accdb (ACE) formats
• Idiomatic Ruby API: Natural Ruby interface wrapping Jackcess's Java API
• Table Operations: Create, read, update, and delete tables and records
• Schema Inspection: Examine database structure, columns, indexes, and relationships
• Type Safety: Automatic conversion between Java and Ruby types

Quick Start

require 'jackcess'

# Open an existing database
db = Jackcess::Database.open('path/to/database.accdb')

# Read from a table
table = db.table('Customers')
table.each do |row|
  puts "#{row['FirstName']} #{row['LastName']}"
end

# Add a new row
table.add_row(
  'FirstName' => 'Alice',
  'LastName' => 'Smith',
  'Email' => 'alice@example.com'
)

# Close the database
db.close

Installation

Requirements

• JRuby 9.3+: This gem requires JRuby and will not run on MRI Ruby
• Java 8+: Jackcess requires Java 8 or higher

Verify your JRuby installation:

jruby --version
# Expected: jruby 9.3.x (or higher)

Install via Bundler (Recommended)

Add to your Gemfile:

gem 'jackcess-rb'

Then run:

bundle install

Install via gem

gem install jackcess-rb

Usage

Opening and Closing Databases

require 'jackcess'

# Open an existing database
db = Jackcess::Database.open('data/northwind.accdb')

# Work with the database
# ...

# Always close when done
db.close

# Or use block form for automatic cleanup
Jackcess::Database.open('data/northwind.accdb') do |db|
  # Database automatically closed when block exits
end

Reading Data

# Access a table
table = db.table('Products')

# Iterate through all rows
table.each do |row|
  puts "Product: #{row['ProductName']}, Price: #{row['UnitPrice']}"
end

# Get specific row by ID
row = table.find_by_id(42)

# Access column values
name = row['ProductName']
price = row['UnitPrice']

Writing Data

table = db.table('Products')

# Add a new row
table.add_row(
  'ProductName' => 'Widget',
  'UnitPrice' => 19.99,
  'UnitsInStock' => 100
)

# Update an existing row
row = table.find_by_id(42)
row['UnitPrice'] = 24.99
row.save

# Delete a row
row.delete

Creating Tables

# Create a new table with columns
db.create_table('Orders') do |t|
  t.column 'OrderID', :long, auto_number: true
  t.column 'CustomerID', :long
  t.column 'OrderDate', :date_time
  t.column 'ShipAddress', :text, length: 255
  t.column 'Total', :double

  t.primary_key 'OrderID'
end

Schema Inspection

# List all tables
db.table_names.each do |name|
  puts "Table: #{name}"
end

# Inspect table structure
table = db.table('Customers')

puts "Columns:"
table.columns.each do |col|
  puts "  #{col.name} (#{col.type})"
end

puts "Indexes:"
table.indexes.each do |idx|
  puts "  #{idx.name}: #{idx.columns.join(', ')}"
end

Transaction Management

# Explicit transaction control
db.transaction do
  table = db.table('Accounts')

  from_account = table.find_by_id(1)
  to_account = table.find_by_id(2)

  from_account['Balance'] -= 100
  to_account['Balance'] += 100

  from_account.save
  to_account.save

  # Automatically committed if no exception
  # Automatically rolled back on exception
end

API Documentation

Jackcess::Database

Main entry point for database operations.

Class Methods

• Database.open(path, options = {}) → Database

  • Opens an existing database file
  • Options:
    • :readonly (Boolean): Open in read-only mode (default: false)
    • :auto_sync (Boolean): Auto-sync changes to disk (default: true)
  • Returns: Database instance
  • Block form: Automatically closes database when block exits

• Database.create(path, format = :v2000) → Database

  • Creates a new database file
  • Formats: :v2000 (.mdb), :v2003 (.mdb), :v2007 (.accdb), :v2010 (.accdb)

Instance Methods

• #table(name) → Table

  • Access a table by name

• #table_names → Array<String>

  • Returns array of all table names

• #create_table(name, &block) → Table

  • Create a new table with schema defined in block

• #close

  • Close the database connection

Jackcess::Table

Represents a table in the database.

Instance Methods

• #each(&block) → Enumerator

  • Iterate through all rows

• #find_by_id(id) → Row

  • Find row by primary key value

• #add_row(data) → Row

  • Add a new row with given data (Hash)

• #columns → Array<Column>

  • Returns array of column definitions

• #indexes → Array<Index>

  • Returns array of indexes

Jackcess::Row

Represents a single row/record in a table.

Instance Methods

• #[](column_name) → Object

  • Get column value

• #[]=(column_name, value)

  • Set column value

• #save

  • Save changes to the database

• #delete

  • Delete this row from the table

See API Documentation for complete reference.

Type Mapping

jackcess-rb automatically converts between Access/Java types and Ruby types:

Access Type	Ruby Type	Notes
TEXT	String	Variable length text
MEMO	String	Long text fields
BYTE	Integer	8-bit integer
INT	Integer	16-bit integer
LONG	Integer	32-bit integer
FLOAT	Float	Single precision
DOUBLE	Float	Double precision
CURRENCY	BigDecimal	Fixed-point decimal
DATE_TIME	Time	Date and time
BOOLEAN	Boolean	true/false
BINARY	String (binary)	Binary data
GUID	String	UUID/GUID strings

Design Principles

jackcess-rb is built following Durable Programming principles:

Pragmatic Problem-Solving

• Solves real-world need: accessing Access databases without Windows
• Focuses on common use cases first
• Provides escape hatches to underlying Jackcess API when needed
• No unnecessary abstractions—just enough Ruby idioms for comfort

Sustainability and Longevity

• Built on mature, well-maintained Jackcess library
• Minimal dependencies (JRuby + Jackcess only)
• Clear separation between Ruby API and Java interop
• Designed for long-term maintenance

Quality and Reliability

• Comprehensive test coverage
• Type-safe conversions between Ruby and Java
• Proper resource management (automatic cleanup)
• Error handling with meaningful messages

Customer-Centric

• Developer experience prioritized
• Idiomatic Ruby API
• Clear documentation with practical examples
• Progressive disclosure: simple things simple, complex things possible

Transparency

• Open source (MIT license)
• Clear documentation of capabilities and limitations
• Honest about JRuby requirement
• Links to underlying Jackcess documentation

Architecture

jackcess-rb provides a thin Ruby wrapper around the Jackcess Java library:

┌─────────────────────────────────────┐
│   Ruby Application Code             │
├─────────────────────────────────────┤
│   jackcess-rb (Ruby API)            │
│   - Idiomatic Ruby interface        │
│   - Type conversions                │
│   - Resource management             │
├─────────────────────────────────────┤
│   JRuby Bridge                      │
├─────────────────────────────────────┤
│   Jackcess Java Library             │
│   - Access file format handling     │
│   - Database operations             │
├─────────────────────────────────────┤
│   Access Database Files             │
│   (.mdb, .accdb)                    │
└─────────────────────────────────────┘

The gem focuses on:

• Ruby-friendly method names and conventions
• Automatic type conversions
• Block-based resource management
• Iterator patterns for large datasets

Performance Considerations

• Memory: Large tables are iterated lazily—only current row in memory
• Transactions: Batch operations in transactions for better performance
• Indexes: Use indexed columns for lookups when possible
• File Format: .accdb (v2007+) generally faster than .mdb for large databases

Limitations

• JRuby Only: This gem requires JRuby and will not work with MRI Ruby
• File-Based: Jackcess works with Access files directly, not via ODBC/ADO
• Access Features: Some advanced Access features (forms, reports, VBA) are not accessible
• Concurrent Access: Limited support for multiple processes accessing same file simultaneously

Contributing

We welcome contributions! See CONTRIBUTING.md for:

• Development setup
• Running tests
• Code style guidelines
• Pull request process

Testing

# Run the test suite
bundle exec rake test

# Run specific test file
bundle exec ruby test/database_test.rb

Troubleshooting

"Java::JavaLang::UnsupportedClassVersionError"

Your Java version is too old. Jackcess requires Java 8+.

java -version
# Should show 1.8.0 or higher

"Cannot find Java library 'jackcess'"

The Jackcess JAR is missing. Reinstall the gem:

gem uninstall jackcess-rb
gem install jackcess-rb

"Database format not supported"

The Access file format may be too old or corrupted. Try:

• Opening in Access and saving as newer format (.accdb)
• Using Access's "Compact and Repair Database" feature

License

MIT License

Support

• Documentation: https://rubydoc.info/gems/jackcess-rb
• Issues: GitHub Issues
• Jackcess Documentation: https://jackcess.healthmarketscience.com/
• Email: commercial@durableprogramming.com

Credits

jackcess-rb is developed by Durable Programming LLC.

Built on the Jackcess library by Health Market Science.

Changelog

See CHANGELOG.md for version history and release notes.