MSAccess to SQLite install and setup custom content types?

Question

I'm looking for information on how I could use or import a MSAccess database into a SQLite install of Drupal 7. Is this possible or will I have to export the MSAccess tables to CSV or something and then add them to the SQLite db slowly?

Answer 1

** Full Disclosure I've Not Tried This Using the Requested Setup!! **

I've migrated Joomla -> Drupal and CSV to drupal ... so I have some experience with this. But having not tested your exact request I'm not 100% this will work.

You can install PDO and PDO support for ODBC drivers. I assume your PHP environment is Windows so you can also install MSAccess, which supports an ODBC driver connection. Here is a blog example of how to install/use MsAccess ODBC for a plain PHP webapplication.

You can then configure your settings.php file to include a custom database connection using PDO to use an ODBC driver to talk to your MSAccess database. This could look something like:

   $databases['remote_msaccess_db']['default'] = array(
     'driver' => 'odbc:DRIVER={Microsoft Access Driver (*.mdb)}',
     'DbQ' => 'C:\some\path\to\your\db.mdb',
     'uid' => '',
     'pwd' => '',
     'host' => 'localhost',
     'prefix' => '',
   );

That's actually the hard part :). Which may not work. Assure you have the same 32bit or 64bit ODBC drivers for PHP as whatever version OS MSAccess is running.

After that you can use a tool like Migrate to move the data from MSAccess into Drupal content types, Entities or even a plain old table based migration which Migrate supports (and I've used).