DBConvert for MS Access & SQLite: Troubleshooting Common Issues

DBConvert for MS Access & SQLite: Troubleshooting Common Issues

1. Connection failures

• Cause: Incorrect file paths, locked Access database (.mdb/.accdb), wrong SQLite file path, or missing drivers.
• Fixes:
  • Verify full file paths and file existence.
  • Ensure Access DB isn’t open in another application; close MS Access.
  • Install/repair required ODBC/OLE DB drivers (Microsoft Access Database Engine).
  • Check file permissions; run DBConvert with sufficient privileges.

2. Authentication errors

• Cause: Password-protected Access DB or SQLite database with encryption, or incorrect credentials for remote DB.
• Fixes:
  • Provide the correct Access database password in the connection settings.
  • For encrypted SQLite, ensure DBConvert supports the encryption format and supply the key/password.
  • Confirm credentials for any remote servers are correct and allowed.

3. Slow performance or long migration times

• Cause: Large dataset, network latency (if using remote storage), lack of indexes, or conversions that include unnecessary logging.
• Fixes:
  • Migrate in batches or use filters to move subsets first.
  • Run the tool on the same LAN or machine hosting the files to reduce network overhead.
  • Disable or defer rebuilding indexes during bulk transfer, then recreate indexes on target.
  • Use multithreaded/parallel options if available and appropriate.

4. Data type mismatches or truncation

• Cause: Differences in supported types between Access and SQLite (e.g., memo/longtext, autonumber, GUIDs).
• Fixes:
  • Map source types to appropriate SQLite types in DBConvert’s type-mapping settings.
  • Increase target column sizes or switch to TEXT for variable-length content.
  • Review and adjust field mappings before running full migration.

5. Loss of Unicode or special characters

• Cause: Encoding issues, incorrect collation, or driver limitations.
• Fixes:
  • Ensure UTF-8 encoding is selected for SQLite target.
  • Verify the Access DB stores Unicode and that the conversion tool preserves encoding.
  • Test with sample records and validate output.

6. Referential integrity/foreign keys not preserved

• Cause: Access relationships may not map directly to SQLite constraints.
• Fixes:
  • Enable options to create foreign keys if DBConvert provides them.
  • After migration, manually create foreign key constraints in SQLite if needed.
  • Verify primary key and index creation in target.

7. Auto-increment / Identity column issues

• Cause: Different implementations of autonumber vs. INTEGER PRIMARY KEY AUTOINCREMENT.
• Fixes:
  • Map Access autonumber to SQLite INTEGER PRIMARY KEY AUTOINCREMENT.
  • Check sequences and reset AUTOINCREMENT values if necessary.

8. Errors during schema creation

• Cause: Unsupported SQL constructs in SQLite (e.g., certain ALTER TABLE operations, complex default expressions).
• Fixes:
  • Simplify schema or pre-create target schema manually with supported constructs.
  • Use DBConvert’s schema preview to catch incompatible statements.

9. Partial transfers or interrupted sessions

• Cause: Network drop, app crash, or timeout.
• Fixes:
  • Use resume/retry features if available.
  • Re-run migration with “skip existing” or incremental mode to avoid duplicates.
  • Export logs to identify where it stopped.

10. Unexpected NULLs or default value differences

• Cause: Defaults in Access not transferred, or NULL handling differs.
• Fixes:
  • Explicitly set default values in target schema or in mapping rules.
  • Inspect source data for implicit defaults and handle during ETL.

General troubleshooting steps

1. Backup both source and target before changes.
2. Run a small test migration with representative tables first.
3. Use logging — enable verbose logs and review errors/warnings.
4. Validate results — row counts, checksums, spot-check rows, and schema compare.
5. Update DBConvert to the latest version for bug fixes.
6. Consult vendor docs/support with logs and sample files if problems persist.