Hi,

If I try to select the data from a query using ADO, as well by using Recordset as command object, it always returns an empty recordset.

What I'm I doing wrong, or can I only select data by using a sql statement on a table instead of calling a query?


Best regards,
Mike

What code are you using and how are you determining that there are no records?

In general, a SELECT query and a table look the same to the Access GUI - they both present a grid that looks like a spreadsheet. I.e. a recordset. At base level, most things cannot tell whether they are seeing a query or a table. So ...

can I only select data by using a sql statement on a table instead of calling a query?

You are not restricted in the manner as you asked. You should be free to query a table or query another query. You do not have to directly open a table to return records.

Therefore, your problem is either the WHERE clause or the table under the query is empty. Which you say it isn't. So check your WHERE clause. Or post the query here so we can pick it apart for you.

This code works if I use "SELECT * FROM table"

I have tried using recordset:

Dim SQL As String
SQL = "SELECT * FROM query"

Dim RS As New ADODB.Recordset
Call RS.Open(SQL, CurrentProject.Connection)

And tried using Command:(also tried with a parameter query, therefore still the parameter lines in between

Dim CMD As New ADODB.Command

With CMD
.CommandText = SQL
.CommandType = adCmdTable
'.Parameters.Append .CreateParameter("Trainer", adVarChar, adParamInput, 100)
'.Parameters("Trainer").Value = strValue
.ActiveConnection = CurrentProject.Connection
End With

Dim RS As New ADODB.Recordset
Set RS = CMD.Execute

test for empty recordset:

If RS.EOF = True Then
MsgBox "Lege recordset"
Exit Sub
Else
Dim arRecord As Variant
With RS
arRecord = .GetRows

Dim x As Integer, strRecord As String
For x = 0 To UBound(arRecord)
strRecord = strRecord & Chr(35) & Chr(160) & UCase(.Fields(x).Name) & vbCr
strRecord = strRecord & arRecord(x, 0) & vbCr & vbCr
Next x
Me.txtTest.Value = strRecord
End With
End If

You're specifying adCmdTable, but the CommandType you're actually using here, is adCmdText. What you could be using, is just using the name of the query as CommandText, and adCmdStoredProc as CommandType.

How does the SQL look? Are you per chance using wildcard? If so, remeber that ADO wildcards are % and _ (percent and underscore) not * and ?.

I recommend doing explicit instantiation, i e

dim rs as adodb.recordset
set rs = new adodb.recordset

over your implicit instantiation.

then just

rs.open "queryname", currentproject.connection, <cursortype>, <locktype>,adcmdstoredproc

Also, why are you stuffing the recordset into an array? Why not just use the recordset?

Roy,

Thanks for the answer, sorry for the late reply.

The problem is solved, the array was something I tried to fill a label on a form in an excel worksheet some time ago.

Thanks
Mike