How do you import a Table from SQL server to Access with punctuation problems?

[anoble1]

Hi,

I am having some problems (big problems) importing/Linking tables from the SQL Server to MS Access. Here is the problem: In the SQL server there is a table I need that it wont let me import because it has a column named this:

No. of Days

Yeah, has a punctuation and spaces in it. There is too much stuff tied to it since it was created many years ago that go out to a lot of customers. But i need it in my Access DB. Any ideas of making tricking MS Access to make it link that table?

Maybe there is some code that can change that column and name it something else before the linking? (Probably not if you always wanted it updated but worth a shot)..

Thanks,

[NeoPa]

Why not create a view in the SQL Server that simply relays all the data from the table but, for that one field, renames it to something acceptable?

Better yet, provide a view for the existing cr*p and update the actual table to reflect a properly named system ;-) You would then need to create the view with the same name as the existing table, and use a different name for the updated table.

[anoble1]

How do I do those in SQL Server? Create views? Most of my skills are in Access, but limited ha! Right now I may try the 1st suggestion, then move to the other one to "correct" it. But I'm not sure how to create a view that relays data.

[NeoPa]

A View is essentially SQL Server terminology for a query. It's pure T-SQL, but a pass-through (Don't confuse with Pass-Thru in Access.) view is generally as simple as :

SELECT *
FROM   [Table]

If you're not responsible for the company SQL Server then I strongly recommend you deal with whomever is.

[anoble1]

Is there anyway to do this in Access in VB?

[NeoPa]

Do you mean is there any way to set up the view in Access VBA, or are you asking if there is a way that doesn't require controlling access to the SQL Server?

[anoble1]

Is there a way to do the changes in MS Access. Maybe through VB import table by table, then when you get to the problem table rename the columns somehting else and finish the link?

[NeoPa]

I don't think so. You could rename the table after a successful link, but renaming of the field would need to be done at the server end I believe.

[anoble1]

Update:
I worked around the problem. I went into Access and created a New Pass Through Query. From the pass through query I told it to go to the SQL Server and SELECT * from tblBillHistory, which in return gave me the whole table, but for that 1 column that was messed up, it gave wiped out that column.

[NeoPa]

If you're using a PassThru query you can rename the field in the query. Just say :

SELECT *
     , [No. Of Days] AS [NoOfDays]
FROM   [SQLTable]