A Relationship Question!

[khwaja]

I have a master (stores) table with one to many relationship to another projects table. I am also using the master table with a one to one relationship to another surveilance installation table. Here, there might be some some questions raised as to the need for having 1 to 1 relationship but I just need to have this as part of extended number of attributes in madter table. One store may one surviellance installed.

The issue is that if someone is working in a screen based on master-projects table, others working in another form based oon 1 to 1 relationship get out of memory error message when they try to close the form. Is there anyway, this can be avoided. Could I avert this error by changing the relation to 1 to many and if so, how could that be done.



Cheers

AK

Note: Using Access 97 - still.

 

[Norris68]

When I did this, I used a LEFT JOIN to the optional table i.e. the main form recordsource would be something like SELECT Stores.*, Surveilance.* FROM Stores LEFT JOIN Surveilance ON ...

The two tables were each displayed on their own tab in a tab control. They have a 1:1 relationship (inherent when you define a relationship on the two primary keys). I have had no reports of any problems yet. It even seems to be intelligent enough to update the primary table before the secondary one.

 

[khwaja]

Thanks for the insight. I tried your solution but to no avail. However, I have narrowed down the problem - so that if one user accesses the DB via the switchboard and the other is already using the form based on secondary table, he gets 'out of memory' message when trying to close the form (using a close button). If switchboard is not used, then there is no issue. I find it quite suprising. Unforunately, there is no help on this error message. Any further ideas? This could have a conflict with close button and the switchboard?

Cheers

AK

Note: Using Access 97 - still.

 

[willir]

khwaja

Out of memmory when closing a form? I do not think changing your database design is the answer. In general, a better designed database is easier to work with.

How large is your database -- number of records in your tables?

Then how many records does your query return? Run through each query used in your forms.

Is the number of records returned as expected or very high?

I think the term is "cartesian product" where a query returns tblA x tblB records. Some times this is because of a poorly designed query -- eg. improperly set criteria, nulls. If so, then this part is okay.

However, some queries are less effecient that others because they need to generate a cartesian product and then filter down to get the desired result. Specifically, some queries have more overhead than others.

"Outer joins" where you want all records in one table regardless of matches (nulls) on the joining table will have more overhead.

Theta joins, ie comparison joins, x > y type of thing, will have more overhead.

So re-thingkig some of the queries may work.

Perhaps using the database analyzer utility (under Tools) may help.

NEXT, could this be a PC specific thing -- the out of space thing happens on PC's with small hard drives, lots of TEMP files? Some "PC Therapy" may be in order.

Lastly, your "CLOSE" button may be a clue. Does it run any code??

Richard