VB-ADO with SQL search gives error message. Attached procedure is use

[Yasir4444]

Public Sub GetCounter(TableOfCounter As String, RecordCounter As Double)

Dim DbCnn As ADODB.Connection0
Dim Cmd As ADODB.Command
Dim Rec As ADODB.Recordset
Dim SQLStatment As String

SQLStatment = "SELECT CntCounter FROM TblCounter WHERE CntTblName = ' " & TableOfCounter & " ' "

TableOfCounter = UCase(TableOfCounter)
RecordCounter = 0

Set DbCnn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set Rec = New ADODB.Recordset

DbCnn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\MySample\MyMSAccess.MDB"

With Cmd
.ActiveConnection = DbCnn
.CommandType = adCmdText
.CommandText = SQLStatment
.CommandTimeout = 60
Set Rec = .Execute
End With

RecordCounter = Rec!CntCounter

End Sub


' On the second last line [RecordCounter = Rec!CntCounter] I get error message,
which is
"Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

The record does exist if I issue the same SQLStatment into MS-Access using
MS-Access queries.

I have tried to insert this statment [Rec.MoveFirst], but I get the same error
message as stated above.

Could someone, please help me since I have spent over a week now and cannot find
any solution even after checking serveral different books.

Thanks,

Yasir Ahmad

 

[proggroup]

you can try making it adOpenDynamic and first thing is check if the record exists!!