Solved

MS Access to SQL Server Express Migration ASP SQL queries language

Posted on 2007-04-01
5
286 Views
Last Modified: 2010-03-19
Hi,

For a few years now I have been running an asp applicaiton with a MS Access Database.   Now is the time to move over to MS SQL Server 2005 Express.

Originally I used paramaterized queries in Access but a year ago i though it would aide my inevitable DB migration more painless by coding the SQL in asp instead, leaving just the table structures and data intact in Access.  I now realise I may have been better off leaving alone.

My problem is how do I easliy migrate the data and queries to the new dbms (sql server express).   The MS management studio seems to have translated the tables (although my Access database used uk date format #31/12/2006# and clearly this has changed although Im not sure how) but the sql which queires the MS access db needs to be 'translated' (including some complex and lenghthy sql) to work with the new dbms.

I would be really grateful for some advice on this experts.

rgds
R

0
Comment
Question by:davie455
  • 2
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 200 total points
ID: 18833451
I would approach the conversion in stages.  The first stage is to migrate your data, which can be done with the upgrade wizard in Access or with SSMS tools.  At that stage, you can simply change your Access backend to link to the SQL Server tables and the application should continue to work.  The Access queries and your front-end SQL (which will still be serviced by Access) should work pretty much transparently.

After you are comfortable with the new backend, you can begin to phase Access out of your middle tier. The first logical step in my mind, would be to begin developing views and sp's that are equivalent to the Access queries.  You can do that piece-meal.  Once you have the SS equivalent, then just modify the Access query to select from the View/Procedure instead of from tables.  At this stage Access is still in the mix, but it is essentially a passthru.

Next step is to begin modifying ASP code to connect to SS directly, bypassing Access.  Again, you can do that a little at a time, until Access is phased out.

You mentioned some issues with date translation.  As far as I know, Access dates and SQL Server dates are compatible, so I expect your dates converted just fine.  However, you may have some issues with formatting during the presentation of dates, possibly influenced by workstation settings. I expect with a little research, you will understand better what has happened to the dates and will be able to work around it.

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 18834083
Seriously put some consideration into rewriting all your SQL queries to take advantage of MS SQL Server.  Do NOT try and convert them or you will be disappointed and frustrated at the performance or lack of.

>>although my Access database used uk date format #31/12/2006# and clearly this has changed although Im not sure how<<
Both MS Access and MS SQL Server do not use any regional settings to store datetime columns.  MS Access stores datetime columns as doubles (the whole part is the date and the decimal is the time) and MS SQL Server stores them as two integer values (one for the date and the other for the time). There are some subtle differences, however.  For example the range of dates is not the same, but I don't expect you will have any problems converting.  As pointed out you will have to change the way you are querying datetime columns.  If you were using #dd/mm/yyyy# you should get in the habit now of using an unambiguous date format such as 'yyyy-mm-dd'
0
 

Author Comment

by:davie455
ID: 18849558
thanks for both comments so far,

i think my best option is to first get the data into SQL 2005 but continue to use access as the front end by linking to the data and leave the SQL transactions as they are (access compatible).  once I have this up and running then go about rewriting the queries.

Just two points of clarification if I may, acperkins you said:  >>As pointed out you will have to change the way you are querying datetime columns.  If you were using #dd/mm/yyyy# you should get in the habit now of using an unambiguous date format such as 'yyyy-mm-dd'

does this mean I dont need to worry about the format the data is stored in, I  just need to consider what format i query that data in?

Also, some of my more complex queries I have relied 100% on the access query builder to get running.  is there some kind of query builder for SQL 2005 syntax...if that makes sense?

Really appreciate the help.

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 18854366
>>does this mean I dont need to worry about the format the data is stored in, I  just need to consider what format i query that data in?<<
Correct.

>>is there some kind of query builder for SQL 2005 syntax...if that makes sense?<<
There are a number of Third party tools, SQL Prompt is one of them.  But none are free.
0
 

Author Comment

by:davie455
ID: 18869583
Thanks guys, apologies for delay in posting points.   I have followed your advice.  Most of my queries are pretty simple but there are one or two extremeley lenghthy and complicated statements.  I spent all of yesterday translating the biggest and guess what....used to execute in 3 seconds in access and now about 5 in SQL server...this is after a removed access from the equation.   So, I am now splitting the query up into something leaner (I always new it was a badly written one anyway and I hope the extra commands in T-SQL will give me more flexibility).

Thanks again.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Are triggers slow? 7 25
Union & Crosstab qrys 101! 6 59
SQL Select Query help 1 38
denied execute as 13 33
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question