Table Corrupted due to NEW ADODB.RECORDSET

[PCroser]

I have encountered a problem when querying a table.
The query passed data into a recordset which should have resulted in many records but has returned EOF.

After debugging the code the only solution i have found is that i have set the recordset to NEW ADODB.RECORDSET two times before actually perfoming the rs.open (once in the main program and once in the dll which contains the loadrecords function).

MAIN PROGRAM

Dim rs as NEW adodb.recordset

qry = "SELECT * FROM tablename WHERE id=x"

IF (NOT dll.loadrecords(rs,qry,cn)) then
goto error_function
endif

DLL

function loadrecords(byref rs as adodb.recordset, _
byval qry as string, _
byval cn as adodb.connection) as boolean

SET rs = NEW ADODB.RECORDSET
rs.ActiveConnection = cn
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseServer
rs.Open qry

loadrecords=true
exit function

after executing this program and even go to the mysql engine (dos prompt) and perform the query (by the primary key) no records are displayed. (querying the table by another field which is not the primary key results in displaying the records i want). this proved to me that something was causing corruption to the table. restarting the mySQL service on the server allows the records to be displayed by the query using the primary key.


i have found that setting the declaration of rs in the main program to:

dim rs as adodb.recordset

has resulted in fixing the problem. because i eliminated the two times of the rs to new adodb.recordset. (?)

i have found a solution to fix this problem but i am not sure why this has only happened recently because it was working fine before for a long period of time. i am not sure whether it is a microsoft update to my computer or the server containing mysql database that has caused it or maybe an mdac issue?

if anyone has experienced this type of problem and knows exactly what is causing it i would greatly appreciate a response.

Peter