Basic guidelines and tips for DAO to ADO conversion

1. Generally speaking, in ADO, all action queries, including crosstab ones, are considered procedures, whereas the select queries are considered views. In my particular case, some select queries were considered to be procedures by the system. I'm not sure why. It may be because the select query referenced a crosstab query or something like that. But the guideline above is a good place to start. If the error message you get is something like it can't find the item in the collection, switch to the other one and try again.

2. Year is a reserved word in ADO. This caused problems with my make table queries and subsequent queries based on those tables with Year as the field name / label. Not wanting to rewrite a ton of code or redo queries, I got around this problem by creating the table initially with "Year" as the field name. This worked on my summation queries so that Access did not balk at CountOfYear and others. However, I did run into problems when referencing it in some other queries. I had to use tablename.[""Year""] in order to avoid Access thinking my select query was a parameter query and sticking it in the procedures collection. Once I fixed the reference, Access put it in the views collection. Remember this point. This "feature" of Access moving the query around is very frustrating and time consuming. I looked for a list of reserved words for ADO but couldn't find one. If someone has one, please attach it to this posting.

3. According to Access help, in ADO, the value which .recordcount returns varies depending on how you open the recordset. If you open as static, it will provide the number of records immediately upon opening. If you open as forward-only, it will provide -1 immediately upon opening. If you open as dynamic, it will provide either -1 or the number of records immediately upon opening, depending. They didn't say depending on what, just depending. I know for sure that opening dynamic of an empty recordset will return -1 in recordcount. For those of us who check recordcount immediately upon opening, this tidbit is priceless. It is time consuming but absolutely necessary to evaluate the best way to open your recordset and then check recordcount appropriately. If you formerly checked only if recordcount > 0, as I did, you will have to change that to <> 0 if opening dynamic, in order to get past the error which is generated if your recordset is empty.

4. In ADO, table names with hyphens, and I assume query names like that also (I didn't have any), must be enclosed within square brackets, [], in the Open statement. No error is produced if you don't have them, so watch out. This is not necessary in DAO. It took me a while to figure out why my following if recordcount statement wasn't working, and it was due to the missing brackets on the Open statement. HOWEVER, if you are referencing a table using its name with a catalog, leave the square brackets off.

5. It appears that ADO has no equivalent of the recordsetclone. At least, not one which works as well as the DAO version. I left my occurrences alone and will upgrade when MS finally gives us a decent equivalent. If anyone has a foolproof version of this in ADO currently, I would appreciate a posting.

6. ADO uses the percentage character (%) as the wildcard for strings, while DAO uses the asterisk (*). You need to replace all occurrences of the * with the % in your queries in order to get them to execute properly from the program. However, they will continue to execute properly manually if you do not change them. Be wary of this fact.

7. Section is a reserved word in ADO.

8. Select queries which are marked as hidden are considered procedures in ADO.

Hope this helps the rest of you who are faced with this sort of conversion task.

The following is feedback from a couple of members:

1) PHV (MIS) 4 Nov 05 15:15
A useful link on this topic:
http://msdn.microsoft.com/library/en-us/dndao/html/daotoado.asp
Hope This Helps, PH.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886


2) RoyVidar (Instructor) 4 Nov 05 18:52

I'm sorry, I have to add some comments on some of this.

1 - union queries and parameterized queries are also often found in the procedures collection, and queries based on such, I think.

2 - in addition to being a reserved word in Access List of reserved words in Access 2002 and Access 2003, it is also a reserved word in Jet List of reserved words in Jet 4.0, I'm not sure how much it relates to ADO per se, but one should generally avoid reserved words when naming objects. There are a couple of naming conventions around, where I believe one of the reasons for using such, is that challenges like that does not arise.

3 - do not use the .RecordCount property of ADO recordset to check whether a recordset contain records, use .bof/.eof. The .RecordCount property of ADO recordsets are, at least in my, view, not reliable. See this discussion thread709-1052131 for more info (there's lot more available through a search). Even when, theoretically speaking, the .RecordCount property of an ADO recordset should give the correct count, it might not.

4 - never encountered something like that, probably because I never use anything but letters, numbers and underscore in object names - this is consistant with some of the most used naming conventions, and recommended by many. LittleSmudge here also recommends against underscore FAQ700-2190, but I wouldn't go that far.

5 - It does, you just have to use ADO form recordset (form recordsets, all versions, are DAO unless you've explicitly assigned ADO, or you're working with ADPs), not the default DAO - now I'm not 100% sure, but perhaps with 2000 version you had to use set rs = me.recordset.clone (i e, the clone method of the form recordset))

6 - Yup, and underscore (_) in stead of question mark (?) for single character wildcard

7 - Section is a reserved word in Access and Jet (see #2)

Roy-Vidar


Thank you Roy and PV for your clarifications and explanations and for the list of reserved words.