Querying Access database from Analytica 64

Migrating Access Database to SQL Server Express 2008

Microsoft Access databases cannot be queried from Analytica 64-Bit as Microsoft has decided to deprecate its JET database engine -- which includes the Access (*.mdb), Excel (*.xls) and Text file drivers, and have refused to provide 64-bit drivers for these (see http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine#Future).

One way to get around this issue is to migrate Microsoft Access databases to Microsoft SQL Server Express. Migrated SQL Server databases can then be queried from Analytica 64 and, by selecting the right options while migrating, can still be accessed from Microsoft Access even after the migration.

This page outlines the steps to migrate an existing Access database to SQL Server.

Step 1. Installing the SQL Server Express 2008

Microsoft SQL Server Express

Microsoft SQL Server Express 2008 is a free version of Microsoft's SQL Server. SQL Server Express does have a few limitations:

• Maximum database size of 4GB.
  
• Memory utilization limited to 1GB.
  
• CPU utilization limited to 1 CPU.
  

Please go here fore more information about SQL Server Express: http://en.wikipedia.org/wiki/SQL_Server_Express.

Installation

The web-installer for SQL Server Express can be downloaded heree: http://www.microsoft.com/web/downloads/platform.aspx

Here is a link to a video that goes through the installation of SQL Server 2008 Express: http://www.msdev.com/Directory/Description.aspx?eventId=1209

Pointer: Please check Management Studio Express while installing the SQL Server Express 2008. Management Studio Express lets you easily access, configure and manage SQL Server Express and SQL Server Databases. Please visit the following link to learn more about SQL Server Management Studio on MSDN: http://msdn.microsoft.com/en-us/library/ms174173.aspx.

Step 2. Installing the SSMA(SQL Server Migration Assistant) for Microsoft Access Databases

SSMA for Access

SSMA or SQL Server Migration Assistant is a tool that helps to migrate Microsoft Access database to SQL Server. More information on the SSMA tool can be found here: http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx.

Installing SSMA for Access

Here is a link to the SSMA for Access installer: http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en

Please look at the following video that goes over the activation and installation of SSMA for Access: http://www.microsoft.com/sql/solutions/migration/access/SSMA01.wvx

Step 3. Backing up your Access database

The migration tool makes changes to you database, so please backup your database before using the migration tool.

Step 4. Migrating Access database to SQL Server Express 2008

Please look at the following video that explains all the steps of migrating an existing Access database to SQL Server: http://www.microsoft.com/sql/solutions/migration/access/SSMA02.wvx

Important: Remember to check Link Table to have the option of accessing your database from Microsoft Access even after the migration. See the following picture.

More information on the SSMA tool can be found here: http://www.microsoft.com/sqlserver/2005/en/us/migration-access-learning.aspx