Delete Queries Won't Work!!!

[Only1Abhi]

Hi ppl.
Simple Problem.
I have an archive system in my DB.
However, the Delete query is NOT working and it shows EXTRA fields in the "Datasheet View".

Where have these extra fields come from?
How can I get rid of these?
How can I get the Delete queries to work properly?

There is also a problem on the append queries that says:
"Microsoft Access can't append all the records in the query".

Many thanks to every1 in advance.
Best Regards,
Abhi
)

 

[Rob.Mills]

I can see where it's having challenges but explain to me how you're determining which records to append and delete. First off it's probably best to only contain the table you're deleting from in the delete query. Not necessary but simpler. If there's a way to filter, and there should be, then it will work if you've only got that table in it.

Another suggestion: Remove the relationship between tblTransaction and tblCustomer. You don't even need a field in tblTransaction to relate back to the tblCustomer because you've got tblCustomerContact in between them. That might be causing the append problems but don't hold me to that.

 

[Rob.Mills]

Also, remove referential integrity from all your relationships. It's just going to complicate this whole process.

 

[Rob.Mills]

Guess you disagreed with me Pat.

Well I certainly won't argue with you. But out of curiousity, why do you insist on not removing it? I've setup plenty of databases without it and haven't had a problem that I know of.

 

[Rob.Mills]

I see your point. And now that I think about it I can see how using cascade delete would eliminate the need to run a delete query for the many side table.

Point in fact I have seen orphan records in my databases. And now that I've read your post I understand that RI would prevent that.

Thanks for the explanation.

 

[Rob.Mills]

Well good. I always like to make people feel good.

Just curious, how long have you been programming... Access and in general?

I noticed that you're probably the most senior developer on this forum.

I've been working on Access for a couple years now. I must say I've done most of my learning since I've been a member of this site.

 

[Only1Abhi]

lol all this effort to write by both of u!

I have solved my problem by just testing out the different possible relationships.

LOL!!!

Thanks anyway to both!

Best Regards,
Abhi
:0)

 

[The_Doc_Man]

Just a side note from an old hardware twiddler, Pat.

In 1968, an IBM 360/50 COULDN'T relocate. The hardware to support that feature didn't come into being until the 370 series came out. IBM 360/50 was ALWAYS physical address. Virtual and segment-relative addressing techniques didn't come about until a bit later.

 

[Rob.Mills]

Pat,

Need a little help. I applied RI to a database I've had. Most of the users like to go in on the main form and enter info in the subform before they type in the information in the main form. Now they're always getting the message that a related record is required. And then basically gets stuck in that until they shut the database down. How could I solve this. I've tried telling them to enter the main data first but it seems that someone's always going to ignore and then I have to go over to their desk to fix it.

 

[Jacob Mathai]

Pat, I really enjoyed reading about your background. I agree with you that ACCESS provides more job satisfaction than any other systems I have used in the past. This forum has helped me a lot in my ACCESS education.

Thanks to all who ask the questions and those who reply.

 

[rpadams]

Reply Rob.Mills input order

I never used to apply RI, but I have in the last year and it has made life a lot easier. To see how useful it is, set it up on a system that has been running a few years without it and even if it has had the most careful input you can imagine, you will still have to remove many orphan records before Access will accept the RI being added. I now add it right from the get-go on all my applications.

If you use A2K, you can even make ad-hoc subdatasheet queries and they all work effortlessly if the RI is set up properly beforehand.

To solve the main-form, sub form data entry, I generally make the subform invisible until some piece of required data is entered into the main form even if it is an autonumber. Since the user can't "see" the subform, he can't put any data in out of order. This is really good practice anyway if you are adding one to many relationships. For example, an Order Number is entered into the main form and then used automatically for each entry in an Order Detail subform. I don't see why a user would want to do it in a backwards order.

While you guys are all describing your backgrounds, you're old timers compared to me. But how many of you can say that you were in the same room at a teletype machine at Griffiss Air Force Base working with the ORIGINAL Arpanet switch accessing the true progenitor of the internet?