Migrating to SQL server - converting DAO to ADO

[adhoustonj]

Does anyone have any good references for converting DAO to ADO by example? One thing I have really struggled with is finding a good reference source to convert my DAO to ADO. I know queries need to be optimized, and views implemented where possible, etc.. That is about the extent of my knowledge.

I've heard about query params, and query defs, but.. I'm not sure I need those.

My biggest trouble has just been finding a source on this is what your DAO was, this is what your ADO needs to be..

Also, I know there are many ways to connect to SQL server tables -
Declaring a const connection string, using the native access link, and declaring a string in the connection string while setting/declaring ADO.

I feel I am mixed between different implementation solutions the more I search and read other code.

I recently set up a test environment in SSMS and for some reason - (IT controls this) but one of the more complicated engineering databases was selected as the promo candidate, but I would like to suggest we test the migration with a simpler database, which would be an employee training database, that doesn't require other db's. it is isolated, and simple, but I would like to be prepared on how to modify my DAO to ADO before requesting the new test migration.

My IT dept desperately wants to get rid of access, but there eyes were opened when they realized the full extent of these db applications - they thought they were just "databases" but once receiving a preview they quickly reclassified them as ohhhhhh, these are "applications". Yes, big yes.

 

[theDBguy]

I don't know any reference, but you might consider posting a sample of one of your DAO you want to convert and maybe someone here will give you a hand (or show us how) to convert it into ADO.

 

[GPGeorge]

Does anyone have any good references for converting DAO to ADO by example? One thing I have really struggled with is finding a good reference source to convert my DAO to ADO. I know queries need to be optimized, and views implemented where possible, etc.. That is about the extent of my knowledge.

I've heard about query params, and query defs, but.. I'm not sure I need those.

My biggest trouble has just been finding a source on this is what your DAO was, this is what your ADO needs to be..

Also, I know there are many ways to connect to SQL server tables -
Declaring a const connection string, using the native access link, and declaring a string in the connection string while setting/declaring ADO.

I feel I am mixed between different implementation solutions the more I search and read other code.

I recently set up a test environment in SSMS and for some reason - (IT controls this) but one of the more complicated engineering databases was selected as the promo candidate, but I would like to suggest we test the migration with a simpler database, which would be an employee training database, that doesn't require other db's. it is isolated, and simple, but I would like to be prepared on how to modify my DAO to ADO before requesting the new test migration.

My IT dept desperately wants to get rid of access, but there eyes were opened when they realized the full extent of these db applications - they thought they were just "databases" but once receiving a preview they quickly reclassified them as ohhhhhh, these are "applications". Yes, big yes.

Is there a business rule requiring conversion from DAO to ADO in force at your shop? Is this driven by IT's demand?

 

[cheekybuddha]

My biggest trouble has just been finding a source on this is what your DAO was, this is what your ADO needs to be..

You do know that in 99% of cases DAO works just fine (and is often faster than ADO). As George asks, is there a particular need for you to convert to ADO?

 

[CJ_London]

Ado has a more limited range of criteria options and some functionality of forms do not work with ado - in particular sort and filter functions.

you would be better looking at making use of sql server tSql and using pass through queries

 

[cheekybuddha]

Ado has a more limited range of criteria options

Please expand

 

[CJ_London]

Please expand

With ADO you cannot:

