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' => '[email protected]'
)

# 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)

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')

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

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

  .save
  .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:

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

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.