By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

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

anoble1
100+
P: 223
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:
Expand|Select|Wrap|Line Numbers
  1. 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,
May 11 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [Table]
If you're not responsible for the company SQL Server then I strongly recommend you deal with whomever is.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
May 11 '12 #2

anoble1
100+
P: 223
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.
May 14 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [Table]
If you're not responsible for the company SQL Server then I strongly recommend you deal with whomever is.
May 14 '12 #4

anoble1
100+
P: 223
Is there anyway to do this in Access in VB?
May 14 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
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?
May 14 '12 #6

anoble1
100+
P: 223
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?
May 15 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 15 '12 #8

anoble1
100+
P: 223
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.
May 16 '12 #9

NeoPa
Expert Mod 15k+
P: 31,186
If you're using a PassThru query you can rename the field in the query. Just say :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2.      , [No. Of Days] AS [NoOfDays]
  3. FROM   [SQLTable]
May 17 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.