Adding records - unpredictable results

[ryetee]

I have three tables

MyForms
MyControls
MyLanguage

There is a 1 to many relationship between MyForms and MyControls
There is a one to many relationship between MyControls and MyLanguage

I have a function that populates these tables.
For each form in the system I store 1 record on the MyForms table.
I then store 1 record for each control in the form on MyControls.
For each control I store N (currently 2) records on MyLanguage.

This all works fine up to a point.
I noticed that after a time records were no longer getting stored on MyControls and MyLanguage.
The only way around this was to delete all records on all files (I have cascade delete so only need to delete MyForms) and the Compat and Repair the database. The compact and repair doesn't work unless I delete the records first.
It seems to go astray when the MyControls table hits 14-1500 records (MyLanguage 28-3000).

I've stepped through the code line by line and the store of the records appears to go through fine.

Any ideas out there?

 

[Uncle Gizmo]

What is The purpose of 'MyLanguage?

 

[ryetee]

What is The purpose of 'MyLanguage?

MyControl holds the default text for the control caption. MyLanguage holds an English alternative and also the description in other languages (only an extra 1 at the moment).

 

[Uncle Gizmo]

I was wondering what is calling your function? Is it possible that it is being called numerous times and adding lots of unnecessary records which is causing your issue.

 

[ryetee]

I was wondering what is calling your function? Is it possible that it is being called numerous times and adding lots of unnecessary records which is causing your issue.

I have a form which has a command button that when clicked will run the function.
The function therefore can only be called the amount of times this button is clicked. Whilst testing the function I have basically stepped through the code line by line and all is as is should be, apart from a few teething issues. It was whilst sorting out the teething problems that I noticed no new records were being added to MyControl and MyLanguage after a while. On further messing around erm, I mean investigation, I noticed as per original post that the problem could be cured by deleting all records repair and compact the database and rerun the function, and that the number of records on MyControl was about 1400 when adding records stopped.

 

[Uncle Gizmo]

You say " I noticed no new records were being added"

From what you have said so far my understanding is that your code examines the construction of your forms and controls, extracts information and stores it in a table. If that's correct then I wouldn't expect there to be any new records unless you add new controls to your forms?

 

[ryetee]

You say " I noticed no new records were being added"

From what you have said so far my understanding is that your code examines the construction of your forms and controls, extracts information and stores it in a table. If that's correct then I wouldn't expect there to be any new records unless you add new controls to your forms?

Yes you're (more or less) right.
The function will be run every time there is a new form or new controls on old forms. So there will be more or less a finite number of records on all 3 tables, and probably will never be anywhere approaching 1400 records on MyControls.

However as I can not explain what happened I can't be sure that it will start 'failing' at a much lower figure in a different environment. Once I can explain the problem I can either ignore it or redesign to over come the problem.

 

[Uncle Gizmo]

You say:- >>>I noticed that after a time records were no longer getting stored on MyControls and MyLanguage<<<

I would suggest you deliberately add new controls and possibly a new form to double-check that this is the case. This will give you a controlled situation where you have total control and total prediction of what records should be added. The results may provide you with a clue as to where the problem is occurring, it may well be within your VBA code.

 

[ryetee]

You say:- >>>I noticed that after a time records were no longer getting stored on MyControls and MyLanguage<<<

I would suggest you deliberately add new controls and possibly a new form to double-check that this is the case. This will give you a controlled situation where you have total control and total prediction of what records should be added. The results may provide you with a clue as to where the problem is occurring, it may well be within your VBA code.

Have done all that. It all works fine until it doesn't!!!! I've added forms - works great. Added controls - ditto. Whilst testing other things, it at some point which I can't define, stopped adding the records to the control table.

 

[DennisOJensen]

1 Question and 1 Suggestion

Question: Can you outline the how the tables are built -- Field Name, Field Type, Key, Foreign Key ... that kind of thing.

Suggestion: It sounds like you do not have an error checking routine wrapped around your inserts that might go a long way in helping trouble shoot the when it stops and thus help find out the why. If you do and this has not helped then as part of the Insert (after inserting) you should query the database and make sure the value is there. Throw an error if you do not find it. Again finding out exactly when this happens may go a long way into figuring out why it happens.

 

[ryetee]

1 Question and 1 Suggestion

Question: Can you outline the how the tables are built -- Field Name, Field Type, Key, Foreign Key ... that kind of thing.

Suggestion: It sounds like you do not have an error checking routine wrapped around your inserts that might go a long way in helping trouble shoot the when it stops and thus help find out the why. If you do and this has not helped then as part of the Insert (after inserting) you should query the database and make sure the value is there. Throw and error if you do not find it. Again finding out exactly when this happens may go a long way into figuring out why it happens.

MyForms
ID
FormName
FormDesc

MyControls
ID
ControlName
ControlDesc
FormID
FormDesc (this is superfluous but added for testing)

MyLanguage
ID
ControlID
Language (2 digit language identifier)
LanguageText

Field names and relationships should be obvious.

