Importing Queries to Blank DB created "Duplicate Output Alias" Errors

[AlexB123]

Hi All,

I'm working on upgrades for a DB, and for my updated version I began with a blank DB and imported all the forms, queries and tables. I made a number of changes to the forms, but left the queries and tables alone.

In the previous version, v.1.3, all my queries worked fine and were rigorously tested. Now, in my new version, v.1.4, the queries are throwing errors and breaking the code. In particular, I am receiving the error message "Run-Time Error '3062': Duplicate Output Alias 'Expr1'".

Comparing the original queries to the new ones, Access has inserted "Expr1", "Expr2", and so on to fields that had no aliases, which is creating a conflict because these "Expr" aliases already exist in some of my queries. For example:

An original query:

INSERT INTO tblExample ( PID, CID, ActionID, ActionDate, ActionUser, ActionNote )
SELECT tblOne.PID,
 tblOne.CID,
 Right(tblOne.[ActionID],3) AS Expr1,
 Now() AS Expr2,
 [Forms]![frmSwitch]![txtUser] AS Expr3,
 "Action Updated From " & [tblOne].[OldActionID] & " to " & Right([tblOne].[ActionID],3) AS Expr4
FROM tblOne
WHERE (tblOne.Status)=1;

The "new" query:

INSERT INTO tblExample ( PID, CID, ActionID, ActionDate, ActionUser, ActionNote )
SELECT tblOne.PID AS Expr1,
 tblOne.CID AS Expr2,
 Right(tblOne.[ActionID],3) AS Expr1,
 Now() AS Expr2,
 [Forms]![frmSwitch]![txtUser] AS Expr3,
 "Action Updated From " & [tblOne].[OldActionID] & " to " & Right([tblOne].[ActionID],3) AS Expr4
FROM tblOne
WHERE (tblOne.Status)=1;

Is there a setting I can turn off? Can I delete and reimport all the queries without these additional aliases? Is there anything I can do besides editing several hundred queries one at a time?

Thanks

 

[ranman256]

Check your AS ExprN
the query builder doesn't know where a previous query left off....
as Expr1,
as Expr2,....

you cannot have two Expr1's.

 

[AlexB123]

Right ... that is definitely what made the query break. But how/why did Access add that extra "ExprN"?

I deleted all the queries and re-imported them, and my application seems to work now (although I still need to rigorously test it all to make sure). But I would like to be able to ensure this doesn't happen again when I'm working on future releases ...

 

[xenou]

There's no setting for this. Offhand it seems you might want to import all the table first. Then all the queries. Then all the Modules and Macros. Then all the forms. Then all the reports. Hahaha. Or at least all tables first. Presumably that would be the order access would do it in if you select everything all at once. You might have shot yourself in the foot if you did import all queries yourself, before all tables.