Microsoft Jet Engine Database stopped the ... Totally Stumped

I keep getting the following message when I run the attached VB program (unzipped vbp and Access 2000 database will run if extracted to the same folder).

The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Here is the query that loads the main form...

adodcCR.RecordSource = "SELECT CR.CHANGE_ID, CR.REQUESTOR_ID, CR.CHANGE_TITLE, CR.STATUS_TYPE, CR.ASSIGNED_ID, CR.PRIORITY, CR.ENTITY_NAME, CR.ATTRIBUTE_NAME, CR.DS_COUNCIL, CR.TYPE_OF_FIX_ID, [tblDATES].[SUBMITTED_DATE], CR.RELEASE_ID, CR.CHANGE_DESCRIPTION, CR.BENEFIT_PURPOSE, CR.PROGRESS_NOTES, [tblDATES].[DATE_PHASE], CR.CROSS_REF, CR.PROBLEM_LOG_NUM, CR.BUS_UNIT_ID, CR.EXP_CLOSE_DATE, CR.TRANSRULE_DB01, CR.TRANSRULE_DB02, [tblDATES].[START_DATE], [tblDATES].[IMPLEMENTED_DATE], [tblDATES].[CANCEL_DATE], [tblDATES].[INACTIVE_DATE] FROM tblCHANGE_REQUEST AS CR INNER JOIN tblDATES ON CR.CHANGE_ID = [tblDATES].[CHANGE_ID] WHERE CR.PHASE = [tblDATES].[DATE_PHASE] ORDER BY CR.CHANGE_ID;"

When I change something in tblDATES on the form, I have no problem. If it is from CR then I have a problem.

I've tried everything I could find on the internet (making a new copy of the database, renaming, running JETCOMP.EXE, etc...).

Any help would be greatly appreciated.

Regards,

Brian

Hi,

Just tried to run the code. At what moment are you getting the error message?
Another thing, what do you mean by "If it is from CR then I have a problem. "
What is "CR" ?

Shurik.

I get the error when I update a any field on the form other than a date field. So for instance if I change the Status - I get an error when I navigate to another field. Sorry, CR is the alias for tblCHANGE_REQUEST in my SQL that generates my recordset that populates the ADO form.

Thanks.

So you are trying to update fields within a JOIN? This is typically bad practice. What you need to understand is, each table within your JOIN in being evaluated. If updating a field on the CR Table effects the JOIN relationship you will get an error. You are currently JOINING via the ID field. If this ID field is effected in any way, the database is going to complain.

It is always best to update each individual table, instead of trying to update records returned with a JOIN query.

This goes back to what you were saying about not using data controls, right?

Do you have any recs. on a book, example, etc... where I could see an example of this?

Basically I want to use VB ADO to display one form where data can be updated. The data comes from multiple tables that are related.

Any suggestions would really be appreciated.
Regards,
Brian

Well, yes, you should be using ADO instead of ADODC. Go to the knowledge base, look at the thread "Top Ten Tutorials"...within this thread there is a direct link to the "ADO for beginners tutorial". This will get you started.

But this won't help the update issue regarding the JOIN. Again, you should be updating each table individually, not trying to update a table within a JOIN construct.

Thanks. I guess I'm a bit confused though - if I have a table that is joined to another table and I need to give the user to update all of the fields - how do I do it?

You should be updating each TABLE individually then. If you have 10 fields from Table A and 4 fields from Table B, you should conduct an update on the table that needs to be updated (UPDATE TABLEA SET...etc.).

Thanks. The tutorial helped a bit.

I still don't get though how if I have Name and Address from Table A and Info (dropdown) from Table B how I could get them all on the same form that updates.