• use a subquery or syntax such as EXISTS
• use Like 'AB*CD' or like '*ABCD', only like '*ABCD*' or like 'ABCD*'
• use IN (1,2,3)
• use vba functions such as iif(... or switch(..., - (which can't be used in TSQL either as Pat intimates)
• mix AND's and OR's - so (A AND B) OR (C AND D) is OK, but (A OR B) AND (C OR D) is not.

 

[adhoustonj]

Wow, for some reason I thought I had read many many times to convert DAO to ADO when migrating to SQL server. I have dodged a bullet, because I do not like ADO at all.

Very good input from everyone, thanks.

@Pat Hartman thank you again for your guidance. I have some optimizations to do. I think my biggest struggle is true normalization in some of my databases versus the filters, as most forms always include a filter in the recordsource tied to whatever is relevant. What about like fosUsername functions in where clauses? Most of my start up forms include a fosUserName function that is a windows API in a sql where clause, it sounds like that is a big no no? And also in constant useroutdate forms to track users in databases which a hidden form after launch that also has a fosUserName function in the SQL where clause.

 

[Josef P.]

With ADO you cannot:

• use a subquery or syntax such as EXISTS
• use Like 'AB*CD' or like '*ABCD', only like '*ABCD*' or like 'ABCD*'
• use IN (1,2,3)
• use vba functions such as iif(... or switch(..., - (which can't be used in TSQL either as Pat intimates)
• mix AND's and OR's - so (A AND B) OR (C AND D) is OK, but (A OR B) AND (C OR D) is not.

What do you mean?
Why should you not be able to use an Exists expression with ADODB in a sql statement?
The use of Jet expression services does not run with direct Connection to SQL-Server.
But everything else works with ADODB - with the code in the appropriate SQL dialect.

With ADODB you have the advantage that you can directly use the SQL dialect of the server if you use a direct OLEDB connection.
When I use ADODB, I only use the direct connection to the server. CodeProject.Connection (to the local DB) gives no benefit for me.

On the other hand, I see no particular reason to switch from DAO to ADODB.
I use both technologies in parallel - just what fits best.

/edit:
I think I can guess what is meant.
ADODB.Recordset.Filter does not support the above.
I didn't think of that at first, since I don't use something like that to filter records. I prefer to let the DBMS work.
Giving that as a reason not to use ADODB ... hmm ...

 

[cheekybuddha]

With ADO you cannot:

• use a subquery or syntax such as EXISTS
• use Like 'AB*CD' or like '*ABCD', only like '*ABCD*' or like 'ABCD*'
• use IN (1,2,3)
• use vba functions such as iif(... or switch(..., - (which can't be used in TSQL either as Pat intimates)
• mix AND's and OR's - so (A AND B) OR (C AND D) is OK, but (A OR B) AND (C OR D) is not.

A curious list.

It should be pointed that this only applies [AFAICT] in the specific case of trying to use an existing recordset's .Filter property.

All these are possible if specified in the WHERE clause of the original SQL of the recordset being opened. (only tested the first 3bullet points)

I had never come across these limitations because I don't think I have ever filtered a recordset after opening. Better to limit the amount of data before it comes down the wire.

Do the same limitations apply to ADODB.Recordset.Find ?

Thanks for the info.

 

[CJ_London]

I think I can guess what is meant.
ADODB.Recordset.Filter does not support the above.

yes - you are right, that is what I was thinking of. I make a fair amount of use of ado for ostensibly non updateable queries such as union queries

edit - a recent example is a list of payments where each payment might be split across a number of cost centres by using a 'sub record'. All records are returned and the sub records filtered out - user can click on a payment and the filter is adjusted to show the relevant sub records within the list. By disconnecting the recordset, the user can add new records/sub records 'in place' rather than at the end of the form.

I could have shown the sub records in a sub form, or used a tree view control, but neither met the requirements of the client.

Another example is a budget summary where clicking on a row expands the list to see the detail

I don't use .find, I'll take a look next time I'm working on it, but my guess would be the same limitations would apply

 

[cheekybuddha]

Didn't see Josef's edit before I posted

 

[adhoustonj]

So - is there any reason to use ADO over DAO for certain things?

Also, when converting backend to SQL server, are there certain procedures that should be taken out of front end db such as persistent connection with vba & hidden forms?

 

[adhoustonj]

My skull is really quite thick.

To clarify what I'm asking - I was wondering if the persistent connection code should be removed from Access FE's when migrating to SQL server BE's, and it sounds like it probably should. Is anything else I should also add to my "BE Access to SQL server migration" checklists?

I haven't used querydefs at all, but if you say you use them for almost everything - sounds like I need to check those out.
when you say passthrough queries, are those mostly Sql server stored procedures, a mix of both, and if a mix what determines whether you make something an SP versus traditional sql statement?

 

[CJ_London]

So - is there any reason to use ADO over DAO for certain things?

it's about using the right tool for the job. Access is designed to work with DAO, but sometimes you can't do what you want to do with DAO, so use ADO - I provided an example in post #13. To do that in DAO would require creating a temporary table.

 

[GPGeorge]

My skull is really quite thick.

To clarify what I'm asking - I was wondering if the persistent connection code should be removed from Access FE's when migrating to SQL server BE's, and it sounds like it probably should. Is anything else I should also add to my "BE Access to SQL server migration" checklists?

I haven't used querydefs at all, but if you say you use them for almost everything - sounds like I need to check those out.
when you say passthrough queries, are those mostly Sql server stored procedures, a mix of both, and if a mix what determines whether you make something an SP versus traditional sql statement?

A persistent connection is unnecessary with linked SQL Server (or other server-based database) tables. You don't gain anything by trying to implement one in that environment.

Manipulating QueryDefs in VBA can be effective when the specific task at hand calls for that, but it's more practical to let that demand drive the choice of tools, rather deciding on a tool and looking for a way to use it.

Passthrough queries send a SQL string directly to the server to be processed. That means anything you could do in the SQL Server database, from executing a stored procedure to opening a Recordset, to appending records, etc. can be sent to the SQL Server database that way.

The choice of using passthrough query vs a local Access query comes down to what advantages do you get by executing the query on the server. Heavy calculations involving multiple tables might be one such situation. If you can run a complex query in 2 seconds in SQL Server, and the same query using linked tables in Access would require 10 seconds to run, then that is a candidate for a passthrough. You can write VBA functions to process records in linked tables. Or you can write stored procedures to perform that same processing on the Server. Again, speed and efficiency will tell you whether it makes sense to move that process to the server and call it from a passthrough.

Other reasons might exist, but the general principle at hand is when you gain speed, efficiency, or greater security by moving work from Access to SQL Server, it is a good idea to do that.

The issue of using QueryDefs comes out of that decision. Because Access merely sends the SQL to the server via a passthrough, it can't provide parameter references, such as a control on a form. SQL Server knows nothing about that form and would fail if you sent the control reference as a parameter. You have to convert the reference to the value it represents, and that means rewriting the SQL in the passthrough for every request. And that is where the QueryDef objects comes into play.