Home : Products : Aspen : Import Data from Aspen into SQL 2000
Q10746 - HOWTO: Import Data from Aspen into SQL 2000

How to Import Data from Aspen into SQL 2000


Microsoft Sequel Server 2000 has a utility called DTS Import/Export Wizard.  DTS stands for Data Transformation wizard.  This wizard can be used to import data from your Aspen working.mdb into MS Sequel Server 2000.  Before you import data you will need to create a SQL database and run the scripts provided when you purchased Aspen Enterprise.  After the scripts run all Tables, triggers and constraints will be created.



There are numerous tables in Aspen that have auto number fields.  These same fields in MS SQL are referred to as Identity fields.  The tables with Identity fields are listed in the section below titled Groupings of Aspen tables for importing into SQL.  If you have data to import into any of the tables with identity fields you will need to remove or set the identity to No.  For example let’s look at the Samples table.  Click on the table and then right click to get more menu options.




Select Design.  You will be presented with a list of fields and their datatypes.



Notice that the LIMS number field is an integer and the column allows nulls is not checked.  If we look at the bottom portion of the screen you will notice that the setting Identity is set to Yes.  Click in this field and change the value to No.



Close the table and confirm saving of the structure.  Continue to do this for all tables you wish to import data into that have identity fields.


Now you are ready to start the DTS wizard.  Select the Database and then right click, selecting the All tasks.  This will then present another listing of menus, select Import Data.





The DTS wizard starts up.



Click Next.  You will need to select Microsoft Access from the drop down list for your Data Source.  Next enter the file path and name to working.mdb.  You can use the browse function by clicking on the ellipses button next to the field File Name.  Now type in your lims administrator Username and password.  Before you click Next you must click on the Advanced Button.

You need to enter the path to the workgroup file apsen.mdw in the property Jet OLEDB System Database.  You can simply click in the value column and type in the path and file name (there isn’t the option to browse to this file – you will need to type in the value).



When you are done click OK.  You will be brought back to the originating form.  You can now click on Next.  If you did not enter the correct path to the aspen.mdw file when you click on Next an error message will be displayed indicating that you do not have permissions.



The next form asks if you want to copy the tables or create a query.  For this article we will select Copy.



Click Next.  Now a list of tables found in working.mdb will be presented. 

Select the tables you want to import.  You must select a grouping of tables as outlined below.  If you don’t have any data to import into a table do not check it.  You will need to import data from the “First Group” and then start the DTS wizard again to import data from the Second group.  This is important to prevent errors due to referential integrity.


Groupings of Aspen tables for importing into SQL


First Group:

  1. **Analyst_Cap
  2. **Analyst_CAP_Stds
  3. **Analyst_TestGroups
  4. **Analyst_Training
  5. Arch_Export_Queries
  6. Arch_Worksheet_Groups
  7. Audit_Reports
  8. Audit_Reasons
  9. **Audit_Trail
  10. Audit_Trail_Setting
  11. BusinessRules
  12. **Complaints_Lab
  13. **Complaints_LabActions
  14. **Complaints_WQ
  15. **Complaints_WQActions
  16. **Complaints_WQSamples
  17. ComplaintValues
  18. ContainerLibrary
  19. Data_Qualifier
  20. Export_Queries
  21. ExportNames
  22. ExportNamesFields
  23. **Holidays
  24. inst_AnalystLib
  25. inst_SalesRepLib
  26. inst_ServiceRepLib
  27. inst_SuppliersLib
  28. inst_SupplierPartsLib
  29. inst_VendorLib
  30. inst_VendorSalesRepLib
  31. inst_VenServiceRepLib
  32. inst_Instrument
  33. **LAB#
  34. LabSections
  35. LocationLib
  36. LoginValues
  37. ManufacturerLib
  38. **MDL_Study
  39. MDL_Study_RelatedTestGroups
  40. MDL_Study_Tests
  41. OPSFacilities
  42. **PrepBatches
  43. Preservatives
  44. QC_Codes
  45. QC_Group_Ref
  46. **QC_Samples
  47. QC_Tests_Ref
  48. QC_Types
  49. Quote_Code
  50. Quote_QA_QC
  51. Quote_TAT
  52. Quoters
  53. **Quotes
  54. Report_Names
  55. SampleRefLib
  56. **SAMPLES
  57. **Sched_Samples
  58. SchedSet
  59. Spreadsheets
  60. SubcontractorLib
  61. SubmitterLib
  62. SubmitterLib_Contacts
  63. SubmitterLib_Reports
  64. tbl_XMLDupNode
  65. tbl_XMLKeyNode
  66. tbl_XMLRecFld
  67. tbl_XMLRoot
  68. tbl_XMLSpecLib
  69. TestGroupLib
  70. TestLib
  71. **Worksheet#
  72. Worksheets

 Second Group:

  1. ContainerHistory
  2. Containers
  3. **inst_Calibration
  4. inst_CalibrationTests
  5. **inst_Certifications
  6. inst_CertificationTests
  7. **inst_Components
  8. **inst_Devices
  9. **inst_Parts
  10. **inst_PM
  11. **inst_Service
  12. Locations
  13. Manufacturers
  14. **Prep_QC_Samples
  15. Prep_QC_TestGroups
  16. Prep_QC_Tests
  17. Routine_Reports_Copies
  18. QC_Pointers
  19. QC_TestGroups
  20. QC_Tests
  21. **Quote_Details
  22. **ReportSeries
  23. Routine_Reports_Arch
  24. **Routine_Reports_Control
  25. Sample_Reference_TestGroups
  26. Sample_Reference_Tests
  27. Sched_Locations
  28. sched_Manufacturers
  29. sched_Notes
  30. sched_Samples_TestGroups
  31. sched_Samples_Tests
  32. sched_SampleSchedule
  33. Sched_Submitters
  34. SchedSet_Samples
  35. SchedSet_Schedule
  36. SchedSet_TestGroups
  37. SchedSet_Tests
  38. SerializedReports
  39. SeriesReports
  40. TestGroup_TestLib
  41. TestGroupLib_TestInst
  42. TestGroupLibCalcs
  43. TestGroupLibCalcs_Tests
  45. TESTS
  46. **Worksheet_Groups


** Indicates tables with Identity fields



After you are finished importing data without any errors you must go back to the tables with identity fields and set the identity back to Yes and set the Seed value.  The set value is the last value for the identity field.  For example you can open the Samples table and find the last LIMS#. If this LIMS# is 16789 that is the value you would enter into the Identity Seed field.




Once you have set the Identity back to Yes and set the Seed Identity value, close the table and save your changes.  Repeat this for all tables that you previously changed the Identity to No.



Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?


Tell us why you rated the content this way. (optional)
Approved Comments...
No user comments available for this article.
Created on 2/12/2008 2:17 PM.
Last Modified on 2/12/2008 2:20 PM.
Last Modified by No Author Name Available!.
Skill Level: Intermediate.
Article has been viewed 7286 times.
Rated 5 out of 10 based on 5 votes.
Print Article
Email Article