C
Can Of Worms
I ran into a puzzling problem that I haven't been able to figure out. I have
several functions, that when called, iterate through each of 9 tables and
process data from them into 9 other tables as needed, through append or
update queries. I am creating the SQL string via code, to avoid saving off 36
queries that only slightly differ from one to the next.
I have a Variant (SQL_Fields) into which I assign an array of the fields of
interest from the current table being worked with. During the SQL build, I
use a "For Each Field in SQL_Field" to iterate through each applicable field
for the table and add it into the SQL statement appropriately. It works the
first time, but fails the 2nd time. Debugging shows that the SQL_Fields
variable empty? When I hover over the variable, it doesn't have a tooltip at
all. No "Null", "Empty", no nothing. If I pause on the previous iteration, it
shows the beginning of the array variables. Calling it with "? SQL_Fields" in
the immediate window returns an error, Type Mismatch.
Here is my code:
Calling it:
Dim SQL_Fields As Variant
Filling it: (Which works)
SQL_Fields = Array("[DataID]", "[CaseID]", "[AssignedID]",
"[CaseName]", "[CaseStatus]", "[OpenDate]", "[UpdateDate]", "[CaseCold]",
"[ColdDate]", "[CaseType]", "[CaseSubType]", "[CaseSource]", "[Public]",
"[CreateDate]", "[CreateID]", "[EditDate]", "[EditID]")
SQL_Type = "UPDATE (" & SQL_Table_Destination & " "
SQL_Join = "INNER JOIN " & SQL_Table_Origin & " ON " &
SQL_Table_Destination & ".[DataID] = " & SQL_Table_Origin & ".[DataID]) "
SQL_Join = SQL_Join & "INNER JOIN tbl_DB_DataIDs_Existing ON " &
SQL_Table_Destination & ".[DataID] = tbl_DB_DataIDs_Existing.[DataID] SET "
For Each Field In SQL_Fields '(This works fine)
SQL_Body = SQL_Body & SQL_Table_Destination & Dot & Field & " =
[" & SQL_Table_Origin & "]!" & Field & ", "
Next
If Right(SQL_Body, 2) = ", " Then
SQL_Body = Left(SQL_Body, Len(SQL_Body) - 2)
End If
SQLStatement = SQL_Type & SQL_Join & SQL_Body
DoCmd.RunSQL SQLStatement
SQL_Type = "INSERT INTO " & SQL_Table_Destination & " ( "
SQL_Body = ""
SQL_Body2 = " ) SELECT "
For Each Field In SQL_Fields '(Goes through the For Each fine,
but SQL_Field is empty, so no actual field information is added to the next 2
statements)
SQL_Body = SQL_Body & Field & ", "
SQL_Body2 = SQL_Body2 & SQL_Table_Origin & Dot & ", "
Next
If Right(SQL_Body, 2) = ", " Then
SQL_Body = Left(SQL_Body, Len(SQL_Body) - 2)
End If
If Right(SQL_Body2, 2) = ", " Then
SQL_Body = Left(SQL_Body2, Len(SQL_Body2) - 2)
End If
SQL_Join = " From " & SQL_Table_Origin & " INNER JOIN
tbl_DB_DataIDs_NewLocal ON " & SQL_Table_Origin & ".[DataID] =
tbl_DB_DataIDs_NewLocal.DataID"
SQLStatement = SQL_Type & SQL_Body & SQL_Body2 & SQL_Join
DoCmd.RunSQL SQLStatement '(Fails here, since no field names
were passed to the string, SQL_Field doesn't have any information attached)
Any ideas? I figure I am overlooking something.
several functions, that when called, iterate through each of 9 tables and
process data from them into 9 other tables as needed, through append or
update queries. I am creating the SQL string via code, to avoid saving off 36
queries that only slightly differ from one to the next.
I have a Variant (SQL_Fields) into which I assign an array of the fields of
interest from the current table being worked with. During the SQL build, I
use a "For Each Field in SQL_Field" to iterate through each applicable field
for the table and add it into the SQL statement appropriately. It works the
first time, but fails the 2nd time. Debugging shows that the SQL_Fields
variable empty? When I hover over the variable, it doesn't have a tooltip at
all. No "Null", "Empty", no nothing. If I pause on the previous iteration, it
shows the beginning of the array variables. Calling it with "? SQL_Fields" in
the immediate window returns an error, Type Mismatch.
Here is my code:
Calling it:
Dim SQL_Fields As Variant
Filling it: (Which works)
SQL_Fields = Array("[DataID]", "[CaseID]", "[AssignedID]",
"[CaseName]", "[CaseStatus]", "[OpenDate]", "[UpdateDate]", "[CaseCold]",
"[ColdDate]", "[CaseType]", "[CaseSubType]", "[CaseSource]", "[Public]",
"[CreateDate]", "[CreateID]", "[EditDate]", "[EditID]")
SQL_Type = "UPDATE (" & SQL_Table_Destination & " "
SQL_Join = "INNER JOIN " & SQL_Table_Origin & " ON " &
SQL_Table_Destination & ".[DataID] = " & SQL_Table_Origin & ".[DataID]) "
SQL_Join = SQL_Join & "INNER JOIN tbl_DB_DataIDs_Existing ON " &
SQL_Table_Destination & ".[DataID] = tbl_DB_DataIDs_Existing.[DataID] SET "
For Each Field In SQL_Fields '(This works fine)
SQL_Body = SQL_Body & SQL_Table_Destination & Dot & Field & " =
[" & SQL_Table_Origin & "]!" & Field & ", "
Next
If Right(SQL_Body, 2) = ", " Then
SQL_Body = Left(SQL_Body, Len(SQL_Body) - 2)
End If
SQLStatement = SQL_Type & SQL_Join & SQL_Body
DoCmd.RunSQL SQLStatement
SQL_Type = "INSERT INTO " & SQL_Table_Destination & " ( "
SQL_Body = ""
SQL_Body2 = " ) SELECT "
For Each Field In SQL_Fields '(Goes through the For Each fine,
but SQL_Field is empty, so no actual field information is added to the next 2
statements)
SQL_Body = SQL_Body & Field & ", "
SQL_Body2 = SQL_Body2 & SQL_Table_Origin & Dot & ", "
Next
If Right(SQL_Body, 2) = ", " Then
SQL_Body = Left(SQL_Body, Len(SQL_Body) - 2)
End If
If Right(SQL_Body2, 2) = ", " Then
SQL_Body = Left(SQL_Body2, Len(SQL_Body2) - 2)
End If
SQL_Join = " From " & SQL_Table_Origin & " INNER JOIN
tbl_DB_DataIDs_NewLocal ON " & SQL_Table_Origin & ".[DataID] =
tbl_DB_DataIDs_NewLocal.DataID"
SQLStatement = SQL_Type & SQL_Body & SQL_Body2 & SQL_Join
DoCmd.RunSQL SQLStatement '(Fails here, since no field names
were passed to the string, SQL_Field doesn't have any information attached)
Any ideas? I figure I am overlooking something.