Access query QBE expr aliases popping up

[DaveBo]

I've written many queries in the Access QBE that reference linked tables.
Often, when I reopen a query many of the fieldnames are suddenly replaced with Expr1: OldName, Expr2: OldName2 etc.

Any idea why this is happening?
How to prevent it or how to fix it?

I assume it's got something to do with the remote (SQLserver) table structure changing, e.g. new fields added, but I religiously do the "update linked tables" thing.

Thanks, DaveBo

[RobDog888]

Yes, its because the table definitions have changed and Access can not find the field. Aliasing changes can affect this too.

Adding fields to a table shouldnt affect your query but removing them will.

[DaveBo]

Yes, you're right.

My major problem was that I automatically import files, e.g. from CSVs and XLSs using the TransferText & TransferSpreadsheet methods in VBA, but always to the same temporary table name, i.e. it's constantly changing structure.

If I happen to open one of the queries written for a particular input file after I've imported a different file, then the fields suddenly don't line up.

Thanks, DaveBo

[RobDog888]

Well you may need to change your code to validate the fields are existing in the table so when the query runs its good. I dont know how you will be able to predict new fields to add in your query but perhaps its a manual intervention process solution only.

[Al42]

If this is in a business environment, we charge customers a higher rate for data that has to be handled manually. It's amazing how many of them (and there are huge multinationals) supply us with consistent data after having a few of their submissions manually entered.