Different recordset results from VBA & Access

[Clark Savage Jr]

I am running a bunch of aggregate insert queries to populate a table.
When I run the SQL statement via a saved query, all is well. However,
when I run it as an ADO recordset in VBA, the count is one greater
than the actual number of records. Out of over 15 queries, only two
do this and there does not appear to be anything special about any of
the records that satisfy the criteria.

Any ideas?! (Code below)

Fred

Sample SQL:
INSERT INTO InstallByMarket ( Area, Market, SiteCnt )
SELECT area, "Manhattan", Count(ID) AS CountOfID
FROM Installs WHERE((Clustername = "New York") AND (Marketareaname =
"West NY") AND ((Relos = 0) AND (Sector = 0)))
GROUP BY area, 'Manhattan';

VBA code to automate:
Dim rs As ADODB.Recordset, rsIn As ADODB.Recordset
Dim Cnn As ADODB.Connection
Dim strSQL As String, strCrit As String, strOther As String
Dim Fld As Field
Dim i As Integer

Set rs = New ADODB.Recordset
Set rsIn = New ADODB.Recordset
Set Cnn = CurrentProject.Connection

rs.Open "SELECT * FROM MarketCriteria", Cnn, adOpenStatic
rsIn.Open "DELETE FROM InstallByMarket", Cnn
rsIn.Open "UPDATE Installs SET Market = 0", Cnn

Do Until rs.EOF

