Unexplained MakeTable SQL addition

[Guest]

Acess 2003, Windows XP Pro

I am using VBA to build an SQL string which is, in the end, a MakeTable
query from a Union Query. My basic stucture being:

SELECT * INTO tblMyNewTable FROM (SELECT.....UNION.......)

My problem is that when I view the SQL code after the definition has been
established by the VBA code, there is a short character string added onto the
end of my definition. That string is

AS [%$##@_Alias];

As always is important, the Make Table Query does seem to work, but what
does this short character string addition do. I have attempted to delete the
character string manually, but then there is a "FROM clause error" when I
do.

I guess I would feel better knowing I had properly generated the correct SQL
and did not rely upon an "intelligent" program making assumptions on my
ignorance.

Anyone have an explanation to ease my perhaps needless concern.

Thanks as always.

 

[Douglas J. Steele]

I wouldn't worry about it. I believe any "complex" query (like joining two
separate queries with a UNION operator) gets treated this way internally.

 

[Guest]

As always is important, the Make Table Query does seem to work, but what

does this short character string addition do.

It's an alias that Jet adds to the subquery in the FROM clause when
optimizing your query, because you didn't assign an alias to this subquery.
The times when this situation becomes a problem are when:

1. One copies this query or makes alterations to the query and/or the
subquery, because Jet will often find a syntax error with this syntax (even
though Jet wrote this "special" syntax); and

2. One uses Reserved words for, or illegal characters in, table, query, or
field names which require brackets surrounding the names so that Jet will
ignore these Reserved words or illegal characters and process these as
user-defined names. Embedded brackets within the subquery that is also
bracketed doesn't always get interpreted by Jet as one would assume it should
be.

The first problem can be fixed by removing the Jet-assigned alias and
replacing the two brackets with the original parentheses, then altering the
rest of the query as needed. When the query is saved, the brackets and
Jet-assigned alias will replace your parentheses, but the query will work
without generating the syntax error. And if you don't like the Jet-assigned
alias, you can assign your own.

The second problem doesn't _always_ happen, but it happens often enough that
one should _always_ avoid it by never using illegal characters or Reserved
words for any identifier. Only use alphanumeric characters and the
underscore character.

Anyone have an explanation to ease my perhaps needless concern.

As long as you aren't guilty of potential problems 1 and 2, then you needn't
be concerned. It's just Jet doing its housekeeping to optimize your query
for you -- and you can't do anything about it, except assign your own
descriptive alias.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Acess 2003, Windows XP Pro

I am using VBA to build an SQL string which is, in the end, a MakeTable
query from a Union Query. My basic stucture being:

SELECT * INTO tblMyNewTable FROM (SELECT.....UNION.......)

My problem is that when I view the SQL code after the definition has been
established by the VBA code, there is a short character string added onto the
end of my definition. That string is

AS [%$##@_Alias];

As always is important, the Make Table Query does seem to work, but what
does this short character string addition do. I have attempted to delete the
character string manually, but then there is a "FROM clause error" when I
do.

I guess I would feel better knowing I had properly generated the correct SQL
and did not rely upon an "intelligent" program making assumptions on my
ignorance.

Anyone have an explanation to ease my perhaps needless concern.

Thanks as always.

 

[Guest]

Thanks Doug...

Your informed assuarance will help me sleep! Thanks for taking the time to
respond.

Cheers......Keith

I wouldn't worry about it. I believe any "complex" query (like joining two
separate queries with a UNION operator) gets treated this way internally.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Acess 2003, Windows XP Pro

I am using VBA to build an SQL string which is, in the end, a MakeTable
query from a Union Query. My basic stucture being:

SELECT * INTO tblMyNewTable FROM (SELECT.....UNION.......)

My problem is that when I view the SQL code after the definition has been
established by the VBA code, there is a short character string added onto
the
end of my definition. That string is

AS [%$##@_Alias];

As always is important, the Make Table Query does seem to work, but what
does this short character string addition do. I have attempted to delete
the
character string manually, but then there is a "FROM clause error" when I
do.

I guess I would feel better knowing I had properly generated the correct
SQL
and did not rely upon an "intelligent" program making assumptions on my
ignorance.

Anyone have an explanation to ease my perhaps needless concern.

Thanks as always.

 

[Guest]

I want to say thanks, but again, Gunny,

I have great confidence in your replys. Your extensive and complete
knowledge always shines through with your responses. You are certainly
generous with your time in producing complete and informed responses. If
your life has not included time as an educator, you would have done well in
the field.

With your answer, I can proceed with confidence.

Many Thanks .......Keith

As always is important, the Make Table Query does seem to work, but what
does this short character string addition do.

It's an alias that Jet adds to the subquery in the FROM clause when
optimizing your query, because you didn't assign an alias to this subquery.
The times when this situation becomes a problem are when:

1. One copies this query or makes alterations to the query and/or the
subquery, because Jet will often find a syntax error with this syntax (even
though Jet wrote this "special" syntax); and

2. One uses Reserved words for, or illegal characters in, table, query, or
field names which require brackets surrounding the names so that Jet will
ignore these Reserved words or illegal characters and process these as
user-defined names. Embedded brackets within the subquery that is also
bracketed doesn't always get interpreted by Jet as one would assume it should
be.

The first problem can be fixed by removing the Jet-assigned alias and
replacing the two brackets with the original parentheses, then altering the
rest of the query as needed. When the query is saved, the brackets and
Jet-assigned alias will replace your parentheses, but the query will work
without generating the syntax error. And if you don't like the Jet-assigned
alias, you can assign your own.

The second problem doesn't _always_ happen, but it happens often enough that
one should _always_ avoid it by never using illegal characters or Reserved
words for any identifier. Only use alphanumeric characters and the
underscore character.

Anyone have an explanation to ease my perhaps needless concern.

As long as you aren't guilty of potential problems 1 and 2, then you needn't
be concerned. It's just Jet doing its housekeeping to optimize your query
for you -- and you can't do anything about it, except assign your own
descriptive alias.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Acess 2003, Windows XP Pro

I am using VBA to build an SQL string which is, in the end, a MakeTable
query from a Union Query. My basic stucture being:

SELECT * INTO tblMyNewTable FROM (SELECT.....UNION.......)

My problem is that when I view the SQL code after the definition has been
established by the VBA code, there is a short character string added onto the
end of my definition. That string is

AS [%$##@_Alias];

As always is important, the Make Table Query does seem to work, but what
does this short character string addition do. I have attempted to delete the
character string manually, but then there is a "FROM clause error" when I
do.

I guess I would feel better knowing I had properly generated the correct SQL
and did not rely upon an "intelligent" program making assumptions on my
ignorance.

Anyone have an explanation to ease my perhaps needless concern.

Thanks as always.