Column names changing in a linked spreadsheet

[tbtcust]

Hello everyone. I have an excel spreadsheet linked as a table in a database. I have several queries that use this table. The column names in the linked spreadsheet is constantly changing, which is impacting all the queries. Columns are constantly being added and deleted as well.

Can someone recommend a method or two that I can use to reduce impact on the queries?

Thanks in advance for any help.

 

[Golom]

They are just changing mysteriously or someone is opening the spreadsheet and changing them?

 

[SkipVought]

Holy Macerel tblcust.

Columns are constantly being added and deleted as well.

Can someone recommend a method or two that I can use to reduce impact on the queries?

Like the patient who complained to the doctor, that every time he hit his head with a hammer, his head hurt. And the doctor replied, "Stop hitting your head with a hammer, and that will be $100 for expert advice."

Only here, there is no charge.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

I suppose you could record what fields and data types you expect to have available and examine the linked spreadsheet to see if everything that you expect is as it should be. If it isn't then don't proceed any further.

I certainly wouldn't trust anything coming out of a system that was based on missing or corrupt data.

 

[SkipVought]

IMNSHO, an uncontrolled workbook ought not to be considered a valid data source.

Consequently, a significant level active, intelligent intervention would be prudent with each attempted access.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tbtcust]

Thanks everyone for your suggestions.

Did some reading on other sites and came up with the notion of a data access layer, which amounts to a set of select queries against the linked spreadsheet. I have standard names in the DA queries. Other queries, reports, etc, use the DA queries. Now when there is a change to the linked spreadsheet I update the DA queries and there is no impact downstream.

Thanks again everyone.