More commitment!

[DAW]

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

 

[HiTechCoach]

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

 

[DAW]

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.

 

[DAW]

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

 

[HiTechCoach]

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)?