• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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!


1 Solution
can you post some code please?
try this after the import and before the query:


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.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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.
llucy88Author Commented:
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?


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.


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

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.

just a little comment....
I had the exact same problem about a year ago when I was
importing from anouther database, using update querys. If I put a pause of say 1 sec before the requery it would be ok, if not then on the update I got duplicate error.
I fixed the problem by closing and reopening the connection to the target database. This obviously decreased efficiency drastically but seemed to work. Still dont know how to fix properly though. Glad to see some other poor fool has the same stupid problems as me ;-))

llucy88Author Commented:
Thanks a lot!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now