Link to home
Start Free TrialLog in
Avatar of llucy88
llucy88

asked on

Using ADO problem

I use ADO to do database manipulation(DB access2000). First I open the connection, then import records to "mytable" from a text file. Then I CLOSE this recordset. Try to query "my table" using "select * from my table". But sometimes, I got "rst.eof" is true, some times is false. The result is not consistant. I opened the table, the records are there. I am wondering if the database status is not refreshed after I import records from text file? How should I refresh it? The wierd thing is if I use "debug" to step into every process without changing code, the "rst.eof" will always "false"???

Any help will be appreicated!

Thanks!

Lucy
Avatar of bobbit31
bobbit31
Flag of United States of America image

can you post some code please?
Avatar of HobbitHouse
HobbitHouse

try this after the import and before the query:

mytable.MoveFirst

Avatar of cmgarnett
Try using BeginTrans and CommitTrans on your ADO record writing code.

Sometimes ADO seems to lag behind and using the BeginTrans and CommitTrans makes the program wait for the ADO processing to be completed.
Are you closing the connection between the import and the opening of the record set, if you are that might be the problem.
Also try adding DoEvents after closing your original recordset.
Avatar of llucy88

ASKER

Too much code for me to put here.

my table.movefirst, doevents, not working.


As files importing tables are for multiple tables. I am using the "begintrans" here to utilize the "rollback" feature in case one of the file format is not right. I defined differenct DB connections for "importing" and "manipulation". Between them, I closed the importing connection. Maybe the reason is  S2's comments. I just don't understand why? Do I have to change them same connection? why sometimes the process is right?

Thanks!

Lucy
this sample should work for you
(the source for the problem is the same)
you can find some code to flush changes to the database from client pc.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240317

cn.Properties("Jet OLEDB:Transaction Commit Mode") = 1

SUMMARY
Because the Microsoft Jet database engine has a read cache and lazy writes, you can get duplicate values in your custom counter field if two applications add records in less time than it takes for the cache to refresh and the lazy-write mechanism to flush to disk. This article presents a method that takes these factors into account.



ASKER CERTIFIED SOLUTION
Avatar of BBaron
BBaron

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of llucy88

ASKER

Thanks a lot!

fool