Solved

MS Access to SQL Server Express Migration ASP SQL queries language

Posted on 2007-04-01
5
276 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

920 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

18 Experts available now in Live!

Get 1:1 Help Now