Do DAO and ADO have problems?

[jonrmoore]

This is a general question to developers who use VB6 with DAO and ADO preferably in the same project. Several years back another programmer and I wrote and developed a system using VB6 and DAO with Access databases. Everything was okay except for his coding problems--now he is gone. Another programmer and I have taken that code and slowly replaced DAO with ADO so that later we could use SQL or whatever. Every since then, the systems are not stable to say the least. Sometimes we get incomplete data and often get many, many system error messages. We found that MS Server 2003 and 2008 "timeout" with ADO and access databases; so, we applied the modifications MS suggested to the registry. That helped but did not eliminate the problems. I just wanted to ask if anyone else has encountered this and what they did about it. Thanks.

[Spoo]

Jon

I'm in your former camp .. all I know is how to code for DAO.

I don't know squat about ADO, so if you don't mind, I'll tag
along for the ride.

Spoo

[Pradeep1210]

I haven't faced any problems with ADO during the entire time I coded in VB6. Now I've left that and moved on to ADO.NET.

Do you have any specific examples of statements those cause that problems you mention? Also, are you sure you have used the correct cursor location, cursor types, locks etc.?

[jonrmoore]

The problems seems to be random in the broadest sense of the word. They only occur in projects where multiple users are involved like point-of-sale. We get erros like an object is closed or that tables in a database are not found (but they are clearly there). It seems like DAO and ADO do not recognize each others record locks, record status or whatever.

[Pradeep1210]

There is no co-relation between DAO and ADO except that they both can be used to fetch data from/to database. If you are using objects created using DAO and trying to use with ADO, then that might be a problem.

But just as I said, I passed the entire lifecycle of ADO and didn;t face any such problems. You might be doing things wrongly. VB6+ADO was a very good and reliable platform. That's what my experience says.

[jonrmoore]

The specific problem observed is like this:
- data is written using DAO : a new record is created for example
- immediately the same data is read by ADO : and this record can't be found until a few seconds (or if I close the DAO connection)

[Pradeep1210]

Once data has been written to database, it has been written. It doesn't matter you wrote it using DAO or ADO, or you opened ms-access table and typed it in.

Since you said the data is not available until DAO connection is closed, that can be a problem. It might be caching the data temprorily and writing to database in bulk. Though I haven't seen this problem in ADO.

[Hack]

I am with Pradeep1210...it has been at least 10 years since I've use DAO for anything, and I haven't had any problems at all.

I suspect that once you are completely off of this "dual" connectivity platform (i.e., ADO only with no more DAO anywhere), your issues will disappear.

[Pradeep1210]

Since DAO/ADO are connection based technologies, you should also adhere to good practices to optimize your application.

