I'm using DTS to import data from an Access database to SQL Server 2005. It seems that DTS imports Access queries as tables instead of views, which won't work for me. Is there any way around that?
+1
A:
You can choose to not include the saved queries. (at least you can when using the SSMA - I suggest you use this in place of DTS anyway…it tends to do a better job).
you can find it here: http://www.microsoft.com/sqlserver/2005/en/us/migration.aspx
It is not clear if you going to continue to use ms-access as the front end here or not?. If you plan to continue using access then you really don’t need to convert those saved queries (views) up to sql server anyway. Most of the saved queries in access will work as before (now with linked tables to sql server).
You only need change/fix those saved quires that run slow. In other words most queries can continue to be used and run as is. It is only the slow ones and especially the ones with aggregate functions (sums, totals etc that process many records, but produce few rows). This types of queries really benefit from being moved up to sql server as a view (you then link to that view from ms-access).
If your not keeping any part of ms-access, then I am afraid there no automated tool for those queries. In these cases I just do a cut + paste from ms-access right into the management studio view builder. Most queries require very little modifications.
Albert D. Kallal
2009-07-23 03:13:33
My app supports several dbs, and access isn't technically one of them. I tried SSMA to do the whole import, but for some reason the app didn't work with the resulting db. So, I used DTS to do the tables and SSMA to do the queries (converted to views just fine) and it seems to work. Very weird.
jobrahms
2009-07-23 17:22:19
@jobrahms: Access is not a database.
David-W-Fenton
2009-07-23 19:44:36
@David W. Fenton Thanks for not helping! pedant...
jobrahms
2009-08-18 20:17:41