Solved

MS Access to SQL Server Express Migration ASP SQL queries language

Posted on 2007-04-01
5
273 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now