Setting up Access in SQL Server: Field names with spaces

[phweston]

Hi,
I am in the process of starting to switch over an Access Database to SQL Server. The Access DB was not origanlly set up by me, and there are Field names with spaces in the actual Field name(Example: Customer Name as opposed to having Customer_Name or CustomerName).

I tried importing the tables from Access to SQL, and it appears that these Field Names came over okay with out any issues that I can see.

Should we change these Fields names in the Access Database before importing the tables, or can we leaves the names as is?

 

[ptheriault]

If your field names have spaces in them you will need to use brackets when you refernce them. for example
SELECT Customer Name
FROM table

will fail.

But
SELECT [Customer Name]
FROM table

Will not.

I think it is a pain in the but to have to remember to use brackets all the time so I would remove them.


- Paul
- Database performance looks fine, it must be the Network!

 

[phweston]

Thanks.

The database we are using is a Back End Database, and the Front End(in Access) is already set up with these field names with spaces in the name. All our queries are set up using these Field Names, so we are debating whether or not we should start changing stuff.

 

[ptheriault]

I would change them. If for no other reason but just to follow SQL Server best practices.


- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

If you use the Import/Export wizard to move your tables (with data intact), you can specify in your Transformations tab the changes to column names. Otherwise you'll have to do the changes right before or right after the move.

Regardless of how you do it, I think you're going to have to do each one by hand.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"