No relationships, yet Access insists I delete them...

[Guest]

Several times I have attempted to create relationships between tables, and am studying index types, key fields, etc. I just can't get it to work the way the books says it's supposed to, though. Then, when I try to start all over and delete the relationships that aren't working, Access won't let me toy with indexes or anything else because it insists that I need to delete the relationship first. (Even if there are absolutely NO relationships established!) Also, I get several other error messages that aren't true, such as, "The database engine could not lock table 'Report_Manager' becaust it is already in use by another person or process." Well, no it isn't. No one is using what I'm still trying to build. No other "Process" is invoked

Another problem is that when I try to set the relationship, Access reverses the One:Many attributes

I have a few very thick books on Access and am doing my best to understand the logic (as I have quite successfully designed with Alpha 5 V5 and prior versions, and have a heightened sense of indexes, relationships, lookups, etc) behind linking tables - yet this is like learning Greek

Any advise?

 

[Kevin3NF]

Are you using lookup fields in your tables? A relationship is created
behind the scenes when you use those nasty little buggers...

Also, in the relationship window, have you elected to "show all"?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Several times I have attempted to create relationships between tables, and

am studying index types, key fields, etc. I just can't get it to work the
way the books says it's supposed to, though. Then, when I try to start all
over and delete the relationships that aren't working, Access won't let me
toy with indexes or anything else because it insists that I need to delete
the relationship first. (Even if there are absolutely NO relationships
established!) Also, I get several other error messages that aren't true,
such as, "The database engine could not lock table 'Report_Manager' becaust
it is already in use by another person or process." Well, no it isn't. No
one is using what I'm still trying to build. No other "Process" is invoked.

Another problem is that when I try to set the relationship, Access

reverses the One:Many attributes.

I have a few very thick books on Access and am doing my best to understand

the logic (as I have quite successfully designed with Alpha 5 V5 and prior
versions, and have a heightened sense of indexes, relationships, lookups,
etc) behind linking tables - yet this is like learning Greek.

 

[Van T. Dinh]

Wanda

Tables don't need to have data before you link them. In fact, relationships
should be defined before any data.

It sounds to me that there may be corruptions in your database. Have you
tried Compact & Repair? If Compact and Repair doesn't work, try Tony Toews'
Web page:

http://www.granite.ab.ca/access/corruptmdbs.htm

An idea: create a blank database, import all objects from the existing
database but NOT the relationships and see if the new database works
properly.

Also, John Vinson previously posted "shot-gun" code to delete ALL
relationships in your database. If the above doesn't help, you may like to
search Google for his code.

--
HTH
Van T. Dinh
MVP (Access)

Hi Kevin,
Thank you for responding. I thought I'd get an email message stating

someone had replied to my post, but didn't. That means I'll have to log on
frequently, to see when replies come...and my employer monitors the amount
of time one spends on the internet - so I can't just leave it running.

That said, the answers to your questions are No, I deleted all lookup

fields AND all keys. Yes, I've clicked Show All, which shows nothing after
I've deleted ALL relationships.

Another odd thing that I think I've discovered is that it seems linked

tables must have data before they can be linked. Could this be true?

 

[Kevin3NF]

Are you deleting the relationships in the Back-end, or trying to delete
them from the FE?

Side note: Your email address is not in here anywhere, and this is a very
good thing. You would almost instantly be inundated with spam and viruses
it is was.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Hi Kevin,
Thank you for responding. I thought I'd get an email message stating

someone had replied to my post, but didn't. That means I'll have to log on
frequently, to see when replies come...and my employer monitors the amount
of time one spends on the internet - so I can't just leave it running.

That said, the answers to your questions are No, I deleted all lookup

fields AND all keys. Yes, I've clicked Show All, which shows nothing after
I've deleted ALL relationships.

Another odd thing that I think I've discovered is that it seems linked

tables must have data before they can be linked. Could this be true?

 

[Guest]

Van, Thank you. Maybe I got the error message about "Data in the table 'Report_Manager' violates referential integrity rules." because I had "dummy" data in one table and not the other. If that's the case, how does one put in "bad" data on purpose to see if the correct error message disallows the user the commit the entry

I didn't consider the database to be corrupt, as I have deleted them and started over several times, and still get the error messages. I'll look for Compact/Repair and see if it helps

Meanwhile I've found DDM's website and am hoping I can make progress with their Tips and Tricks (although I don't understand why one would need any tips or tricks to design a simple little database! It should be straight-forward, with straight-forward documentation in the Help.

Thank you again! Wanda

 

[Kevin3NF]

In a nutshell, a split database is in two pieces. One contains the tables,
the other contains everything else and is linked to the first one.
Relationships are supposed to be created in the first (where the tables
are).

If you have everything in one .mdb file, then this does not apply. You
mentioned linked tables in one of your posts which is why I brought it up.


MS can only do so much on a public newsgroup.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

I have no clue about what back end or front end even means...haven't

researched it, yet. (If researching it through various different mediums
will bring several different, conflicting answers, then can you describe for
me what they mean, please?)

It is unfortunate to not be able to correspond, personally! Seems M$ would

protect their message boards as a routine method of operation.

 

[Guest]

Hi Kevin
Okay - so a split table is what creates the front end and the back end - the front end being where the actual data is stored
Thank you

 

[Kevin3NF]

Split database (not table) and the data is stored in tables which are in the
backend (generally on a server)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Hi Kevin,
Okay - so a split table is what creates the front end and the back end -

the front end being where the actual data is stored?

 

[John Vinson]

Hi Kevin,
Okay - so a split table is what creates the front end and the back end - the front end being where the actual data is stored?
Thank you

Nope, you have it backwards.

The "Backend" is a database on a shared drive or server. It contains
the Tables, and only the tables.

The "Frontend" is what's in front of the user - a database on their
own machine, containing the Forms, Reports, Queries and so on, with
the tables in the backend linked to it.