CODEWITHSUNDEEP
ms-accessvba
cosmarchy
cosmarchy

Reputation: 686

Why can I run a Query via the query editor but running through vba fails?

I am trying to retrieve records from a table in access using VBA. So far I have this simple function:

Private Function GNCN() As String

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cm As ADODB.Command
Dim strSQL As String
Dim intYD As Integer

Set cn = CurrentProject.Connection
'cn.CursorLocation = adUseClient
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly

intYD = 16

strSQL = "SELECT DCN FROM tblDCD WHERE (DCN like '" & intYD & "*')"
Set rs = cn.Execute(strSQL)

Debug.Print rs.RecordCount

Set rs = Nothing
Set cm = Nothing
Set cn = Nothing

End Function

When I run this, I don't get any records returned.

However if I take the SQL query:

SELECT DCN FROM tblDCD WHERE (DCN like '16*')

and run this within Access' query builder, I get around 912 records returned, so I know I am able to retrieve the records and that the query itself appears to be correct.

The table is simple data which consists of string values such as (in the DCN column):

"13000"
"17001"
"16003"

Around 38000 in total so I shaln't print them all here...

Does anyone know why this will work via the query builder but not via VBA?

Thanks

Upvotes: 0

Views: 42

Answers (1)

June7
June7

Reputation: 21370

Appear to be mixing DAO and ADODB. Consider:

Private Function GNCN() As String

Dim rs As DAO.Recordset
Dim strSQL As String
Dim intYD As Integer

intYD = 13

strSQL = "SELECT DCN FROM Rates WHERE DCN like '" & intYD & "*';"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
Debug.Print rs.RecordCount

Set rs = Nothing

End Function

Upvotes: 1

Related Questions