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
