I run three insert queries to populate the tables in my db - code below. If I run through line by line using F8 then no problem, but if I let the code go at full speed sometimes (most of the time) the final statement fails. I reckon this is because of the relationships and trying to execute the statement before commitment is complete.
How should I correct myself on this? (I can get it working by using sleep(500) after the initial insert, but what if this wasn't long enough for some reason?)


SQL = "INSERT INTO tbl_GroupType ([GroupType]) VALUES ('" & GT & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_GroupArea ([GroupArea]) VALUES ('" & GA & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_AssignedGroup ([GroupName],[Type],[Area]) VALUES ('" & GN & "','" & GT & "','" & GA & "');"
CurrentDb.Execute SQL

I run three insert queries to populate the tables in my db - code below. If I run through line by line using F8 then no problem, but if I let the code go at full speed sometimes (most of the time) the final statement fails. I reckon this is because of the relationships and trying to execute the statement before commitment is complete.
How should I correct myself on this? (I can get it working by using sleep(500) after the initial insert, but what if this wasn't long enough for some reason?)


SQL = "INSERT INTO tbl_GroupType ([GroupType]) VALUES ('" & GT & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_GroupArea ([GroupArea]) VALUES ('" & GA & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_AssignedGroup ([GroupName],[Type],[Area]) VALUES ('" & GN & "','" & GT & "','" & GA & "');"
CurrentDb.Execute SQL


I use the DBEngine object's Idle method with dbRefreshCache to force Microsoft Jet to refresh its cache between queries.

Try:

SQL = "INSERT INTO tbl_GroupType ([GroupType]) VALUES ('" & GT & "');"
CurrentDb.Execute SQL

DBEngine.Idle dbRefreshCache


SQL = "INSERT INTO tbl_GroupArea ([GroupArea]) VALUES ('" & GA & "');"
CurrentDb.Execute SQL

DBEngine.Idle dbRefreshCache

SQL = "INSERT INTO tbl_AssignedGroup ([GroupName],[Type],[Area]) VALUES ('" & GN & "','" & GT & "','" & GA & "');"
CurrentDb.Execute SQL

DBEngine.Idle dbRefreshCache

Thanks for the reply, but no better unsing that cache refresh thingy. Also, the sleep(500) aint working either now making me believe this is more involved than first thought.

I think I've sussed this but still need help, if anyone can. I think its to do with a space between the user entered fields. The GT variable could contain "Change Management", in this case the first SQL works but the third doesn't. Can anyone see why??


SQL = "INSERT INTO tbl_GroupType ([GroupType]) VALUES ('" & GT & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_GroupArea ([GroupArea]) VALUES ('" & GA & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_AssignedGroup ([GroupName],[Type],[Area]) VALUES ('" & GN & "','" & GT & "','" & GA & "');"
CurrentDb.Execute SQL

I think I've sussed this but still need help, if anyone can. I think its to do with a space between the user entered fields. The GT variable could contain "Change Management", in this case the first SQL works but the third doesn't. Can anyone see why??


SQL = "INSERT INTO tbl_GroupType ([GroupType]) VALUES ('" & GT & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_GroupArea ([GroupArea]) VALUES ('" & GA & "');"
CurrentDb.Execute SQL
SQL = "INSERT INTO tbl_AssignedGroup ([GroupName],[Type],[Area]) VALUES ('" & GN & "','" & GT & "','" & GA & "');"
CurrentDb.Execute SQL


What is the generated SQL statement that is getting the error (the value in the variable SQL)?