lost joins in frontend

[EVPLS]

Hi,

for the second time I just encountered a problem with queries in my
frontend. The underlying tables in the backend have joins, those joins
"disappeared" from the query. Example:

query in frontend:
SELECT I.ItemNo AS Expr1, T.ItemNo AS Expr2 FROM tblItem AS T,
tblItemIndustry AS I WHERE (((I.ItemNo) Is Null));

same query in backend:
SELECT I.ItemNo , T.ItemNo FROM tblItemTrunk AS T INNER JOIN tblItemIndustry
ON T.ItemNo = I.ItemNo WHERE (((I.ItemNo ) Is Null));

At the time I had designed the query (in the frontend), the relationships
were present and the query worked fine until yesterday. It has been my
understanding that linked tables in the frontend inherit relationships from
their "original" tables in the backend. The funny thing is that relationships
show in the Relationships window of both backend and frontend (while I am NOT
trying to "double up" on relationships in the frontend).

Has anyone got an idea what may have caused this effect and how it could be
avoided?

Thank you very much for any hints.

 

[Jeanette Cunningham]

This may be a sign of corruption. Name AutoCorrect may be involved in this.
I assume you have a recent backup you could use, if not try these steps to
create a new database.

1. Create a new (blank) database.

2. Before doing anything else, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect.

3. Import the tables from the problem database:
File | Get External Data | Import (A2003)
Select all the tables on the first tab of this dialog.

4. Now import the other objects from your problem database:

File | Get External Data| Import (A2003)
and select all the queries, forms, reports, etc.

5. Press Ctrl+G to open the Immediate Window.
Choose References from the Tools menu.
Set up the references you had before.

6. Still in the code window, choose Compile from the Debug menu to make sure
any code can be understood.

Jeanette Cunningham

 

[EVPLS]

Hello Jeanette,

thank you very much for your detailed reply. I had followed your steps 1
thru 6 only yesterday when it had shown signs of corruption. But I admit I
hadn't checked the queries, I may have imported them corrupted. I'll uncheck
AutoCorrect and keep my fingers crossed that loosing the relations won't
happen again.

Would it make sense to relocate the queries into the backend? It's hardly
ever touched and may not corrupt as quickly as the frontend.

Again, many thanks for your help.

 

[Jeanette Cunningham]

After you uncheck Name AutoCorrect, do a compact and repair.
For tips on how to prevent corruption check this website.
http://www.allenbrowne.com/ser-25.html
Make sure to keep the queries in the frontend. The backend just stores the
tables.
Hopefully you will experience much less corruption from now on.
Be sure to backup your front end at least every day you make changes to it.

Jeanette Cunningham