what's the best way of importing a query from access to be a view in MSSQL2005?

[anniebai]

I've struggled on this for 30 mins, it was really simple on SQL 2000, but now i am using 2005.
I've tried the import/export wizard, the query is always automatically converted to a table. Is there a way to import the query to be a view in SQL 2005?
THanks very much!

[ck9663]

I haven't tried that myself but I think it's possible.

1. [Create a view in sql server.
2. Build your query using OPENROWSET (Check example C)

Good luck

-- CK

[anniebai]

Thank you very much, CK. I'll give it a try.

I haven't tried that myself but I think it's possible.

1. [Create a view in sql server.
2. Build your query using OPENROWSET (Check example C)

Good luck

-- CK

[taoh15]

Hi

This may seem realy silly but the easiest way is open up Access. Edit the query, change to SQL view, select all and copy.

Now open SQL Server Management studio add a view, don't select any table and just paste into the SQL code windows, save and give it name.

Cheers
Taoh

[ck9663]

Taoh is right. My suggestion is for you to use the same view in Access.But if you're trying to migrate your entire database to ms-sql, you can just create a view in sql-server, which Taoh is suggesting. Here's the complete syntax.

-- CK

[anniebai]

thanks Taoh and CK.
I don't understand why MSoft make this so difficult in 2005? does anyone know the reason behind?