I am trying to perform multiple table updates by using VB to run the SQL commands. Right now I have this code:
Dim conDatabase As ADODB.Connection
Dim strSQL As String
Dim fieldsArray(4)
fieldsArray(0) = "code1name"
fieldsArray(1) = "code2name"
fieldsArray(2) = "code3name"
fieldsArray(3) = "code4name"
Set conDatabase = CurrentProject.Connection
i = 0
Do While i < 4
strSQL = "INSERT INTO '" + fieldsArray(i) + "' ( name ) SELECT DISTINCT temp_cstat_data_os." + fieldsArray(i) + " FROM temp_cstat_data_os WHERE temp_cstat_data_os." + fieldsArray(i) + " NOT IN (SELECT " + fieldsArray(i) + ".name FROM " + fieldsArray(i) + ")"
conDatabase.Execute strSQL
i = i + 1
Loop
MsgBox "Done!"
conDatabase.Close
This works great if all the tables are empty and you run it once. But once the tables start having data in them and you run this it will only add SOME of the data and leave the rest in the table. As far as I can tell the code is ok. I have two of these querys setup. One that takes code1name - code4name from temp_cstat_data and then this one which does the same from temp_cstat_data_os. Any idea why on earth it is skipping some of the data? Here is some examples of the data it isn't adding to the reference table (code1name - code4name).
"slow connection", "box swap" and "Z_OLD06_2004_Cancel -x" without the "".
Question
Guest FiZi2
I am trying to perform multiple table updates by using VB to run the SQL commands. Right now I have this code:
This works great if all the tables are empty and you run it once. But once the tables start having data in them and you run this it will only add SOME of the data and leave the rest in the table. As far as I can tell the code is ok. I have two of these querys setup. One that takes code1name - code4name from temp_cstat_data and then this one which does the same from temp_cstat_data_os. Any idea why on earth it is skipping some of the data? Here is some examples of the data it isn't adding to the reference table (code1name - code4name).
"slow connection", "box swap" and "Z_OLD06_2004_Cancel -x" without the "".
Link to comment
Share on other sites
0 answers to this question
Recommended Posts