VBA & DAO problems - not making the connection to the database



I have given a sample of my code. It is not making
connection to the database. I get a recordcount of -1
which is weird. What am I doing wrong? Can anyone help
me out. Thanks!

Dim strSQL As String

Dim objADOConnect As New ADODB.Connection 'connection
Dim objRecSet As New ADODB.Recordset 'recordset
Dim objCommand As New ADODB.Command 'command

strSQL = "SELECT [index_id],[folder_name],[parent_id]
FROM Folder_Info" & _
" WHERE [parent_id] = '0'" & _
" ORDER BY [folder_name]"

With objADOConnect
.ConnectionString = "DBQ=speakers.mdb;" & _
"DRIVER={Microsoft Access Driver
(*.mdb)};" & _
"DefaultDir=C:\My Documents\Eve
'.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0; Data Source= " & _
"C:\My Documents\Eve
End With

With objCommand
.CommandText = strSQL
.CommandType = adCmdText
.ActiveConnection = objADOConnect
Set objRecSet = .Execute ****This is the problem area
End With

Juan M. Afan de Ribera

That is because the recordset you get with the command .execute method
returns the default recordset, which cursor type property is
adOpenForwardOnly and which lock type is adLockReadOnly, and it needs to be
another kind of cursor type and lock type for recordcount to be available.
You better use the recordset .Open method. For example:

Dim rst As New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Source = "customers"
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
End With

Debug.Print rst.RecordCount

Set rst = Nothing

I don't really know if it is a better solution for it, because I'm not an
expert programming in ADO, but this could be a way to do it.


Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question