Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Will column name changes made by SSMA during migration from Access 2000 to SQL 2005 affect the functionality of the database?

Posted on 2008-06-21
9
Medium Priority
?
370 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:PDSWSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 21840627
As long as the column names were using valid characters in SQL Server, I would suggest trying to change them back (after taking a backup of your migrated database.. and even better, restoring a test/pilot copy to make the changes on). If you are using an existing Access Forms application and just changing the pointer on the back end to point to SQL Server 2005 then you would definitely want the object names to remain the same so your functionality remains the same. Or change the forms.
0
 

Author Comment

by:PDSWSS
ID: 21840682
Thanks. Thats what seemed to be necessary. I wanted to check with an expert before making the column name  changes. Could you suggest a resource that would clearly describe what characters are allowed in
SQL server.
0
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 2000 total points
ID: 21840817
You probably don't have invalid characters if they were valid in Access. I generally try to avoid using numbers and reserved keywords (http://msdn.microsoft.com/en-us/library/ms189822.aspx) when choosing names as this avoids confusion when accessing and using objects.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:PDSWSS
ID: 21841566
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,
0
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 2000 total points
ID: 21842868
So.. Will it allow you to create a column name like that? Yes. Should you? In my opinion, No.

I believe column names should not have those characters. It is a bad design practice and makes querying the objects have an extra "step". You would have to reference the column name in brackets.. Select * from Table1 where [City, Permanent Relative] = 'Manchester', for example.

So it is possible but it is not a best practice. Descriptive but short names are a better option. If you can have different types of Cities for whatever your application is maybe a more normalized manner would be to have a "CityType" table that describes CityTypes and a table that contains Cities of various types. (just one though, there are other methods).

So the answer to your question is yes but not a great practice. If it means you can use your migrated app easier change them back for now, keep the brackets in mind and think about a plan to look at your design and at least look at your column names in the future.

HTH - Mike
0
 

Author Comment

by:PDSWSS
ID: 21842927
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,
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21842946
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.
0
 

Author Comment

by:PDSWSS
ID: 21842975
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,
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21842989
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question