How to migrate from the Access Version SQL
Printer Friendly Version
1. Start the Create Table Applcation
2. Click on 1. Create Migration Database, If The Site has a SQL Server Installed we will be using that Server. But if the site has no SQL server installed then we need to do one if the following
a. If the Site is a SBS site we need to install the SQL Server that came with SBS and Configure Same
b. If the Site is not a SBS Site and has no SQL Server then SQL Express Edition can be downloaded from the Microsoft Website and Installed.
c. During the Installation make sure the server is installed in Mixed Mode.
d. Make sure you know the SA Username and the SA Password
e. Make sure you know the SQL Servers Instance Name
3. Before starting this Process Make sure you have a complete Backup of the Server copy of Practpro
4. ALL users need to be out of eXpd8.
5. From the Computer that is going to run the Migration Tool eXpd8 shared data must be mapped to P: and Microsoft Access 2000 (or Above) is needed on that computer also
6. Click on Update Database.
7. Click on Yes to start the update
8. if the Following Screen is displayed then you have not made sure that the data is in p:\PractPro
9. Click on OK to exit Program, and make sure that the shared data is in p:\PractPro
10. The following will then be displayed
11. Click on OK to Continue. As the Screen explains this process could take some time depending on the size of the data so be patient. When the process has completed the First Step the Migration Tool will display the following
12. Click on OK to continue. As before be Patient as the Program is Still Running it is now doing the Documents, but after time has passed the following will be displayed.
13. Click on OK, the Program is Still Running it is now doing the Phone Messages, after time has passed the following will be displayed
14. Click on OK, The Process is now completed
15. Click on Exit to exit the Migration Tool
16. Now this is where we need access 2000 (or above) as the Migrate Database is an Access 2000 Database
17. Double Click on the Migrate.MDB file located in P:\PractPro\ to Open Access or start Access and Browse to p:\PractPro\Migrate.mdb
18. Click on Queries, we now need to Run Each of The Queries
19. What we are looking for is no entries on each of the Queries. See below for example
20. But if we have Entries, these must be resolved before any other steps can be completed
21. As above the Case_Link_Contact Without Matching Cases Query Shows 2 Rows Case ID 3 and 7, With Contacts 1 and 4. To Correct This Go Back to Tables. Open the Case_Link_Contact Table. Delete the Lines that are affected
22. Note that the Cases Without Matching Client Query will show information, it is important in this Query that all the Client_ID’s that are displayed should be 0, if not correct as required
23. if the Contacts Without Matching Contacttype Query
24. The problem above is with Contacts Table Contact_ID 5 and Contact_ID 116 have invalid Contact_Type_ID’s, to Resolve check the Item List for a valid Contact_Type_ID and enter them into the Contacts Table.
25. Documents Without Matching Cases Query
26. The List above relates to Documents that exist on the system but have no case associated with them, if we tried to import these the import process would fail so these entries need to be deleted from the Table SingleCase_NoteDoc
27. NoteDocCentralTB Without Matching Cases Query
28. The error lies in the NotedocCentralTB Table see the example below
29. Delete the rows as indicated by the Query
30. Tasks Without Matching Cases and Tasks Without Matching User queries, relate to Tasks, correct as required of
31. User_Link_Alarms Without Matching Alarms. Correct as required
32. once all queries are Blank we can Precede with the Next Step of the Migration
33. Most of these problems will have been resolved when you have supplied Central to US
34. We also supply a Utility Called CreateTablesSQL We need to Run This Program Next
35. Connect to the SQL Server using the SA user and SA Password as this information is required in the Next Steps. With out this information we can not continue. If you do not Know the SA Username and Password and you can connect to the database using Windows Authentication, try to change the SA Password
36. Click on Connect
37. Expand the Security Group
38. Expand the Login Group then Double Click on SA
39. Change Password, Confirm Password, Click OK
40. Go Back to Create Tables Program
41. Click on Enter SA User Name And Password Button and Enter required Information
42. Click OK, The System will now verify the Information, if all ok the Main Menu will be displayed.
43. Now Click on Create Blank Database
44. Do Not be tempted to click on any other buttons as this would stop the next stage from completing correctly
45. Now Click on 4. Create Blank Tables.
46. Now Click on 5. Create Users
47. Go back into SQL Server Management Studio Express, and verify the tables have been created. And the user has also been Created.
48. Next we need to Migrate the Converted and Verified Data, Click on 6. Migrate Access Data (Which will no be enabled) after creating Users
49. Lastly we need to go back into SQL Server Management and see if the eXpd8Users has been created.
50. Expand Security, expand Logins
51. Verify that eXpd8User exists. Double Click on eXpd8User. Click on User Mappings and Verify the Following
52. Users Mapped to this login: that eXpd8 is Ticked, and Under Database role Membership for: Expd8. That the above have Been Ticked. If this is not the case Tick as appropriate, click OK.
53. If an error is displayed about eXpd8user Already exists. We need to remove eXpd8user from The Security of the eXpd8 Database then do step 5. Create Users again. To remove eXpd8User from the eXpd8 Database follow these steps:
54. Close the Login Properties Window. Then expand databases , Expand Expd8, Expand Security,
55. Right Click on eXpd8User and Select Delete.
56. Click on OK then Do Step 5 Create Users Again
57. We now have migrated to SQL
58. Install SQL Version of eXpd8 onto each Computer.
