Query loses links when table is missing

[sharpnova]

I recently opened a query (in design view) that referenced a table whose link was not currently intact.

The query gave an error message upon opening it in design view and then showed the query, with the table in question empty of any fields (normal) and all links between this table and the other broken.

I closed the query and wasn't asked to save. I didn't save.

I restored the table and the query is still screwed up.

Why does Access destroy queries without regard and give no way to recover the information about the structure of the query?

And how can I avoid this happening?

 

[GohDiamond]

Since queries have to be based on something, either TABLES or other queries, the loss of the foundational datasource is devastating to the query. If the foundational Table or Query is restored before opening the higher query, in most cases Access will grant you GRACE and retain all the information that was incorporated in that query. But if you open it in a broken condition then, c'est la vie... all that is gone.

After creating a query, go to SQL view, Copy the SQL used to create the query and save it somewhere as text. All the Tables, Relationships, criteria and ancillary sorts or renaming of fields or formulas will be there. It's a form of BACKUP.

Also, and I hate to blow this horn but: BACKING UP YOUR DATABASE on a regular schedule can help immensely

Cheers!
Goh

 

[sharpnova]

Since queries have to be based on something, either TABLES or other queries, the loss of the foundational datasource is devastating to the query. If the foundational Table or Query is restored before opening the higher query, in most cases Access will grant you GRACE and retain all the information that was incorporated in that query. But if you open it in a broken condition then, c'est la vie... all that is gone.

After creating a query, go to SQL view, Copy the SQL used to create the query and save it somewhere as text. All the Tables, Relationships, criteria and ancillary sorts or renaming of fields or formulas will be there. It's a form of BACKUP.

Also, and I hate to blow this horn but: BACKING UP YOUR DATABASE on a regular schedule can help immensely

Cheers!
Goh

Well I create about 100 queries a day against hundreds of tables in dozens of databases.

The underlying issue is the network is flaky and a link will break temporarily. When I said "restored the table" I was really referring to simply waiting a second and trying again.

Copying the SQL from each query would be cumbersome and slow me down. As for backing up the database, I do this daily. I could do it more frequently but that would also cause a big performance hit. (Most of the Access apps I work with are over a gig and I'm working through a virtual desktop which slows everything down by another layer of simulation beyond the crappy run-times we get in real life due to this universe being simulated imperfectly (not really even down to the electron))

It just seems idiotic to me that all the information about the query is there. And then it gets overwritten without my authorization and no ability to revert back. I don't understand why it doesn't just break.. and then ask if I want to save the changes that it made. And if I say no, revert to the way it used to be and accept that maybe the user will restore some of the missing stuff due to the fact that.. Oh I don't know.. it just alerted me to the fact that there was broken stuff that needed restoration.

 

[spikepl]

As your problem seems linked to flaky network, then that surely doesn't go away by itself?

The main issue is simple: how much effort do you spend on safeguarding your work, vs. recreating lost items. And any true geek would ask: how can I automate that

You may want to have a peek at this post: http://www.access-programmers.co.uk/forums/showthread.php?t=99179 , if you haven't split your giga-apps.

 

[GohDiamond]

OK so I simulated your situation in MS Access version 2003 which is what I use most. I renamed an underlying datasource that was joined to another table LEFTJOIN and checked the number of resulting records which were 999, just for reference here.

When I tried to open the now broken query I got the message:
The Microsoft Jet engine cannot find the input table or query 'name of the missing object'. Make sure it exists and that its name is spelled correctly.

All I could do was click OK or Help. I clicked OK

Then I opened the query in Design View and got the message:
Microsoft Office Access can't represent the join expression 'expression here' in Design View.
Along with the usual caveats about deletions or renamed objects or fields or use of operators that aren't supported in Design view.

All I could do was click on OK, so I clicked it

The Design view opened with only 1 table intact, no JOIN, and an empty table object for the table that should have been there. However, the criteria and the skeletal structure of the query remained, like placeholders for when the table might be restored.

I simply closed the query and was asked if i wanted to Save it or not. I chose NOT.

I then restored the underlying table to its correctly spelled former self and tried to open the query. It opened again just like nothing had ever happened with the same 999 records. Upon inspection in DESIGN VIEW, this time allowed because all the parts of the puzzle had been restored, and found that the entire query was intact including the JOIN and Criteria.

What I'm saying is that THIS is the normal behavior of ACCESS, if that didn't happen in your case there must be something else going on. If on closing the broken Query, Access didn't ask you if you wanted to save it was probably because it had already been saved in its broken state somehow.

Just saying....
Goh

 

[sharpnova]

Yeah the primary difference between our two entity-universe instances was that Access asked me nothing upon closing the query. Just gave me the same two error messages you got.

I suspect your locality may have a finer grain of simulation than mine.