migrationms accesssql-server-2008
Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do
SELECT * from allProducts;
My questions is, what is the equivalent in Sql Server? I'm currently working on a project where we are converting all our ms access databases over to ms sql server. Tables get converted over to tables, but what about queries knowing very well our programmers used access queries as exampled above.
I don't really have any links, but I can give a general how-to of this:
After importing into Access: - A series of update scripts (or a single procedure, really) that copy the data into the appropriate tables - A purge statement clearing out the temp table for the next upload.
It sounds really simple, and it (actually) shouldn't be too difficult, but without knowing your table structure, it's hard to give true specifics.
Are the skills and projects using defined language? if so, this greatly simplifies the job.
Create new linked tables to the SQL Server, drop the Oracle-linked-tables, create new queries with the names of the original Oracle-linked-tables, and presto.
Having said that, if you do this in the Access user interface, Access will attempt to modify the existing queries automatically. You can change this behavior by deselecting the following options:
Caveat: You'd clearly want to do this on a test-copy of the database, then test all the functionality before declaring this a win!
Best Answer
A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you.
Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.