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
Marie\Projects\Speaker_Specialist\Data;"
'.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0; Data Source= " & _
"C:\My Documents\Eve
Marie\Projects\Speaker_Specialist\Data\speakers.mdb"
.Open
End With

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

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
.Open
End With

Debug.Print rst.RecordCount

rst.Close
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.

HTH

--
Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>
http://www.juanmafan.tk
http://www.clikear.com/webs4/juanmafan


"EMP" <kperatopoulos1899@wideopenwest.com> escribió en el mensaje
news:07c301c3a734$be458170$a001280a@phx.gbl...
> 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
> Marie\Projects\Speaker_Specialist\Data;"
> '.ConnectionString = "Provider =
> Microsoft.Jet.OLEDB.4.0; Data Source= " & _
> "C:\My Documents\Eve
> Marie\Projects\Speaker_Specialist\Data\speakers.mdb"
> .Open
> End With
>
> With objCommand
> .CommandText = strSQL
> .CommandType = adCmdText
> .ActiveConnection = objADOConnect
> Set objRecSet = .Execute ****This is the problem area
> End With
>