View source for Querying Access database from Analytica 64

[[Category:Database Functions]]

== Migrating Access Database to SQL Server Express 2008  ==

Prior to Microsoft's release of the Office 2010 64-bit edition, it was not possible to query a Microsoft Access database from Analytica 64-Bit as Microsoft directly.  This was because Microsoft had not released 64-bit drivers for its JET database engine -- which includes the Access (*.mdb), Excel (*.xls) and Text file drivers. This situation has now been remedied -- all you need to do is install Office 2010 64-bit.  

This page describes one way to get around this issue when you don't have the 64-bit edition of Access installed.  The method 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:<br> 

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

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

=== Installation<br>  ===

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

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

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 [http://msdn.microsoft.com/en-us/library/ms174173.aspx this link] to learn more about SQL Server Management Studio on MSDN.

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

=== SSMA for Access<br> ===

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 [http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx here].

=== Installing SSMA for Access  ===

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

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

== 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 [http://www.microsoft.com/sql/solutions/migration/access/SSMA02.wvx this video] that explains all the steps of migrating an existing Access database to SQL Server. 

Note: Your '''Server name''' is your '''computer name''' followed by ''''\sqlexpress'''.' (If your machine name is '''lumina''', the Server name is '''lumina\sqlexpress'''). 

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

:[[Image:LinkTable.jpg|478x297px]] 

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

== Step 5. Testing the Migration ==

=== Creating a DSN  ===

#Type in '''odbc''' in the Start Search (Windows Vista) or Start menu&gt;Run dialog (Windows XP) to start the ODBC Data Source Administrator. 
#Click on the '''System DSN''' tab. 
#Click the '''Add..''' button and then choose the '''SQL Server''' Driver. 
#Type in the name for the DSN, description and the SQL server ('''Computer Name\sqlexpress''') and then click '''Next'''. 
#Choose the authentication method. 
#Choose the database that you migrated your database to as your default database. 
#Click Finish

=== Quering the SQL Server Database ===

#Open Analytica 64 and then connect to the DSN created above using [[DbQuery]].
#Query the DSN to confirm the migration of the Access database.

==See Also==
* [[media:ODBC-Library.ana|ODBC-Library.ana]]
* [[Analytica_Libraries_and_Templates#ODBC_Database_Functions|ODBC Database Functions]]
* [[Analytica_Libraries_and_Templates#DB_Conversion_Library|DB Conversion Library]]
* [[DbQuery/Step-by-Step querying Microsoft Access]]
* [[Excel to Analytica Mappings/Database Functions]]
* [[Database functions]]
* [[Excel to Analytica Mappings]]