Field Name Aliases Disappear when mdb copied

[the_net_2.0]

All,

Strange issue here - I have a template mdb file. In that file, I have a query like so:

INSERT INTO [table0] ( [1], [2], [3] )
SELECT [table1].[field1] AS Expr1, [table1].[field2] AS Expr2, [table1].[field3], 'N' as Expr1
 
FROM [table1];

When my app runs and this file is copied to a directory where the app looks for the file and manipulates data in it, the query changes to this after the copy operation:

INSERT INTO [table0] ( [1], [2], [3] )
SELECT [table1].[field1], [table1].[field2], [table1].[field3], 'N' as Expr1
 
FROM [table1];

The field aliases disappear. I can't seem to figure out why they DON'T disappear after I make any other structural changes to other tables in the template db file. I can virtually do anything, and at that point they don't disappear anymore.

I can't have them hang around because after a while the code runs this query (append type query). And the error is:

Duplicate output field name 'Expr1' in query.

I am at a loss. No idea what's happening. Any insight folks?

thanks!

 

[vbaInet]

Do you realise that you've given two fields the same alias, i.e. Expr1?

 

[the_net_2.0]

Do you realise that you've given two fields the same alias, i.e. Expr1?

The post says that these aliases DISAPPEAR and the actual field names are used once the template is copied. The problem is the structure changes to OTHER tables, which obviously have nothing to do with the queries (my changes don't anyway)

You were really helpful to me, until today.

by the way, this is ADAM. e.g. - ajetrumpet? You know me, remember?

 

[vbaInet]

I'm guessing that the engine is replacing your field names because they are duplicates I don't fully understand your query.

Well, you can't get it good everyday

Yes, I knew it was you. No suprises there Remember when I mentioned in a post one or two weeks ago that you should know more about who the site Mods were before I joined

 

[the_net_2.0]

I'm guessing that the engine is replacing your field names because they are duplicates I don't fully understand your query.

how could you not understand the query?

and the engine is NOT eliminating them because they're dups. Notice, the aliases are:

*Expr1
*Expr1
*Expr2

this is an example of course. the real query we use appends about 20 fields, and about half of them use aliases like "Expr1". In that query, only 2 appended fields use the alias "Expr1" and those 2 are also the only 2 that are duplicated. The rest use "Expr2"/"Expr5", etc...

 

[vbaInet]

how could you not understand the query?

That was used interchangeably I understand your query but I don't understand your problem (is what I meant).

Well, do you see a pattern there? Are the aliases getting deleted for non calculated fields? That is, is it only happening on fields that exist in the query?

 

[the_net_2.0]

Well, do you see a pattern there? Are the aliases getting deleted for non calculated fields? That is, is it only happening on fields that exist in the query?

Here's a short sequential list of what is going on:

• append query with alias names exist in an mdb
• the target table (table being appended to) does not exist in the mdb
• vb6 code runs and copies mdb
• append query changes to "new form", essentially eliminating all of the "AS [Expr#]" statements in the query's sql

so, here is sample sql before the mdb is copied by the vb6 program:

INSERT INTO [table0] ( [1], [2], [3], [4] )
SELECT [table1].[field1] AS Expr1, [table1].[field2] AS Expr2, [table1].[field3], 'N' as Expr1
 
FROM [table1];

and here is the query's sql after the mdb is copied:

INSERT INTO [table0] ( [1], [2], [3], [4] )
SELECT [table1].[field1], [table1].[field2], [table1].[field3], 'N' as Expr1
 
FROM [table1];

As you can see, the only appended value that is NOT losing it's alias is the actual 'N' value (which obviously is not coming from a field in table1).

And in case you're wondering, this query (right before being executed) is preceeded with the execution of this query:

SELECT [table].[field1], [table].[field2], [table].[field3], String(5, " ") INTO table2
 
FROM [table];

so in short, the query operations are:

• make table query forms table2, sourced from table1
• append query appends records (and an extra field) from table 3, to table2

clear as mud?

 

[vbaInet]

That's much better

In the context of an INSERT INTO statement giving your field names aliases within the record source of the insert is irrelevant which is most likely why the Jet engine rightfully (and politely) corrects the syntax. This is because you are not selecting records for display purposes. Aliases are for display purposes, they are not meant to mean anything to the record source.

Just out of interest, what does that VB6 code you mentioned in your last post do exactly?

 

[the_net_2.0]

I know they are not meant to mean anything to the source, my friend.

But again, this answer of yours really doesn't address anything I asked about in the first post. Remember? Structural changes to other tables causes the aliases to NOT disappear?

But since you last posted, I've come to find out that these occurrances are completely random. Sometimes running the app code will eliminate the alias names and sometimes it won't. It's really bizarre. There really is no pattern to when it eliminates them and when it doesn't.

So what I've done to correct the issue is changed all of the "Expr" type alias names to alias names that match the actual field names, letter for letter. This has fixed the problem completely, so I've moved on.

Just out of interest, what does that VB6 code you mentioned in your last post do exactly?

Just out of secrecy, I'll tell you...the vb6 code in this particular portion of the app combines data from 2 tables, and that data eventually (along with plenty of other data, and after weeks and weeks of travel time through other systems and cities) ends up being a bunch of little 1's and 0's that are used to tell a pilot where his airplane is going when he's 40,000 above the ground. Oh yeah, and it also tells him other things like how, where, when and which way to turn the airplane at certain locations up there.

 

[vbaInet]

Just out of secrecy, I'll tell you...the vb6 code in this particular portion of the app combines data from 2 tables, and that data eventually (along with plenty of other data, and after weeks and weeks of travel time through other systems and cities) ends up being a bunch of little 1's and 0's that are used to tell a pilot where his airplane is going when he's 40,000 above the ground. Oh yeah, and it also tells him other things like how, where, when and which way to turn the airplane at certain locations up there.

Just tell me which planes use them and I'll try to avoid flying with them. Maybe the alias could get screwed up mid-flight

 

[the_net_2.0]

Maybe the alias could get screwed up mid-flight

OK, now you're really starting to scare me... Everything is OK with the planes that fly these datasets, because Jodi Foster does all the flight planning for them.

 

[vbaInet]

If Jodi is in the planning, then everything is alright