How do I edit relationships in Access 2007 (1 Viewer)

[Chintsapete]

Hi all

every time I try to edit table relationships in relationship window the DB crashes. Is there any other way doing it? It's really frustrating, but I don't seem to find posts that work.
I tried to delete lookup fields but Access doesn't allow to do it. I even went as far as exporting table into new DB and delete field there and re-import it, but surely there is another way?!:banghead:

 

[jnolfo]

This may seem too rudimentary, but did you try Compact and Repair the database? Sometimes if the file size starts to big or becomes overly complex, Access crashes.

 

[jdraw]

Please tell us more about ths

I tried to delete lookup fields but Access doesn't allow to do it

We only know what you have written in your post, and there isn't much info there.
Some pictures or a copy of the database would be helpful, as would details of the issue you are encountering.

If you are using lookups at the table field level, many will tell you Don't do it.
see http://access.mvps.org/access/lookupfields.htm

 

[Chintsapete]

I did compact and repair it, but doesn't seem to do anything. It's an issue I have for quite a while just got to frustrated with it. I've seen article where they say hide tables and show only the tables you need to edit, but doesn't always work. I think it's a bug in access. Thank for the reply though.

What happens is when I make a table and lookup the supplier with a lookup field access relates the tables automatically as you all know, but in Acccess 2007 when I go to the relationships in database tools and try to delete some of the relationships I don't need no more (say tried something which didn't work) than the BD crashes every time.

For the lookup fields, what's a better way doing it. I did read the tutorial link, but it doesn't suggest a better solution. I know it's a pain to query with the table id numbers but it works, soma soma. I don't like it either to much.
Do you work with foreign ID fields and relate them instead?

 

[jdraw]

You create a separate table, and link it on FK.

see this thread for info

 

[Chintsapete]

Thank you appreciate it, I will look into it and try to better my ways

 

[jdraw]

It isn't that your ways are wrong. M$oft for some reason thought they were helping someone(s). The lookup field in the table has caused more confusion than good according to many; and is limited to Access. There is a traditional approach to lookup/reference/code tables that has been used for years, and is applicable to most RDBMS software.
Good luck

 

[Chintsapete]

Thank jdraw that is the best tutorial I've seen using FK's, it shed a lot of light into the dark corners of my brain about it. Your little sample DB did most of it.
It doesn't seem to help with crashing the DB editing relationships in the relationship window though, once there are more then 5 tables in there. Anyway not sure if that issue has been fixed in newer versions of access, but can't afford to go there to check it out.
Thank a lot for your help appreciate it.

 

[Chintsapete]

I found this post regarding my crashing problem, I couldn't believe it at first but sure as anything it works. I hope it saves someone else some time.

http://bytes.com/topic/access/answers/894613-relationship-diagram-access-crashes-when-tables-added

I found the solution. I had this problems as well. I got a new laptop with new operating system, new Office installation and it happened again. I couldn't believe it, but then I realized the only link between the two was my Quick Access toolbar. I had brought the access.qat over from the previous machine. When I deleted the access.qat my relationships worked fine.

I haven't tried to rebuild the .qat to find the culprit, but I will rebuild that very slowly checking out the function of relationships along the way.

 

[spikepl]

Thank you for posting the solution