How to convert MS Access databases to SQL Server Compact Edition

Background

Beginning with Meridian Enterprise/TeamWork 2010, the external databases (user accounts, Local Workspace, and lookup tables, if applicable) that are created and maintained by its 64-bit components use SQL Server Compact Edition instead of MS Access databases. This is necessary because a 64-bit OLEDB driver for MS Access is not provided with Windows. When upgrading Meridian Enterprise/TeamWork from 32-bit to 64-bit components, these databases must be converted manually if no 64-bit OLEDB driver is installed. A workaround is to install the Microsoft Access Database Engine 2010 Redistributable described below.

Description

Microsoft Office 2010 includes a 64-bit OLEDB driver named the 2010 Office System Driver that can be used to connect to Access, Excel, and text files. The provider name is Microsoft.ACE.OLEDB.14.0. If Office 2010 is not installed, the driver is available as a separate download from the Microsoft Download Center named Microsoft Access Database Engine 2010 Redistributable. For additional information, see Jet for Access, Excel and Txt on 64-bit systems at www.connectionstrings.com.

We recommend that new Meridian Enterprise or TeamWork 64-bit installations use SQL Server Compact Edition and convert any existing Access databases using the following instructions. Existing installations may either install the Microsoft Access Database Engine 2010 Redistributable and continue to maintain the databases in MS Access format or convert existing Access databases to SQL Server Compact Edition using the following instructions.

Note     The 64-bit Configurator application cannot create tables in MS Access format. Use the 32-bit Configurator instead.

To convert the MS Access databases to SQL Server Compact Edition, use BlueCieloECM.MdbToSdfConvertor.exe located in the Meridian Enterprise/TeamWork Program folder.

To convert a lookup tables database:

• Run the following in the \BC-Meridian Extensions\<VaultName> folder:

  <PathToProgramFolde>\BlueCieloECM.MdbToSdfConvertor.exe <VaultName>$LL.mdb -TTAB [-S[ql]]  

To convert a user accounts database:

• Run the following In the \BC-Meridian Vaults folder:

  <PathToProgramFolder>\BlueCieloECM.MdbToSdfConvertor.exe ICUserDB.mdb -TUDB [-S[ql]]

To convert a local workspace database:

• Run the following in the \BC-WorkSpace\<UserName>\<VaultContext>\__$$Environment folder:

  <PathToProgramFolder>\BlueCieloECM.MdbToSdfConvertor.exe __$$Vault.mdb -TLWS -PAMSupport [-S[ql]]

To convert any other database:

• Run the following in the folder containing the database:

  <PathToProgramFolder>\BlueCieloECM.MdbToSdfConvertor.exe <DBName>.mdb -TGEN [-P<DBPassword>] [-S[ql]]

Note     The optional [-S[ql]] parameter is for debugging or informational purposes only. It can be specified as -S or as -Sql and will display the output of the SQL statements that are executed.

After conversion, the corresponding registry keys must be set to use the new database.

On the server:

HKEY_LOCAL_MACHINE\Software\Cyco\AutoManager Meridian\CurrentVersion\Server\UserDatabase\ConnectionString 

For example, C:\BC-Meridian Vaults\ICUserDB.dbf

HKEY_LOCAL_MACHINE\Software\Cyco\AutoManager Meridian\CurrentVersion\Server\TablesDB = 1

On the clients:

HKEY_LOCAL_MACHINE\Software\Cyco\AutoManager Meridian\CurrentVersion\Server\WorkspaceDB = 1

For more information, see the BlueCielo Meridian Enterprise Administrator's Guide.