strSQL = "INSERT INTO InstallByMarket ( Area, Market, SiteCnt
) " & vbCrLf
strSQL = strSQL & "SELECT area, """ & rs.Fields("Market") &
""", Count(ID) AS CountOfID " & vbCrLf
strSQL = strSQL & "FROM Installs " & vbCrL
strCrit = "WHERE("
'create dynamic criteria here
For i = 3 To rs.Fields.Count - 1
If Not IsNull(rs.Fields(i)) Then
strCrit = strCrit & "(" & rs.Fields(i) & ") AND "
End If
Next i

strOther = "((Relos = 0) AND (Sector = 0)))"
strSQL = strSQL & strCrit & strOther & vbCrLf & "GROUP BY
area, '" & rs.Fields("Market") & "';"

rsIn.Open strSQL, Cnn
Debug.Print strSQL
Debug.Print

rs.MoveNext
Loop

[Allen Browne]

Clark, no suggestions on why this is happening since it should not, but does
it make any difference if the literal value is omitted from the GROUP BY
clause?

INSERT INTO InstallByMarket (Area, 'Manhattan', SiteCnt)
SELECT area, Count( ...
ORDER BY area;

If this does solve the problem, please post back. It would be good to raise
the issue as a bug if you have a reproducable scenario.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Clark Savage Jr" <cl*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

I am running a bunch of aggregate insert queries to populate a table.
When I run the SQL statement via a saved query, all is well. However,
when I run it as an ADO recordset in VBA, the count is one greater
than the actual number of records. Out of over 15 queries, only two
do this and there does not appear to be anything special about any of
the records that satisfy the criteria.

Any ideas?! (Code below)

Fred

Sample SQL:
INSERT INTO InstallByMarket ( Area, Market, SiteCnt )
SELECT area, "Manhattan", Count(ID) AS CountOfID
FROM Installs WHERE((Clustername = "New York") AND (Marketareaname =
"West NY") AND ((Relos = 0) AND (Sector = 0)))
GROUP BY area, 'Manhattan';

VBA code to automate:
Dim rs As ADODB.Recordset, rsIn As ADODB.Recordset
Dim Cnn As ADODB.Connection
Dim strSQL As String, strCrit As String, strOther As String
Dim Fld As Field
Dim i As Integer

Set rs = New ADODB.Recordset
Set rsIn = New ADODB.Recordset
Set Cnn = CurrentProject.Connection

rs.Open "SELECT * FROM MarketCriteria", Cnn, adOpenStatic
rsIn.Open "DELETE FROM InstallByMarket", Cnn
rsIn.Open "UPDATE Installs SET Market = 0", Cnn

Do Until rs.EOF

strSQL = "INSERT INTO InstallByMarket ( Area, Market, SiteCnt
) " & vbCrLf
strSQL = strSQL & "SELECT area, """ & rs.Fields("Market") &
""", Count(ID) AS CountOfID " & vbCrLf
strSQL = strSQL & "FROM Installs " & vbCrL
strCrit = "WHERE("
'create dynamic criteria here
For i = 3 To rs.Fields.Count - 1
If Not IsNull(rs.Fields(i)) Then
strCrit = strCrit & "(" & rs.Fields(i) & ") AND "
End If
Next i

strOther = "((Relos = 0) AND (Sector = 0)))"
strSQL = strSQL & strCrit & strOther & vbCrLf & "GROUP BY
area, '" & rs.Fields("Market") & "';"

rsIn.Open strSQL, Cnn
Debug.Print strSQL
Debug.Print

rs.MoveNext
Loop

[Clark Savage Jr]

I switched to DAO and had to change the wildcards to "*" --now all is well.
Very strange indeed!

"Allen Browne" <al*********@SeeSig.invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...

Clark, no suggestions on why this is happening since it should not, but does
it make any difference if the literal value is omitted from the GROUP BY
clause?

INSERT INTO InstallByMarket (Area, 'Manhattan', SiteCnt)
SELECT area, Count( ...
ORDER BY area;

If this does solve the problem, please post back. It would be good to raise
the issue as a bug if you have a reproducable scenario.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Clark Savage Jr" <cl*********@yahoo.com> wrote in message
news:3c**************************@posting.google.c om...

I am running a bunch of aggregate insert queries to populate a table.
When I run the SQL statement via a saved query, all is well. However,
when I run it as an ADO recordset in VBA, the count is one greater
than the actual number of records. Out of over 15 queries, only two
do this and there does not appear to be anything special about any of
the records that satisfy the criteria.

Any ideas?! (Code below)

Fred

Sample SQL:
INSERT INTO InstallByMarket ( Area, Market, SiteCnt )
SELECT area, "Manhattan", Count(ID) AS CountOfID
FROM Installs WHERE((Clustername = "New York") AND (Marketareaname =
"West NY") AND ((Relos = 0) AND (Sector = 0)))
GROUP BY area, 'Manhattan';

VBA code to automate:
Dim rs As ADODB.Recordset, rsIn As ADODB.Recordset
Dim Cnn As ADODB.Connection
Dim strSQL As String, strCrit As String, strOther As String
Dim Fld As Field
Dim i As Integer

Set rs = New ADODB.Recordset
Set rsIn = New ADODB.Recordset
Set Cnn = CurrentProject.Connection

rs.Open "SELECT * FROM MarketCriteria", Cnn, adOpenStatic
rsIn.Open "DELETE FROM InstallByMarket", Cnn
rsIn.Open "UPDATE Installs SET Market = 0", Cnn

Do Until rs.EOF

strSQL = "INSERT INTO InstallByMarket ( Area, Market, SiteCnt
) " & vbCrLf
strSQL = strSQL & "SELECT area, """ & rs.Fields("Market") &
""", Count(ID) AS CountOfID " & vbCrLf
strSQL = strSQL & "FROM Installs " & vbCrL
strCrit = "WHERE("
'create dynamic criteria here
For i = 3 To rs.Fields.Count - 1
If Not IsNull(rs.Fields(i)) Then
strCrit = strCrit & "(" & rs.Fields(i) & ") AND "
End If
Next i

strOther = "((Relos = 0) AND (Sector = 0)))"
strSQL = strSQL & strCrit & strOther & vbCrLf & "GROUP BY
area, '" & rs.Fields("Market") & "';"

rsIn.Open strSQL, Cnn
Debug.Print strSQL
Debug.Print

rs.MoveNext
Loop