PDSWSS
asked on
Will column name changes made by SSMA during migration from Access 2000 to SQL 2005 affect the functionality of the database?
I recently used SSMA to migrate the backend of our Access database in SQL Server 2005. I noticed
that in a couple of tables some of the column names had been changed (words deleted or rearranged).
How will these column name changes affect the functionality of the database e.g., use of forms etc.?
Can I change column names back to same field names as they were previously in Access 2000?
If not, what is the solution? Thanks
that in a couple of tables some of the column names had been changed (words deleted or rearranged).
How will these column name changes affect the functionality of the database e.g., use of forms etc.?
Can I change column names back to same field names as they were previously in Access 2000?
If not, what is the solution? Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again for your help. Examples of column name changes =
Access = Name of Group SQL = name
City, Permanent Relative relative_permanent_city
Are spaces and commas not allowed in SQL? If so, then SQL will would not allow me to change to the Access column names and I would need to change in Access. Is that correct? Thanks,
Access = Name of Group SQL = name
City, Permanent Relative relative_permanent_city
Are spaces and commas not allowed in SQL? If so, then SQL will would not allow me to change to the Access column names and I would need to change in Access. Is that correct? Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is a temporary situation. We are redesigning the database. I will keep your suggestions in mind.
Just for clarity - So you can name a column "City, Permanent Relative" in SQL? Spaces between words are permitted? I had assumed the name was changed by SSMA because spaces were not allowed. I'm glad I was wrong.
When you say "I believe a column name should not have those characters," its not clear to me which
characters you mean? The Access titles are composed of letters, spaces and a comma?
Also why do you need to place the column name in brackets when running a query?
Thanks,
Just for clarity - So you can name a column "City, Permanent Relative" in SQL? Spaces between words are permitted? I had assumed the name was changed by SSMA because spaces were not allowed. I'm glad I was wrong.
When you say "I believe a column name should not have those characters," its not clear to me which
characters you mean? The Access titles are composed of letters, spaces and a comma?
Also why do you need to place the column name in brackets when running a query?
Thanks,
The characters to which I was referring was Spaces and Commas. I like just letters in my column names. Preferably words, without spaces. But yes it would be allowed and would need to be accessed as [City, Permanent Relative].
The reason for the brackets is because when SQL bumps into the space it would normally assume you are trying to give another column name (i.e. SELECT col1, col2, col3) and when you hit the space the query engine will presume you are trying to either provide an alias or do another SQL operation and will error out.
The reason for the brackets is because when SQL bumps into the space it would normally assume you are trying to give another column name (i.e. SELECT col1, col2, col3) and when you hit the space the query engine will presume you are trying to either provide an alias or do another SQL operation and will error out.
ASKER
Thanks for the clarification and all your feedback. I will change the column names back to their original access names tomorrow and continue to test the database. After I do that, I will award you the points and close the question.
BTW- Do you run your Queries in SQL? We still run queries through the Access front end. It appears that queries are easier to set up in Access are also more limited in SQL since action queries are not allowed.
What is your experience with this? Thanks again,
BTW- Do you run your Queries in SQL? We still run queries through the Access front end. It appears that queries are easier to set up in Access are also more limited in SQL since action queries are not allowed.
What is your experience with this? Thanks again,
To be honest I don't run any Access apps and when I have I have done a hybrid approach.
Generally speaking I try to have most of my applications talk to SQL through stored procedures so it is entirely using SQL code and then that data is brought to the application layer where business rules/presentation rules/etc are applied and the data is interacted with. That is just my preference. There are many ways to do it and pros of each and people who swear by each and they are not all wrong. Whatever gives you the performance, security, flexibility and ease of coding.
Generally speaking I try to have most of my applications talk to SQL through stored procedures so it is entirely using SQL code and then that data is brought to the application layer where business rules/presentation rules/etc are applied and the data is interacted with. That is just my preference. There are many ways to do it and pros of each and people who swear by each and they are not all wrong. Whatever gives you the performance, security, flexibility and ease of coding.
ASKER
SQL server.