Multiple Insert Queries - Some Don't Work (1 Viewer)

[A. Turtle]

I have an empty database, copies of which will be used in a number of locations. Before a copy is sent out to the user some data, specific to the user's location, needs to be entered into a total of 40+ tables . I have tried to do this using a series of Update and Insert queries in VBA, but find that not all of the changes have been successful.
I have checked code, field and table names and eliminated typos, misnamed fields and all of the other common mistakes.
It seems as if a block of several tables is missed every so often through the list, as if Access has had trouble doing the writes quickly enough and skipped some.
I have looked for some method of forcing each query to complete its writes before moving on to the next one, but have found nothing so far.
The database is self-contained and will be run on a PC, not on a server.
What I am looking for I guess is the Access equivalent to the Commit command used in other systems.

Will someone please tell me, is there anything like this In Access 2000?

 

[KenHigg]

DoEvents ?

Also, have you (re) enabled warnings so that access will prompt you and maybe you can get some kind of idea as to why access is not executing the queries?

ken

 

[Dennisk]

I would set error trapping then single step through the code to identify the error. but I can assure Access does not skip lines unless forced to by a decision statement (inc on error).

there is a commit statement that is used with a transaction begin statement.

 

[A. Turtle]

Hi Ken, thanks for the reply.
Warnings are now off, and a Debug.Print with incremental number has been added after each query so that I can see where the warnings occur.
One of the errors is caused by trying to change the value in a PK which it claims is part of relationship. That's odd, because there are no relationships. There were, but these were removed as I knew that they would interfere with automating this part of the process. The plan was to recreate them next.
Other errors relate to indexes, again there are none.
Is something seeing things as they were, despite the fact that all changes made to PK, indexes etc have been saved? Or have I missed something when saving changes? Looking through the tables everything, with the exception of the one field that claims to be part of a relationship, is as it should be.

Dennis,
I think the Debug.Print, and watching warnings has shown where problems are. However, as stated above, I'm at a loss when it comes to understanding why. With the exception of the PK one of course, but why that says it's still part of a relationship puzzles me.

 

[A. Turtle]

I may have found the reason for the PK one. That key is used as part of the Join statement in another query. Would this constitute a relationship?
If so then I will have to find a way round that.

 

[Guus2005]

Is your database normalised? Since 40+ tables is a lot for user location data.
Are you following a certain sequence when filling the database? Since reference tables must be filled first.
Have you tried to copy all object to a new database? Since that will get rid of most relationships. Default indexes on PK's will be rebuild. Userdefined indexes not.
Do you have FE/BE databases?

 

[A. Turtle]

Hi Guus, yes database is normalised. The only piece of data that is repeated at this stage is an ID code which identifies where the data came from. At a later stage the data thus collected will be imported into the main database, but at this stage the users do not have access to that and can not input directly. Also, for the purposes of ensuring that everything is as required for import, this way allows for checking before anything is committed. For some unknown reason there is a suspicion that users don't always do things the way they should.
DB is not FE/BE.
I will try the copy that you suggested and see how that goes. May be tomorrow before I get chance to post again as it's now close to 5:00pm here and I don't intend having another late night tonight - too many 10:00pm finishes recently.

 

[Rabbie]

I may have found the reason for the PK one. That key is used as part of the Join statement in another query. Would this constitute a relationship?
If so then I will have to find a way round that.

A Join in SQL is how a relationship is defined.