Problems with removing relationship

I am running into a strange problem with Access 2007 table relationships.

Earlier, I set up a relationship between our Customer table and a state
table (state abbreviation and state name). Recently I learned that in some
cases the state is not known when a Customer is added to the system. If
state is left blank, we get the following error, as expected (because of the
relationship).

The Microsoft Access database engine cannot find a record in the table
name with key matching field(s) name. (Error 3101)

I then removed the relationship between the Customer table and the State
table. I thought that this would eliminate the 3101 error. Unfortunately,
we are still getting this error.

I am confused and could use some advice.

I have looked for “hidden� objects and have found none. I have done a
“Compact and Repair� and we still get the error.

Thanks in advance for your help.

Brad

On May 22, 12:41*pm, Brad wrote:
I am running into a strange problem with Access 2007 table relationships.

Earlier, I set up a relationship between our Customer table and a state
table (state abbreviation and state name). *Recently I learned that in some
cases the state is not known when a Customer is added to the system. *If
state is left blank, we get the following error, as expected (because of the
relationship).

The Microsoft Access database engine cannot find a record in the table
name with key matching field(s) name. (Error 3101)

I then removed the relationship between the Customer table and the State
table. *I thought that this would eliminate the 3101 error. *Unfortunately,
we are still getting this error.

I am confused and could use some advice.

I have looked for “hidden” objects and have found none. *I have done a
“Compact and Repair” and we still get the error.

Thanks in advance for your help.

Brad

Compact the database. then it will properly discard the
relationship. Otherwise, it just hangs around for some reason.

On Fri, 22 May 2009 10:41:00 -0700, Brad
wrote:

I then removed the relationship between the Customer table and the State
table. I thought that this would eliminate the 3101 error. Unfortunately,
we are still getting this error.

How did you remove the relationship? Deleting the table icon in the
relationships window will NOT do so; you need to have the relationship
visible, select the join line, and delete THAT.
--

John W. Vinson [MVP]

hi, John. i'm feeling pretty stupid for not understanding this, but i have
to ask anyway: why would the op get an error because of the relationship.
tblStates has a one-to-many relationship with tblCustomers, correct? as in,
one state may have many customers, but each customer is in only one state.
so if you enter a record in tblCustomers, why would you expect to get an
error if you don't enter a state in that record? i'd only expect a
relationship-based error if you tried to enter a state that wasn't listed in
tblStates. please tell me what i'm not seeing, otherwise it's going to bug
me to death! tia, tina


"John W. Vinson" wrote in message
...
On Fri, 22 May 2009 10:41:00 -0700, Brad
wrote:

I then removed the relationship between the Customer table and the State
table. I thought that this would eliminate the 3101 error.
Unfortunately,
we are still getting this error.

How did you remove the relationship? Deleting the table icon in the
relationships window will NOT do so; you need to have the relationship
visible, select the join line, and delete THAT.
--

John W. Vinson [MVP]

John,

Thanks for your help.

Here is how I deleted the relationship...
- Went into "relationships window"
- Selected the Join Line and deleted it

I then exited the relationship window and went back in again to double check
on the delete. The Join Line was not shown, so I was pretty sure that I had
indeed nuked the relationship.

Because I am fairly new to Access 2007, I am not sure if I am doing
something stupid, or if I am running into some sort of Access bug.

Thanks again for your assistance. I really appreciate it!
Brad



"John W. Vinson" wrote:

On Fri, 22 May 2009 10:41:00 -0700, Brad
wrote:

I then removed the relationship between the Customer table and the State
table. I thought that this would eliminate the 3101 error. Unfortunately,
we are still getting this error.

How did you remove the relationship? Deleting the table icon in the
relationships window will NOT do so; you need to have the relationship
visible, select the join line, and delete THAT.
--

John W. Vinson [MVP]