To migrate data from Version 5 and below to Version 6, a conversion of the access database must be performed.
NOTE : If there is corrupted data in your database, or duplicate data this import process will not resolve the issue - Transmille offer a data migration service to assist in this case
- Configure the New ProCal SQL Database as detailed here : https://support.transmille.com/support/solutions/articles/9000131423-configuring-a-new-server-installation
- Download and Install SQL Server Management Studio from the Microsoft Website if you have not done so already - https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
- After installation, open Microsoft SQL Server Management Studio and connect to the new SQL Database that was created in Step 1
- Expand the SQL Instance by clicking on the + symbol next to the server name, then expand the database by clicking +, finally, right click on the ProCal database and navigate to Tasks, followed by Import Data...
The SQL Server Import and Export Wizard will appear, click Next
On the 'Chose a Data Source' screen, scroll the drop down box to 'Microsoft Access (Microsoft Access Database Engine)
Click the 'Browse' button and navigate to the ProResDB.mdb file on either your computer (Single user systems, located in C:\ProCal\Certificates) or your existing server (Multi-user systems). No username or password is required. Click Next
On the Choose a Destination screen, scroll the drop-down box to 'SQL Server Native Client 11.0 (depending on your version of SQL Server Management Studio the version may differ)
If the Server Name is not automaticaly selected, select the ProCal server name from the Server Name dropdown box.
In the authentication panel, select 'User SQL Server Authentication' and enter the username and password for your ProCal database
Make sure that the ProCal database is selected in the Database drop down menu and click Next
On the 'Specify Table Copy or Query' make sure that 'Copy data from one or more tables or views' is checked and click next. Depending on the speed of your network connection the screen may pause after clicking next
Ensure that the following 'Sources' are ticked, leaving all others unticked :
- ' Additional Data'
Depending upon the age of your database or on your usage it is quite normal to have extra tables or queries listed
Make sure that the destination mapping is as follows :
- 'Additional Data' -> [dbo].[Additional Data
- 'Calibrations' -> [dbo].[Calibrations]
- 'Instruments' -> [dbo].[Instruments]
- 'Results' -> [dbo].[Results]
Select 'Calibrations', then press 'Edit Mappings'
Change the following mappings so that the source and destination columns are as follows:
- Cal_Date -> Cal_Date_Legacy
- Rec_Date-> Rec_Date_Legacy
- Date_Dispatched -> Date_Dispatched Legacy
- 'Results' -> [dbo].[Results]
On the next screen, you can configure the On Error (e.g incorrect data) or On Truncation (field too long in original database) global settings. Only change these to 'Ignore' if you are aware that some data may be lost, and then click Next
- On the 'Save and Run Package' screen, ensure that 'Run Immediately' is clicked and then click 'Next'
A final window will display, confirming the settings and explaining the process that is about to occur
NOTE - Only perform this task on an empty database, performing this process on a database with data already in will cause duplication and numerous other issues
- Click 'Finish' - The task will then run. If no errors occur a message will display the number of lines that were imported for each table. If any errors occur please contact Transmille for further assistance under an active support contract
- Once completed, ensure that the 'Date_Conversion' utility is run. This will convert existing dates to the correct format for the new software. This utility should be found in C:\ProCal\Programs, and is also hosted as a file on this article.