VBA prompts to insert record into new table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Table (Table1) with 5 fields (Field1), (Field2), (Field3), (Field4)
& (Field1) with data type NUMBER Field size DOUBLE. Every table has records
15 records I have a query and below is the SQL. This query creates
combinations of all 15 numbers in Field1 of Table1 and puts it into a table
with a name (res).

I want to do the similar task for all the remaining four fileds and hence
wrote a VBA (pasted the VBA below the SQL). The VBA creates five different
tables names (1, 2, 3 ,4 and 5). The problem with this VBA is, it prompts me
to click on YES to insert all data into a new table. Can the VBA be modified
for not getting a prompt and do the task without user intervention?

Note: The SQL and VBA will not be aligned as I have observed that the forum
puts line breaks/feeds

SELECT Table1.Field1, Table1_1.Field1, Table1_2.Field1, Table1_3.Field1,
Table1_4.Field1, Table1_5.Field1, Table1_6.Field1, Table1_7.Field1,
Table1_8.Field1, Table1_9.Field1, [Table1_9].[Field1]-[Table1].[Field1] AS
Diff INTO res
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3,
Table1 AS Table1_4, Table1 AS Table1_5, Table1 AS Table1_6, Table1 AS
Table1_7, Table1 AS Table1_8, Table1 AS Table1_9
WHERE (((Table1_1.Field1)>[Table1].[Field1]) AND
((Table1_2.Field1)>[Table1].[Field1] And
(Table1_2.Field1)>[Table1_1].[Field1]) AND
((Table1_3.Field1)>[Table1].[Field1] And
(Table1_3.Field1)>[Table1_1].[Field1] And
(Table1_3.Field1)>[Table1_2].[Field1]) AND
((Table1_4.Field1)>[Table1].[Field1] And
(Table1_4.Field1)>[Table1_1].[Field1] And
(Table1_4.Field1)>[Table1_2].[Field1] And
(Table1_4.Field1)>[Table1_3].[Field1]) AND
((Table1_5.Field1)>[Table1].[Field1] And
(Table1_5.Field1)>[Table1_1].[Field1] And
(Table1_5.Field1)>[Table1_2].[Field1] And
(Table1_5.Field1)>[Table1_3].[Field1] And
(Table1_5.Field1)>[Table1_4].[Field1]) AND
((Table1_6.Field1)>[Table1].[Field1] And
(Table1_6.Field1)>[Table1_1].[Field1] And
(Table1_6.Field1)>[Table1_2].[Field1] And
(Table1_6.Field1)>[Table1_3].[Field1] And
(Table1_6.Field1)>[Table1_4].[Field1] And
(Table1_6.Field1)>[Table1_5].[Field1]) AND
((Table1_7.Field1)>[Table1].[Field1] And
(Table1_7.Field1)>[Table1_1].[Field1] And
(Table1_7.Field1)>[Table1_2].[Field1] And
(Table1_7.Field1)>[Table1_3].[Field1] And
(Table1_7.Field1)>[Table1_4].[Field1] And
(Table1_7.Field1)>[Table1_5].[Field1] And
(Table1_7.Field1)>[Table1_6].[Field1]) AND
((Table1_8.Field1)>[Table1].[Field1] And
(Table1_8.Field1)>[Table1_1].[Field1] And
(Table1_8.Field1)>[Table1_2].[Field1] And
(Table1_8.Field1)>[Table1_3].[Field1] And
(Table1_8.Field1)>[Table1_4].[Field1] And
(Table1_8.Field1)>[Table1_5].[Field1] And
(Table1_8.Field1)>[Table1_6].[Field1] And
(Table1_8.Field1)>[Table1_7].[Field1]) AND
((Table1_9.Field1)>[Table1].[Field1] And
(Table1_9.Field1)>[Table1_1].[Field1] And
(Table1_9.Field1)>[Table1_2].[Field1] And
(Table1_9.Field1)>[Table1_3].[Field1] And
(Table1_9.Field1)>[Table1_4].[Field1] And
(Table1_9.Field1)>[Table1_5].[Field1] And
(Table1_9.Field1)>[Table1_6].[Field1] And
(Table1_9.Field1)>[Table1_7].[Field1] And
(Table1_9.Field1)>[Table1_8].[Field1]) AND
(([Table1].[Field1]+[Table1_1].[Field1]+[Table1_2].[Field1]+[Table1_3].[Field1]+[Table1_4].[Field1]+[Table1_5].[Field1]+[Table1_6].[Field1]+[Table1_7].[Field1]+[Table1_8].[Field1]+[Table1_9].[Field1])=413));

