fe and be problems with deletion and relationships

[Guest]

I have just split a completed database and I have a couple of problems.
I add 4 new tables with relationships for each new user and have to delete
them if a user is deleted.
when i create the relationships they only show in the front end. I remember
reading in a post that relationships are passive and do nothing other than
supply the default JOINs and child Master links. If this is true I would like
to remove all of them and get rid of a head ache. I'm concerned about if they
have any impact on record updates in forms etc. Enforce referencial integrity
is not used and the design is finished. Users won't have access to any tables.
Also when I delete a table after removing the relationships using
DoCmd.DeleteObject it only deletes the link. What is the best method of
deleting backend tables from the frontend. The backend is a normal mdb.
Thank you for any help.

 

[Alex Dybenko]

Hi,
once you have spitted the application - you have to add references in
backend
to delete table in BE - you have to open BE database:
dbengine.opendatabase("c:\my_BE.mdb")

and then delete tables these using tabledefs.delete method

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks the delete worked fine.
The problem i had was with relationships not references. The relationships
for the tables in use when i split the db are in the backend. its the ones I
add when i add a user and create tables for them (in code). What I really
want to know is it safe to get rid of them. Refer to the original posting.
Any help with this would be great thanks again.

 

[Alex Dybenko]

Sorry, yes, I meant relationships.
Well, if you do not use referential integrity - then there is no need to
create relationships

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

['69 Camaro]

Hi.

I add 4 new tables with relationships for each new user and have to delete
them if a user is deleted.

This table organization suggests that your database is not normalized. Is
there a reason why you can't add a new record to each of four existing
tables whenever you have a new user, and then delete these four records when
the user is deleted?

I remember
reading in a post that relationships are passive and do nothing other than
supply the default JOINs and child Master links. If this is true I would
like
to remove all of them and get rid of a head ache.

This is incorrect. Relationships that enforce referential integrity ensure
that orphan records are not allowed in related tables. You do not want
orphan records, because you will "lose data," since the records will exist
in the table, but queries won't be able to select orphan records in a
relation.

I'm concerned about if they
have any impact on record updates in forms etc.

If referential integrity is enforced and an update would create an orphaned
record, then you'll receive an error message and the record to update will
not be changed into an orphaned record.

Enforce referencial integrity
is not used and the design is finished.

Then you are going to have data integrity issues with this database
application.

What is the best method of
deleting backend tables from the frontend.

Alex Dybenko has already given you the syntax for that.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

My original posting was to short to explain fully. I am dealing with a
Cashbook system. By User I mean a completely different set of accounts
consisting of multple bank account and sometimes thousands of transactions.
As for the orphaned records No form updates more than one table. For example
if a cheque is stopped then the disbursements belonging to that transaction
are handled in code. So if there is a problem it will be my coding. I'm
afraid i don't trust Access enough to let the forms do it all.
Your comments have been really helpful Thank you.

 

['69 Camaro]

Hi.

You asked about updates, so I told you the consequences when relationships
aren't enforced. I also mentioned data integrity problems when
relationships aren't enforced. That means you'll be allowed to create a
child record that has no parent in the relationship, even when the form only
updates one table. Queries won't be able to pull up child records unless
there's a matching parent record, resulting in "lost data."

By User I mean a completely different set of accounts
consisting of multple bank account and sometimes thousands of
transactions.

There's no need to store transactions in separate tables unless they have
different table structures. If they have the same structure, then you can
store thousands of records per account in the same table, and you will be
able to differentiate the accounts by adding an "Account" column to the
table to record which account number the transaction applies to.

I'm
afraid i don't trust Access enough to let the forms do it all.

Let the database engine do what it was designed to do, like enforce
referential integrity between related records. Otherwise, you'll have to do
extra coding to try to duplicate this functionality, and your coding can't
make the same guarantees on database record operations that a relational
database engine can, so you risk data integrity.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

I hear you Loud and clear.
I do store multiple Accounts in one set of tables. I create the new set of
records for a completely different identity that has no relation to the
others and will never be compared to them.
As for the rest you are absolutly correct. When I started with Access I had
so much trouble Stopping it doing things I didn't want done in forms I made a
decision to take control as much as possible. I'm a control freak but I can
assure you there will be no children without parents.
Thanks for your time and experience I really do Appreciate it.