By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

converting queries from Access2k to MsSql2k

P: n/a
Hi,

I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.

Using the Import utility in Sql Server, the Access queries seem to get
executed and the resultant data imported as tables. Oops!

Using the Upsize lizard in Access 2003, the queries aren't even in the
selection list of "tables" to upsize. It looks like the Upsize wizard
isn't supposed to do queries.

How does one automate the migration of standard conforming queries to
Sql Server?

Of course, I expect to spend some time addressing the stickier
non-compliant queries, but there must be a way to do the easy ones.
What am I missing?

-Dave

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
nib
dschl wrote:
Hi,

I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.

Using the Import utility in Sql Server, the Access queries seem to get
executed and the resultant data imported as tables. Oops!

Using the Upsize lizard in Access 2003, the queries aren't even in the
selection list of "tables" to upsize. It looks like the Upsize wizard
isn't supposed to do queries.

How does one automate the migration of standard conforming queries to
Sql Server?

Of course, I expect to spend some time addressing the stickier
non-compliant queries, but there must be a way to do the easy ones.
What am I missing?

-Dave


I don't know that you can automagically convert queries from Access to
SQL. After all, the "easy" ones should already run in SQL.

All of Joe's ranting about writting standard SQL for easier migration
isn't sounding so silly after all... :D

Zach
Jul 23 '05 #2

P: n/a
Hi Dave,

The Import util. in SQL will give you the ability to import data from
tables as well as data from queries as if they were Views and not the
schema of the query. A dirty way of upsizing is to again use the Upsize
wizard in Access and when prompted accept the question to create an
Access project. This option whether you decide to keep the Access
Project front-end or not will upsize the schema for the tables and
queries. Through this process Query code that is T-SQL compliant and
not MS Access specific will be created on the SQL Server as a view,
stored proc. or function. A couple of words of advice on the upsize:

1 - Compile, compact and repair the db before upsize.
2 - Ensure that the code behind the queries are clean such as no double
quotes, no Access specific functions, etc...
3 - If large tables, upsize table schema in wizard and when complete
use DTS to import data, this will save some time.
4 - Verify through report at end of upsize, proper table and column
properties.

Hope that helps, Unfortunatly I have been through far to many of these.

RS
dschl wrote:
Hi,

I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.

Using the Import utility in Sql Server, the Access queries seem to get executed and the resultant data imported as tables. Oops!

Using the Upsize lizard in Access 2003, the queries aren't even in the selection list of "tables" to upsize. It looks like the Upsize wizard
isn't supposed to do queries.

How does one automate the migration of standard conforming queries to
Sql Server?

Of course, I expect to spend some time addressing the stickier
non-compliant queries, but there must be a way to do the easy ones.
What am I missing?

-Dave


Jul 23 '05 #3

P: n/a
how many queries you worried about?
i can probably rewrite 20 access queries per hour into SQL Server

Jul 23 '05 #4

P: n/a
I don't know if this would be of any help, but I do this with some
frequency and set up some guidelines for being more efficient with the
conversions.

See

http://thecodegallery.org/modules.ph...rder=0&thold=0

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.