NumberCruncher Inventory Management Products | FAQ’s

Home ► Support

Converting NumberCruncher data from Access to MS SQL

All Orders 3.X

Before following this FAQ, please read FAQ 112 - How to Migrate NumberCruncher All Orders to MS SQL

The following FAQ will provide you with a utility and guidance on converting your current NumberCruncher from Access to MS SQL.

Before converting your data:

All users should be logged out of NumberCruncher.
If you do not have version 3.1.18, download the latest version of NumberCruncher.
Open the company file and do a full sync (File --> Synchronize with QuickBooks --> Full Sync).
Do a compact & repair (File --> Compact & Repair).
Do a backup (File --> Back Up).
Determine the name of the computer on which you installed MS SQL. You can do this by going to the MS SQL computer itself, right clicking on properties and selecting the Computer Name tab. The computer name is the first part of the Full Name. For example, if the Full Name is SERVER.mydomain.local, then the computer name is SERVER.

Step 1 - Convert the data

Download and run NCAccess2SQLInstall.exe. This is a self extracting file that will copy the conversion utility to c:\NCAccess2SQL. You should then see a screen that looks the screen below. If you don't see this screen, using Windows Explorer to folder c:\NCAccess2SQL and double click NCAccess2SQL.exe.

If not already pre-filled, select your NumberCruncher data file (eg. MyCompany.NCB) using the Browse button.
Under SQL Server Name enter the name from #6 above.
Under Server Database enter the desired name for your database. Note: If if you want to use more than one word, separate the words with underscore (Eg. My_Company_Inc or Always_Health_Inc). Do not use any numbers, commas, quotes or other special characters.
Under SQL User ID enter the SQL user that will be used to login to the server. This is usually sa.
Under SQL Server Password enter the password for the SQL User ID or blank if it nothing. The default password if you installed MSDE is sysadmin.
Click Transfer Data. If you get an error that says 'Server does not exist or access denied', go back to step FAQ 113 - Step 3 and enabled TCP/IP
First the database structure will be created. After that is complete, you will be asked if you want to Transfer the Data. Select Yes All.
If you get an error, note the error description (a screen capture is recommended) then press OK and continue with the rest of the data. Note: You may get an error on a table called tmpAmounts, you can ignore this error.
If you receive no error, other than tmpAmounts, you are ready for the next step or if you want to test the system go to Switching between Access and SQL. Otherwise report your errors to [email protected] immediately and read the section Switching between Access and SQL.

Note: the following is screen capture of the error that you can ignore.

Note You can repeat this for all your NumberCruncher data files.

Step 2 - Disable the Original NCB File

Rename, move or delete the original NCB file thus preventing any users from logging on to the Access file in error.

Step 3 - Configuring other users

During the data transfer, a registry entry was created that tells NumberCruncher where the database if locations. The registry can be viewed as follows: From Windows Start, select Run, type in regedit and press OK. The registry entries are under HKEY_LOCAL_MACHINE\SOFTWARE\NumberCruncher. This registry entry must be copied on all user machines. The easiest way to do this is to export the entry and re-import to the other users. As follows:

Export

Highlight HKEY_LOCAL_MACHINE\SOFTWARE\NumberCruncher
Do File --> Export
Name and save the file were you can open it from any machine on the network.

Import - to be done on all users machines

Open up the Registry Editor
Do File --> Import then select the file that was created in #3 above

Switching between Access and SQL

During the data transfer, a registry entry was created that tells NumberCruncher where the database if locations. The registry can be viewed as follows: From Windows Start, select Run, type in regedit and press OK. The registry entries are under HKEY_LOCAL_MACHINE\SOFTWARE\NumberCruncher. One of the keys is called DBProvider and you will notice that its value is SQL. To toggle between Access and SQL, simply change the value of this key by double clicking it. For SQL, enter SQL and for Access leave blank.

YOU ARE DONE - LAUNCH NUMBERCRUNCHER AS USUAL.