Sub maxi()
Dim i As Integer
Dim strSQL As String
For i = 1 To 5
strSQL = "SELECT Table1.Field" & i & ", Table1_1.Field" & i & ",
Table1_2.Field" & i & ", Table1_3.Field" & i & ", Table1_4.Field" & i & ",
Table1_5.Field" & i & ", Table1_6.Field" & i & _
", Table1_7.Field" & i & ", Table1_8.Field" & i & ", Table1_9.Field" & i &
", [Table1_9].[Field" & i & "]-[Table1].[Field" & i & "] AS Diff INTO " & i &
_
" FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3,
Table1 AS Table1_4, Table1 AS Table1_5, Table1 AS Table1_6, Table1 AS
Table1_7, Table1 AS Table1_8, Table1 AS Table1_9" & _
" WHERE (((Table1_1.Field" & i & ")>[Table1].[Field" & i & "]) AND
((Table1_2.Field" & i & ")>[Table1].[Field" & i & "] And (Table1_2.Field" & i
& ")>[Table1_1].[Field" & i & "]) AND ((Table1_3.Field" & _
i & ")>[Table1].[Field" & i & "] And (Table1_3.Field" & i &
")>[Table1_1].[Field" & i & "] And (Table1_3.Field" & i &
")>[Table1_2].[Field" & i & "]) AND ((Table1_4.Field" & i &
")>[Table1].[Field" & _
i & "] And (Table1_4.Field" & i & ")>[Table1_1].[Field" & i & "] And
(Table1_4.Field" & i & ")>[Table1_2].[Field" & i & "] And (Table1_4.Field" &
i & ")>[Table1_3].[Field" & i & "]) AND ((Table1_5.Field" & _
i & ")>[Table1].[Field" & i & "] And (Table1_5.Field" & i &
")>[Table1_1].[Field" & i & "] And (Table1_5.Field" & i &
")>[Table1_2].[Field" & i & "] And (Table1_5.Field" & i &
")>[Table1_3].[Field" & _
i & "] And (Table1_5.Field" & i & ")>[Table1_4].[Field" & i & "]) AND
((Table1_6.Field" & i & ")>[Table1].[Field" & i & "] And (Table1_6.Field" & i
& ")>[Table1_1].[Field" & i & "] And (Table1_6.Field" & _
i & ")>[Table1_2].[Field" & i & "] And (Table1_6.Field" & i &
")>[Table1_3].[Field" & i & "] And (Table1_6.Field" & i &
")>[Table1_4].[Field" & i & "] And (Table1_6.Field" & i &
")>[Table1_5].[Field" & _
i & "]) AND ((Table1_7.Field" & i & ")>[Table1].[Field" & i & "] And
(Table1_7.Field" & i & ")>[Table1_1].[Field" & i & "] And (Table1_7.Field" &
i & ")>[Table1_2].[Field" & i & "] And (Table1_7.Field" & _
i & ")>[Table1_3].[Field" & i & "] And (Table1_7.Field" & i &
")>[Table1_4].[Field" & i & "] And (Table1_7.Field" & i &
")>[Table1_5].[Field" & i & "] And (Table1_7.Field" & i &
")>[Table1_6].[Field" & _
i & "]) AND ((Table1_8.Field" & i & ")>[Table1].[Field" & i & "] And
(Table1_8.Field" & i & ")>[Table1_1].[Field" & i & "] And (Table1_8.Field" &
i & ")>[Table1_2].[Field" & i & "] And (Table1_8.Field" & _
i & ")>[Table1_3].[Field" & i & "] And (Table1_8.Field" & i &
")>[Table1_4].[Field" & i & "] And (Table1_8.Field" & i &
")>[Table1_5].[Field" & i & "] And (Table1_8.Field" & i &
")>[Table1_6].[Field" & _
i & "] And (Table1_8.Field" & i & ")>[Table1_7].[Field" & i & "]) AND
((Table1_9.Field" & i & ")>[Table1].[Field" & i & "] And (Table1_9.Field" & i
& ")>[Table1_1].[Field" & i & "] And (Table1_9.Field" & _
i & ")>[Table1_2].[Field" & i & "] And (Table1_9.Field" & i &
")>[Table1_3].[Field" & i & "] And (Table1_9.Field" & i &
")>[Table1_4].[Field" & i & "] And (Table1_9.Field" & i &
")>[Table1_5].[Field" & _
i & "] And (Table1_9.Field" & i & ")>[Table1_6].[Field" & i & "] And
(Table1_9.Field" & i & ")>[Table1_7].[Field" & i & "] And (Table1_9.Field" &
i & ")>[Table1_8].[Field" & i & "]) AND (([Table" & _
"1].[Field" & i & "]+[Table1_1].[Field" & i & "]+[Table1_2].[Field" & i &
"]+[Table1_3].[Field" & i & "]+[Table1_4].[Field" & i & "]+[Table1_5].[Field"
& i & "]+[Table1_6].[Field" & i & "]+[Table" & _
"1_7].[Field" & i & "]+[Table1_8].[Field" & i & "]+[Table1_9].[Field" & i &
"])=413));"
DoCmd.RunSQL (strSQL)
Next i
End Sub
 
Use
DBEngine(0).Workspaces(0).Execute
instead of
DoCmd.RunSQL
and you won't get the warnings.
 
If you're using DoCmd.RunSQL, enclose within
DoCmd.SetWarnings(False)/DoCmd.SetWarnings(True). The only caveat(sp)
though is that if the query craps out you won't be advised that it
failed. If you're appending a batch, you may find that you start to
drive yourself crazy trying to determine wether or not the append FULLY
succeeeded(sp) or if it failed, which records were appended and which
one's weren't. Because of this, I personally will be moving toward using
record-level procesing via DAO since the method will allow me to
immediately know which records had problems.

David H
Come on baby light my fire: www.spreadFireFox.com
 
Back
Top