November 27, 2007 at 5:27 am
Hello,
In the early part of next year, I will be responsible for migrating my companies CRM Access database which has an in-house client front end to SQL server 2005. Now I am expecting this to be quite tough as the Access DB has grown over the years along with the business so its safe to say that it may be somewhat disorganised. I fully expect to have to develop quite a number of different database designs before I find one that is optimal and performs well. I wonder how much tweaking of the application will be needed for it to work well against the SQL database, but I am getting ahead of myself here and I would have to spend time with the app developer.
I really want to know if anybody here has ever done anything similar, where did you start and what methodology did you use? This is going to be a first for me and I could do with as much advice as I think I could possibly handle. Yes, I could just import the lot and it would work, but I want to make a big difference, especially as this is a massive learning opportunity. The database is not heavily written to.
Regards,
D.
December 6, 2007 at 12:10 pm
I would first try to find out what it does and why. Is the reasoning still valid or could be it done better or in another way. Document it and discuss with the users.
We're are also in the process of "upgrading" an application. First thing is reducing the complexity,maintainability of the whole process whilst keeping some fleixibility cause things had to be entered at 3 different places due time constraints in the analysis phase.
Functionbased queries in Access had to be rewritten.
Next was to avoid tight coupling of the user interface with the actual datalayout (n-tier instead of client-server). (formatting is no longer done at database level).
December 7, 2007 at 3:18 am
Hi
There are 3 Major areas that you need to be looking at in conjection with your Application Developer. I am assuming that an Access Front End will be used following the upgrade.
1. Tables - Easy Peasy but watch out for Dates. Best way is to use DTS / SSIS to pull the data from the Tables into the SQL Database.
2. Queries - Take time to analyse these and move them into the SQL Database as views. This is one of the longer tasks as there are quite a few Access Functions that are not allowed in SQL but it is well worth the effort.
3. VBA Code - Where the Application makes use of any direct VBA Code to read / write from tables, consider changing these to stored procedures.
The main reason for changing queries is to make SQL do teh work rather than Access.
Hope it goes well
Paul Smith
December 10, 2007 at 3:26 am
Hello,
Firstly, thank you for taking the time to answer. I didnt think anyone was going to answer this thread after a dry spell, so I also posted it under the development forum. I'd be interested to see what you think of the advice I received on there.
Regards,
D.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply