Trouble on execute queries in VBA

[Merten Baiser]

Hi,

I want execute queries created in Access with VBA. I use following
syntax:
(adodb.)recordset.Open = "[<Abfrage>]" bzw.
(adodb.)recordset.Open = "SELECT something FROM [<Abfrage>] GROUP BY
something"

Some results are empty (BOF and EOF are true), although there are items
in the database and the same SQL-statement returns a valid result at
the Query-View of Access. This happens only on some queries. The most
queries works fine.

Have somebody an idea?

Merten


The structure of the program:

Function Main()

[...]

Dim query As ADOX.View
Dim queries As ADOX.Views
Dim connection As New ADODB.connection
Dim provider, dataSource As String

'Properties necessary to connect to the database
provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
dataSource = "Data Source=xyz.mdb;"

'Connection to the database
connection.Mode = adModeRead
connection.Open provider & dataSource

[...]

'Get all queries available
Set queries = GetQueries(connection)

[...]
RunQuery queries(IndexQuery), connection
[...]

'Close connection
connection.Close

End Function

-------------------------------------

'Get all queries available in the database
Public Function GetQueries(connection As ADODB.connection) As
ADOX.Views

Dim database As New ADOX.Catalog

database.ActiveConnection = connection

Set GetQueries = database.Views

End Function

-------------------------------------

Function RunQuery (query As ADOX.View, connection As ADODB.connection)

Dim rs As New ADODB.Recordset 'Contains the
result of a query
Dim rsCPU As New ADODB.Recordset 'Contains the
CPUs of a query

'Set connection to database
rs.ActiveConnection = connection
rsCPU.ActiveConnection = connection

'Get all CPUs of a query
rsCPU.Open "SELECT CPU FROM [" & query.Name & "] GROUP BY CPU;",
connection

If (rsCPU.BOF And rsCPU.EOF) Then
MsgBox "RunQuery(): Result of SELECT CPU FROM [" & query.Name & "]
GROUP BY CPU; is empty"
rsCPU.Close
Exit Function
End If

rsCPU.MoveFirst
Do While Not rsCPU.EOF
'Get the result of a query for a certain CPU
rs.Open "SELECT * FROM [" & query.Name & "] WHERE CPU='" &
rsCPU.Fields(0) & "';"
[...]
rs.Close

rsCPU.MoveNext
Loop

rsCPU.Close

End Function