June 15, 2003 at 2:10 pm
I am new to SQL Server and will be importing tables from MSAccess to SQL Server. The front end will remain in MSAccess. It is currently linked to MSAccess tables and will now be changed to link to the SQL tables. I've already done this in test mode and it seems pretty straightforward.
But now the requirements include changing some of the field names(columns) for a new .NET reporting function and I'm not sure how to go about that.
The worse case scenario seems to be creating SQL tables with new column names, updating the tables with MS Access data and then changing all field references in the MS Access front end (forms, queries etc). But there must be an easier way.
Any ideas? Is there something that can done with aliases or some other approach?
June 15, 2003 at 3:54 pm
A view is usually a good way to do this. You can either change the table and use a view to make it look as though nothing has changed, or leave the table as is and use the view to make things look different.
If you've got a table called Orders that you actually want to modify, make your changes and then rename the table, maybe OrdersNew or whatever. Then create a view called Orders that is just a select * from OrdersNew. If I recall correctly, you'd have to relink the table in Access AND tell it what the primarykey is, it's not smart enough to figure out the pkey on it's own from a view.
Column name changes are expensive. If they need to be done, bite the bullet and do it. If it's just to make things pretty, just say no!
Andy
June 15, 2003 at 8:57 pm
quote:
A view is usually a good way to do this. You can either change the table and use a view to make it look as though nothing has changed, or leave the table as is and use the view to make things look different.If you've got a table called Orders that you actually want to modify, make your changes and then rename the table, maybe OrdersNew or whatever. Then create a view called Orders that is just a select * from OrdersNew. If I recall correctly, you'd have to relink the table in Access AND tell it what the primarykey is, it's not smart enough to figure out the pkey on it's own from a view.
Column name changes are expensive. If they need to be done, bite the bullet and do it. If it's just to make things pretty, just say no!
Andy
http://www.sqlservercentral.com/columnists/awarren/
Thanks - I'll give that a shot.
June 17, 2003 at 7:16 pm
I believe all you need to do is use an alias in your view and/or query to match the old column name.
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply