VB-ODBC Direct...Is it a bug????????

[Purnima]

Hi there,
I don't know whether its a bug or a problem.
When I use ODBC Direct Workspace for accessing MS-Access database through VB, I get a static value of -1 for the recordcount property of the recordset. Following is the code. Any suggestion is welcome.

Dim ws As Workspace
Dim db As Database
Dim rs As Recordset

Private Sub Command1_Click()
Set ws = CreateWorkspace("lrmsdb", "lrms", "lrms", dbUseODBC)
Set db = ws.OpenDatabase("lrms_db", dbDriverNoPrompt, False, "ODBC;DATABASE=lrmsdb;UID=;PWD=qwerty;DSN=lrms_db")

Set rs = db.OpenRecordset("select khasra_no from khasra where village = 69 and no_of_settlement = 1", dbOpenDynaset)


rs.MoveFirst
MsgBox rs.RecordCount
i = 1

Do While Not rs.EOF
Debug.Print i, " ", rs.Fields(0)
rs.MoveNext
i = i + 1
Loop
rs.MoveLast

MsgBox rs.RecordCount
End Sub

Thanks in anticipation.
Best Regards,
Purnima
[email protected]

[Crazy D @ Work]

I'm using ADO to connect to SQL server, and there I have the same problem, that after a query the recordcount = -1, while it contains a few records (so I changed the check from Recordcount > 0 to Not .BOF and Not .EOF).
I'm not sure why it's not working (it doesn't work too if I do .movelast and then .movefirst again, which I saw as a sollution somewhere).


Crazy D :-)

[Ravi Kiran]

Hi,

I had had similar problem, even with DAO.
I will explain the problem & the soln i found.
Hope it gives you both some idea..

Scene: I have a Access DB, but i dont use Data control. Instead i write most of it using SQL (for what ever reason..). I wrote a member fn to get the no. of records like this: (Db access was wrapped in a class)

public Function GetNoofScans(byval sPatid as string, optional byval scandate)
Dim sqlstr as string
Dim tmprecset as Recordset
If Not m_bDataReady then Exit Function
on error GoTo ErrorInit
sqlstr = "Select * from " & DetailsTBL & " where "
sqlstr = sqlstr & PatIDField & " = '" & sPatid & "' "
If Not IsMissing(scandate) then
sqlstr = sqlstr & " AND " & DateField & " = #" & scandate & "# "
End If
''''' *********** IMP LINE *******
sqlstr = sqlstr & FileDispOrdKey$
'''''
set tmprecset = m_AllPatDB.OpenRecordset(sqlstr)
GetNoofScans = tmprecset.RecordCount
tmprecset.Close
set tmprecset = nothing
Exit Function
ErrorInit:
End Function





Without this "IMP line"
sqlstr = sqlstr & FileDispOrdKey$
(where FileDispOrdKey$ = "ORDER BY " & MainKey & " DESC" - a constant.)
my answer *always* use to be zero, though it did have a few records. Only after a bit of debugging and fretting around i found out that this 'ORDER BY' is necessary.

But, in other fns, there use to be other DB calls like .MoveFirst or .Move SomeIndex after opening the recordset. I found that after executing this/such line ONLY the recset.RecordCount used to show correct value.

But in your case
>rs.MoveFirst
>MsgBox rs.RecordCount
the msgbox is after Movefirst, so i am not sure!!

Anyway, try including a "order by", even if it were to be a default key and check.

Hope it works!
Ravi Kiran