As you point out I don't have any error checking routine but I have 'stepped' through the entire function when it works and when it doesn't work. For form insertions I have the following

strSQL = "INSERT INTO FormNames ([FormName],[FormDesc]) "
strSQL = strSQL & "VALUES ('" & frm & "', '" & Forms(frm).Caption & "');"
Set db = CurrentDb
db.Execute strSQL
newRowFormNames = db.OpenRecordset("SELECT @@IDENTITY")(0)
Response = acDataErrAdded
Set db = Nothing

and for controls I have

strSQL = "INSERT INTO ControlNames ([ControlName],[ControlDesc],[FormID],[FormName]) "
strSQL = strSQL & "VALUES ('" & ctl.Name & "', '" & ctl.Caption & "', '" & newRowFormNames & "', '" & frm & "');"
Set db = CurrentDb
db.Execute strSQL
Response = acDataErrAdded
newRowControlNames = db.OpenRecordset("SELECT @@IDENTITY")(0)
Set db = Nothing


For both I check to see if record is there already and if it is I don't try and add it again.

For both inserts the response (= acDataErrAdded) always= 2 whether the insert works or not.

********
Edit 1
********
Just tested with an on error clause and nothing gets trapped.


********
Edit 2
********

OK it appears I wasn't trapping some errors!
I have now changed
db.Execute strSQL
to
db.Execute strSQL, dbFailOnError
and am trapping the the reason why records aren't being added to MyControls and it is weirder than a weird thing!

I'm getting
Error 3201 (You cannot add or change a record because a related record is required in table 'Field Symptom'.)

There is NO relationship between MyControl and Field Symptom and even if there was how come the it fails in the following fashion...
I start off with no records on MyForms, MyControl and MyLanguages. At this stage the database is repaired and compacted.

I run the function and the right records get added to all 3 files.
I delete the records from MyForms. I have cascaded deletes set so the records are deleted from the other 2 tables as well. I run the function and the records get added to the 3 tables as before. All hunky dory. The only difference is that the ID of the 3 files starts at the next number in sequence after the records were deleted. The SAME records are getting added to each table. I then delete all records from MyForms (records are deleted from other 2 tables). I run the function again and it falls over with the above error. Had I not trapped the error then the MyForms table gets updated, MyControl doesn't (due to above error) and MyLanguages doesn't either as there is now a VALID error in that there is no related record on MyControl .

If I deleted records on MyForms and compact and repair database all works again.
A compact and repair at any other time doesn't solve anything.

Any ideas?

********
Edit 3
********

OK it looks like Field Symptoms and MyControls ARE related according to the database documenter and the field they are related on explains EXACTLY why the fails occur where they do.
HOWEVER if I look in relationship on database tools for the back end database I can not see where the relationship has been formed so now I know what the problem is I have no idea how to fix it!

********
Edit 4
********
OK it's now working.
I went into relationships and added the Field Symptom table and a link was made to the MyControl table which was already present linked to some other table. I deleted that link and it all works fine now..

So the 64,000 dollar/pound/yen question is how did the relationship get there in the first place? There was no sight of the Field Symptoms table in the relationship part of database tools until Iadded it in there and it automatically linked to MyControls?????

 

[DennisOJensen]

1) Are you going to give me the 64k -- As to the how I think I have heard of this before and I vaguely remember that this is one of those undocumented helpful features of Access -- I do not recall what causes it but yeah that is how you fix it.

2) The issue with the number not starting over at the beginning but picking up where you left off -- that is because it is an Autonumber (my guess) and you have to actually reset that value in order for it to start over but since I will not use Autonumbers for numerous reasons I do not recall how you do that.

Other than that it sounds like you got your problems solved good to hear.


Note: I have found that anything that promotes "lazy" coding generally is accompanied with at least one if not numerous issues of why it is a bad idea to use it. Autonumbers are such a case and they simply get overused due to their "ease" of implementation

 

[ryetee]

1) Are you going to give me the 64k -- As to the how I think I have heard of this before and I vaguely remember that this is one of those undocumented helpful features of Access -- I do not recall what causes it but yeah that is how you fix it.

2) The issue with the number not starting over at the beginning but picking up where you left off -- that is because it is an Autonumber (my guess) and you have to actually reset that value in order for it to start over but since I will not use Autonumbers for numerous reasons I do not recall how you do that.

Other than that it sounds like you got your problems solved good to hear.


Note: I have found that anything that promotes "lazy" coding generally is accompanied with at least one if not numerous issues of why it is a bad idea to use it. Autonumbers are such a case and they simply get overused due to their "ease" of implementation

1. Only in Vietnamese dong - I owe you 3 dollars but you'll have to come to the canary islands to pick it up! Yes Access seems to have a few features!! This one took me the longest to work out!
2. I've not had any problems with autonumber and understand it always picks the next number unless you compact and repair which seems to clear access's memory as to where it was up to.

I do have another funny caused by using a referenced module which I'm about to post in another (already started) post