e.g. There is a limit on the number of concurrent users a database supports. MS-Access supports maximum 10 concurrent connections (I'm not sure, exact figure might be wrong). So "opening connection late and closing early" would help you out of this and help you support much more than the specified limits. etc.

[Spoo]

The specific problem observed is like this:
- data is written using DAO : a new record is created for example
- immediately the same data is read by ADO : and this record can't be found until a few seconds (or if I close the DAO connection)

Jon

Would doing either of these help?

• write the data using DAO, and immediately close the DAO connection
• don't use DAO at all (which I thought you were migrating towards)-- write the data using ADO

Spoo

[GuillaumeJ]

I'm the programmer on this thing. The second one. The context above all this thing is that the source code is a mess : for example, the original programmers never used more than three letters in a variable name, and stored data in tag field of listview subitems. Whole forms duplicated when only a table name is changed in a query, and so on. Of course, no comments.

I arrived on the project and one of the thing that was wanted was migrating to ADO : since the budget for full migration was not possible (and still isn't), i just do (nearly) all the new functions in ADO, and when correcting bugs in the old code, tried to move things from DAO. I never really looked, but I think it's still 50+% DAO (70% is my wild guess)

It's a multi-user application, using a access database on shared network folder.

- write the data using DAO, and immediately close the DAO connection

The problem with closing the DAO connection is that there a few recordsets open on the same connection, and closing the connection makes them invalid.

I tried recently a kinder version, putting the DAO code in a transaction (hoping that the commit will flush all pending changes to the file). I'm waiting for user feedback on this.

[Pradeep1210]

The problem with closing the DAO connection is that there a few recordsets open on the same connection, and closing the connection makes them invalid.

Just as I suspected and said in earlier posts, bad practices are the major source of your problems.
And in the same context "open late close early" principle for database connections.
Don't keep connections open unnecessarily. They eat resources, besides blocking other users who might really be needing them. Open connections as late as possible and close them as soon as you have got what you wanted out of that connection. Also ideally, one or two concurrent connections per application is more than enough.

[dilettante]

I see a lot of different issues and questions being jumbled together here, as well as some completely off the wall advice. Jet has no 10 user limit, though there is a limit of 255 open connections. I thought this was a VB6 application. If so MS Access isn't even in the picture here so why mention it?

The specific problem observed is like this:
- data is written using DAO : a new record is created for example
- immediately the same data is read by ADO : and this record can't be found until a few seconds (or if I close the DAO connection)

Most likely this isn't a DAO, ADO, or DAO + ADO issue.

Every client using the database runs his own instance of the Jet database engine. ADO and DAO are just database API "adapters" between Jet and the application code.

Jet itself has numerous optimizations made to improve network and file server use in a shared MDB scenario and local I/O traffic in a single user situation. Several of these optimizations involve concurrency control and caching. There are numerous tuning parameters available for advanced users to manipulate, but "Joe DAO" typically lets everything default to Jet's safest settings.


Just to take one example:

DBPROP_JETOLEDB_SHAREDASYNCDELAY
Description: Jet OLEDB:Shared Async Delay

The maximum time Jet can delay asynchronous writes to disk, in milliseconds, when the database is opened in multiuser mode.

This delay is intentional, not a limitation. It is meant to group several updates within a page of the database disk file before attempting a physical write. By the same token we also have:

DBPROP_JETOLEDB_PAGETIMEOUT
Description: Jet OLEDB:Page Timeout

The amount of time, in milliseconds, that Jet will wait before checking to see if its cache is out of date with the database file.

There are lots of these performance and behavior parameters and they are known to ADO and DAO by different names as well as having different default values set in the registry. One of the worst Mort flubs is to alter the Jet defaults in the registry to tweak application behaior. Not only will this impact every other application using Jet MDBs, it also won't survive a move to a new machine or even a switch between DAO and ADO.

People are using Jet with ADO and DAO every day. Without incident.


Adding transactions when you have a mix of ADO and DAO open connections in the same process seems like a recipe for deeper disaster.

[GuillaumeJ]

Just as I suspected and said in earlier posts, bad practices are the major source of your problems.
And in the same context "open late close early" principle for database connections.
Don't keep connections open unnecessarily. They eat resources, besides blocking other users who might really be needing them. Open connections as late as possible and close them as soon as you have got what you wanted out of that connection. Also ideally, one or two concurrent connections per application is more than enough.

I can't agree more with your first sentence.
The problem is that I did not write this program. I started on it (correcting bug, adding functions - but it was already a "finished" product) a few months (or more ?) after the original programmer quit. So I'm stuck with his mess :The connection is opened when the program starts, and it's closed when the program end. Same for several recordsets also.

I wanted to post the problem here because I was looking for a way to flush pending DAO changes. (I could not find it)
I also know that in ADO you can disconnect a recordset from the db by setting its connection property to noting. Is it possible in DAO ?

[dilettante]

Don't keep connections open unnecessarily. They eat resources, besides blocking other users who might really be needing them. Open connections as late as possible and close them as soon as you have got what you wanted out of that connection.

This looks like a recommendation out of context.

This pattern of connection use is meant for classic ASP pages where IIS connection pooling keeps the database connections from being repeatly closed and re-opened. If you do this in a client application (like a VB6 program) you will incur some horrendously expensive operations repeatedly, not limited to but including firing up the Jet engine, having it go through initialization, loading its metadata from the database, etc, and then shutting it all back down again.

If database access is light and infrequent this might make sense, otherwise not.

There was an answer to all of this long ago: Remote Data Service (RDS). Sadly it was beyond most Morts' skills and required more influence over server platforms than most Morts could ever pry out of the hands of the local box jockeys. Interestingly the whole range of things now called "Web Services" (including SOAP) evolved out of RDS.

In the end Microsoft got disgusted and decided to start pushing stripped down versions of SQL Server instead. The average Mort is now happier (though even more dangerous) because up to a point SQL Server will self-tune and by nature it uses the network very differently making some of the other issues go away.

[GuillaumeJ]

Most likely this isn't a DAO, ADO, or DAO + ADO issue.

So what is it ?
Because I can't think of another source for the problem than a (read or write) cache in ADO or DAO.

Adding transactions when you have a mix of ADO and DAO open connections in the same process seems like a recipe for deeper disaster.

I took care to check that the transaction was around a finite unit of work, so.. even i'm not comfortable with it, it should be safe. Even if the application is multiuser, users are not accessing the same data at the same time, too.

[dilettante]

So what is it ?
Because I can't think of another source for the problem than a (read or write) cache in ADO or DAO.

I'm not suggesting your perceived "problem" isn't related to caching. But the initial post in this thread asked about ADO and DAO "having problems." No, they do not "have problems."

Have you gone through the documentation on the DAO DBEngine object? Many parameters can be tweaked via its SetOptions method, some connection-oriented and some at the Table or Transaction level. For example:

UserCommitSync

The UserCommitSync setting determines whether changes made as part of an explicit transaction (a change made to data by using the BeginTrans, CommitTrans, and Rollback methods) are written to the database in synchronous mode or asynchronous mode. In synchronous mode, Microsoft Jet doesn’t return control to the application code until the changes made by the CommitTrans method are written to the database. In asynchronous mode, Microsoft Jet stores the changes in its memory buffer, returns control to the application code immediately, and then writes the changes to the database in a background thread. Microsoft Jet begins writing the changes either after a specified period of time (determined by the FlushTransactionTimeout setting, or by the SharedAsyncDelay or ExclusiveAsyncDelay settings described later in this section) or when the MaxBufferSize is exceeded. The default UserCommitSync setting is Yes, which specifies synchronous mode. It is not recommended that you change this setting because in asynchronous mode, there is no guarantee that information has been written to disk before your code proceeds to the next command.

[vb5prgrmr]

Okay, the problem as stated with a DAO write is just like a text file. Sometimes the buffer is not flushed until the file is closed and the same can be said for using the recordset method of updating/inserting into a database with with any of the database technologies (DAO, DAO ODBC Direct, RDO, or ADO), thus the problem with ADO not seeing it right away, but close that recordset and you should have faster results. Better yet, change from using the recordset methods to either update or insert queries, create a public sub that accepts a string arguement, and executes via the public/global ado connection object and your ado rs refresh/open method should be resolved.

On another note, using VB's find and replace function can convert a project from dao to ado in a manner of an hour or so...


Edit: dilettante beat me to it with the documentation...


Good Luck

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

The problems do seem to be related to caching etc, as dilettante's posts suggest.

The long term solution would be to only use one of the technologies, but in the short term the kind of changes that dilettante has mentioned are likely to reduce the issues to the point that they are not noticeable.

On another note, using VB's find and replace function can convert a project from dao to ado in a manner of an hour or so...

That depends on the coding style used for DAO, there are a few styles which would be a nightmare for that - while one style should be fine (as long as it is manual editing rather than automatic by find/replace, and there are only very simple SQL statements [no wildcards etc]).

An hour is rather optimistic for most projects, but if the "right" DAO style was used then most projects should take less than a day each to convert.

[GuillaumeJ]

Better yet, change from using the recordset methods to either update or insert queries, create a public sub that accepts a string arguement, and executes via the public/global ado connection object and your ado rs refresh/open method should be resolved.

The one good thing in the code base is that every dao update/insert is made by query.
Are you telling me that the cache problem I have (write DAO/read ADO) would not occur in the other way (write ADO/read DAO) ? Because, it would be easy to do all the execute on the ADO connection.
I already moved a lot of the execute queries in an dedicated Execute function. In fact, I was thinking a few days ago about adding a parameter (global or not) so that the function could use DAO or ADO.

On another note, using VB's find and replace function can convert a project from dao to ado in a manner of an hour or so...

I thought that, at first, but :
- there is a lot of bizarre use of absoluteposition (start at 0 in DAO, 1 in ADO) : it created some nasty bugs. Now that I know about it, maybe I could dodge then, but still, I'm wary about code like that :

  If transactionRs.AbsolutePosition <> transactionRs!transNo Then
       i = transactionRs.AbsolutePosition
        db.Execute "UPDATE [Transaction] SET transNo='" & i + 1 & "' " & _
                         "WHERE ID=" & transactionRs!id, dbFailOnError

- I also encountered problems with FindFirst with several criteria. (whereas ADO Find accepts only one parameter). I replaced them with Filter, but Filter change the recordset count (and there was also a side effect with absoluteposition which I don't remember exactly) which created bugs later

[si_the_geek]

Are you telling me that the cache problem I have (write DAO/read ADO) would not occur in the other way (write ADO/read DAO) ? Because, it would be easy to do all the execute on the ADO connection.

There is no reason to suspect it would be noticeably better; switching fully to just one technology (preferably ADO as it still current, rather than DAO which was out of date in 1997) is the 'reliable' way to do that.

In order to improve things in the mixed situation, follow dilletante's suggestions of specifying the settings for delays etc.

I thought that, at first, but :
- there is a lot of bizarre use of absoluteposition (start at 0 in DAO, 1 in ADO) : it created some nasty bugs. Now that I know about it, maybe I could dodge then, but still, I'm wary about code like that :

  If transactionRs.AbsolutePosition <> transactionRs!transNo Then
       i = transactionRs.AbsolutePosition
        db.Execute "UPDATE [Transaction] SET transNo='" & i + 1 & "' " & _
                         "WHERE ID=" & transactionRs!id, dbFailOnError

Ouch, that kind of code is a bit of a nightmare - and asking for disaster no matter what the situation!

It wont be easy to convert that, but at least you can make it safer at the same time as converting it.

[GuillaumeJ]

There is no reason to suspect it would be noticeably better; switching fully to just one technology (preferably ADO as it still current, rather than DAO which was out of date in 1997) is the 'reliable' way to do that.

Yes, but... no budget.
I added some flags so that every execute can be done in an ADO transaction. Thanks to source control, it will be easy to revert back.
So, every write will be done in ADO, read will be done in DAO (old code) or ADO (new or converted code).
I'm also going to look at these various setting for the connexion :
- Jet OLEDB:Flush Transaction Timeout
- Jet OLEDB:Implicit Commit Sync
- Jet OLEDB:Transaction Commit Mode
- Jet OLEDB:User Commit Sync
and for DAO
- dbPageTimeout (to something very fast, like 200 or 400 ms).