Are syntax of queries same in MS Access and MS SQL 2005?
I am asking because I have lot of queries in Access database. Do I have to
change every query when I move to MS SQL 2005?

Thanks!!!

Hi

No. Most will work the same, but certain Access specific implementations of
queries will not.

If a query does not work with SQL Server 2000, it won't in SQL Server 2005.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

Some basic queries might be portable between MS Access and SQL Server but if
you've used stuff like most of the VBA functions or some of the non-standard
SQL elements of Access then they'll need some work. If you are porting an
application to SQL Server then to get the most out of the platform you
should aim to convert your queries and other data-access code into SQL
stored procedures. That'll almost certainly involve a significant re-write
of your app. How far you need to go will depend partly on what you want to
gain from switching platforms.

Also, if your data model is of more than  trivial complexity you should
certainly review it when upsizing. Many people use data models in Access
that are poorly normalized. These are OK in Access because Access often lets
you do non-relational stuff in the database to cope with problems like
missing keys or redundant data. In SQL Server you are more likely to have
serious problems with a weak data model. Of course, you may already be
totally confident that your data is strictly Third Normal Form, which will
give you a head start.

Hope this helps.

Thanks David,

there are no VBA elements, because I am using VB 6.0 and Access as database,
but some queries are not in VB code, but in Access database, like cross tab
queries.

John:

Cross-tab queries in Access have no equivalent in SQL Server. Also, as was
mentioned before by others in this thread, VB functions in Access won't work
in SQL.

You may want to check out the following: "Microsoft Access Developer's Guide
to SQL Server" by Mary Chipman and Andy Baron (ISBN 0-672-31944-6), available
at Amazon.com for under $10 used.

Going through an upgrade from MDB to ADP/SQL I learned to watch for the
"Now()" function in many Access queries, which were replaced with
"GETDATE()". You will need to see what functions are in use.

Good luck.

Todd

syntax error in CONTAINS query
need help with execution plan
Calling of Store Procs on SQL Server from MS Access
Understanding best way to optimize...
Squeezing out more performance!!