Using VBA to remove the alias name from an SQL query string?

Question

I have an SQL query string. In this string are multiple different columns of data, all coming from the same table. For simplicities sake, here is a short example: SELECT 'A'.'B', 'A'.'C', 'A'.'D' Where 'A' is the alias name. My question is: Is there a way using VBA to take that query string, and remove all instances of the alias 'A' and just return the data columns 'B', 'C', 'D'? Please no full code, I like starting points. I like to fill in the pieces on my own. It helps the learning process.

What is the purpose of removing those aliases? If there are 2 tables with the same column name, your query will fail because it will be too ambiguous to the engine on which column to pull from which table. The alias helps define which column is the proper one. — TTeeple
– TTeeple, Commented Jul 1, 2015 at 14:47
Your example SQL is misinformed. Single quotes are not used for table and column aliases. Also, tag your question with the database you are actually using. — Gordon Linoff
– Gordon Linoff, Commented Jul 1, 2015 at 14:48
I suppose I should have been more clear. I do know that the alias is important and needed when using the query, but I just need the 'B','C','D' fields for other purposes. I apologize for the ambiguity. (still kind of new to this site) — Constuntine
– Constuntine, Commented Jul 1, 2015 at 14:51
@GordonLinoff Thanks. I don't know too much about SQL. I just wrote that short example in the same format that the string I have is. I just replaced the sensitive information with simple letters. — Constuntine
– Constuntine, Commented Jul 1, 2015 at 14:54

AnalystCave.com · Accepted Answer · 2015-07-01 14:53:28Z

1

Recover the SQL under the QueryTable like this:

Recover SQL:

Range("A1").Select 'Your query worksheet
With Selection.QueryTable
    queryText = .CommandText
End With

Then replace the aliases with LEFT, RIGHT, INSTR or RegExp object (if you want to learn something useful and quick use RegExp - regular expressions). If you need an example regex let me know in the comments. If you want to learn Regex in VBA I made a tutorial here.
Replace the query and refresh the QueryTable

Replace and refresh query

Range("A1").Select 'Your query worksheet
With Selection.QueryTable
    .CommandText = queryText
    .Refresh BackgroundQuery:=False
End With

answered Jul 1, 2015 at 14:53

AnalystCave.com

5,0242 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Constuntine Over a year ago

Thank you. I do believe this is what I needed.

JuniperSquared · Accepted Answer · 2015-07-01 14:48:38Z

1

Are you selecting straight from the table? Going straight from the table would give you the column name, unless you're performing some function in your query. Another option is:

Select [A] AS columnName, [B] AS ColumnName2, etc

answered Jul 1, 2015 at 14:48

JuniperSquared

1432 silver badges12 bronze badges

Collectives™ on Stack Overflow

Using VBA to remove the alias name from an SQL query string?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related