DBConvert for MS Access & SQLite — Best Practices and Tips

How to Sync MS Access and SQLite with DBConvert: Step-by-Step

This guide shows a clear, actionable workflow to sync Microsoft Access and SQLite using DBConvert. It assumes you have DBConvert installed (DBConvert for MS Access & SQLite) and both source and target databases accessible on your machine. Steps below use reasonable defaults so you can run a working sync without extra configuration.

What you need

• DBConvert for MS Access & SQLite installed.
• A Microsoft Access (.mdb or .accdb) file.
• A SQLite database file (.sqlite, .db) or path where a new one can be created.
• Basic Windows file access rights to both files.

Quick overview

1. Open DBConvert and create a new project.
2. Choose MS Access as source and SQLite as target.
3. Configure connections and mapping.
4. Select sync mode and schedule (if needed).
5. Run a test sync and then execute the full sync.
6. Verify results and enable automated sync if desired.

Step 1 — Launch DBConvert and start a new project

1. Open DBConvert for MS Access & SQLite.
2. Click “New” (or “Create new project”) to start a conversion/synchronization task.

Step 2 — Set source (MS Access)

1. For Source DBMS choose Microsoft Access.
2. Click “Source” settings:
   • Browse and select your .mdb or .accdb file.
   • If needed, provide a database password.
   • Set appropriate Access driver (usually the default works).
3. Click “Test Connection” and confirm success.

Step 3 — Set target (SQLite)

1. For Target DBMS choose SQLite.
2. Click “Target” settings:
   • Browse to an existing .sqlite/.db file or specify a new file path to create.
   • Choose SQLite engine/version if prompted (use default unless you have special needs).
3. Click “Test Connection” and confirm success.

Step 4 — Choose sync mode and direction

1. DBConvert supports different transfer modes:
   • Convert (one-time data transfer)
   • Sync (bi-directional or one-way synchronization)
2. For regular synchronization choose Synchronize.
3. Select direction:
   • Access → SQLite (one-way from Access to SQLite)
   • SQLite → Access (opposite)
   • Bidirectional (two-way)
4. Default recommendation: Access → SQLite for migrating/replicating Access data into SQLite.

Step 5 — Table selection and mapping

1. In the mapping window, select the tables you want to sync.
2. Review and adjust field mappings:
   • Ensure primary keys are mapped correctly.
   • Convert Access data types to compatible SQLite types (DBConvert usually maps automatically).
3. Exclude any tables/fields you don’t want to sync.
4. Optionally enable row filters or WHERE clauses to limit data transferred.

Step 6 — Dealing with schema differences

1. If creating a new SQLite DB, let DBConvert create tables automatically.
2. If mapping to an existing SQLite schema:
   • Use “Structure Only” or “Structure and Data” modes to align schemas.
   • Manually adjust column types or add missing indexes in SQLite if necessary.
3. Confirm how NULLs, AUTOINCREMENT, and default values are handled.

Step 7 — Conflict resolution and options (for bidirectional)

1. Set conflict rules:
   • Source wins or Target wins, or resolve by timestamp.
2. Configure how deletions are handled:
   • Propagate deletions, ignore deletions, or move deleted rows to a log table.
3. Enable logging to capture conflicts and errors for auditing.

Step 8 — Test run (recommended)

1. Run a small test:
   • Use a subset of tables or enable a row limit filter.
2. Inspect target SQLite DB with a SQLite browser (DB Browser for SQLite or similar).
3. Confirm data integrity, types, and row counts match expectations.

Step 9 — Run full sync

1. After successful testing, run the full synchronization.
2. Monitor progress in DBConvert’s log window.
3. If errors occur, check the detailed log, correct issues (permissions, schema mismatches), and rerun.

Step 10 — Schedule automated sync (optional)

1. If you need recurring syncs, use DBConvert’s scheduling:
   • Set frequency: hourly, daily, weekly, or custom.
   • Choose run options (e.g., only changed rows).
2. Ensure the machine hosting DBConvert remains powered and has access to DB files at scheduled times.

Step 11 — Verification and maintenance

1. After each sync, spot-check row counts and key tables.
2. Use checksums or sample queries to verify data consistency.
3. Periodically review logs and update mapping if schema changes occur in source or target.

Troubleshooting common issues

• Connection fails: check file paths, permissions, and that no other app locks the Access DB.
• Data type mismatches: adjust mappings or alter SQLite schema.
• Primary key conflicts: ensure unique keys exist or use conflict rules.
• Large data sets slow: run initial bulk convert, then use incremental syncs.

Example checklist before production sync

• Backup Access and SQLite files.
• Test connection to both DBs.
• Run a test sync on a small subset.
• Verify primary keys and indexes.
• Configure conflict rules and logging.
• Schedule automated sync if needed.