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.
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.
