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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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:

Code:
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:

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,969
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

Board Regular
Joined
Dec 19, 2014
Messages
203
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,955
Members
416,002
Latest member
t10k14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top