Migrating to SQL server - converting DAO to ADO (1 Viewer)

[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

 

[Pat Hartman]

Bound Access Forms use DAO regardless so, unless you actually have some reason for converting to ADO, don't do it.

If your Access application was built using good client/server techniques, upsizing to SQL Server is trivial and will require slight modifications to DAO code to include arguments that SQL Server needs that Access does not. I always build my DAO code to include the arguments so I don't have to do this step later.

There are a couple of serious buzz kills.
1. your forms are bound to tables or to queries with no criteria so you are using filters to trim down the data set for the user. This MUST be changed to take advantage of SQL Server. ALL forms must be bound to queries with criteria that severely limit the number of rows and columns returned. You do not bring down ANYTHING from the server that you are not going to use immediately. In some applications, I create complex search forms. These are "pre" filters that reduce the rows the client needs to work with. The form builds a query using a fixed select clause with a custom where clause. It counts the rows the query will return and tells the user if the number is too large. He can override and bring down whatever he wants. The code opens a list type form which can be further filtered if multiple rows are returned or opens the edit form directly if only a single row is returned. On the single record form, there are a couple of test boxes or combos that allow a single record to be selected if the use knows going into the task what records he wants to work on - one at a time.
2. If your forms have lots of combos, you might want to download the rowSoures into a local table when the app loads. This can dramatically reduce overhead in a SQL Server environment and sometimes even when the BE is ACE. Of course, you need to give the user an option to update the combos on the fly should he need updated rows. Usually the combos are fairly static so this isn't usually a big problem. Here's a link to a mini-app i include in all my applications that is used to manage simple lookup tables.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

3. You need to make sure that NONE of your queries use VBA functions or UDF's anywhere except the Select clause. SQL Server can't process VBA functions - many have direct conversions to SQL functions but not all - or UDF's because it doesn't support VBA. Therefore, if you use a UDF in your Where clause, it cannot be processed by the server so Access requests all rows from all tables and performs the joins and selection operation locally - eliminating using the power of the server. Rarely, you might need to create Stored Procedures to solve this problem but I have only had to resort to sp's for reports or complex batch processes. I've never had to use one to fetch a record I wanted to update via a bound form.

Once you get past those three things, if you need performance improvements, you can try creating Views to optimize common joins. You will be most unlikely to ever have to resort to pass-through queries or unbound forms if you get everything else right. Remember Access all by itself makes every effort to "pass through" every query to the server. Only YOU can prevent this from being successful so review #3 again. Technically using querydefs against linked tables is slower than using pass through queries but only marginally so because you have the overhead of Access converting Access SQL to T-SQL it can pass to the server. But as long as Access can get a clean convert, you should never have to resort to converting any query.

 

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

 

[Pat Hartman]

The DAO/ADO dilemma for SQL Server predates ACE. DAO has always been better for Jet/ACE but I don't have anyone reporting a significant speed increase when using ADO these days.

If you can convert bulk action queries to pass-through that can be a speed enhancement because it prevents Access from embedding the action in a transaction so it eliminates some overhead. Remember the question you get when you run update and delete queries? Do you really want to do this? This happens because Access encapsulates the update within a transaction giving you the option to say no and then Access will roll back the updates. For pass-throughs, you get NO second chance. You send the delete to the server and you can color the records gone. No OOPS to give you a second chance.

LIKE is a double edged sword. It can easily prevent the database engine (any engine) from using indexes to satisfy your search and that means the database engine must do a full table scan which some call RBAR (Row by Agonizing Row). As you can imagine, this action gets slower and slower as the table row count grows.

If you need to use Like, you need to use Like so you are willing to pay the price but I see so many people, experts included using Like when they don't even have any wild cards:

"Like SomeID" instead of "= SomeID" - is way too commonly seen. SomeID is a primary key and and therefore needs ONE index read and then a second read to retrieve the requested record. In this case, if the engine uses the index, the record will be retrieved just as efficiently.
But
"Like '*' & SomeID" absolutely prevents the use of an index.

 

[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?

 

[Pat Hartman]

The only reason for using a persistent connection with Jet/ACE BE's is because unless the BE is constantly in use, it gets closed automatically when the last user logs out. There is overhead with opening it and creating the .laccdb file that the persistent connection avoids.

Most of my applications use SQL Server or some other RDBMS such as DB2 or Oracle. My FE's use linked tables and bound forms and Access querydefs for almost everything. For some bulk updates I use pass through queries to avoid the overhead of a transaction. For some joins, I create views which seems to optimize them, probably because the view is compiled.

The only thing I regularly use ADO for is code that resets the seed of an autonumber/identity column. And of course that code gets executed once in a blue moon.

 

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

 

[Pat Hartman]

Let me put it this way. I ALWAYS use saved querydefs UNLESS
1. The SQL is dynamic. Taking parameters is NOT dynamic in this context. Dynamic is changing the structure of the query such as changing the Top value or using different columns as selection criteria. So a complex search form would always be powered with dynamic SQL rather than a saved querydef
2. If the querydef is slow, I look for other options. Sometimes the solution is to use Views to create the base query because that helps SQL to optimize the query process. Very rarely, the solution is a stored procedure. I can probably count on two hands the number of sp's I've created - EVER except for one single app where there was a great deal of batch processing to convert data that came from a different application. Even though the process executed over night, I went with sp's. In all cases for all the other apps they were required because of very complex reporting needs.
3. Certain bulk updates should be done with pass through queries such as dropping a table to empty it of records Or running an update that will update lots of records. Usually I will create a querydef but if the query will take parameters for selection criteria, I have to build the SQL in VBA. Once I've done that, I just send the query, I don't save it as a querydef first.

WHY do I ALWAYS use querydefs?
I'm lazy. I've been creating SQL since the 70's when DB2 (IBM) was first released. I used to dream of a tool like the QBE and that was before we even had PC's on every desk and long before Access came into my world. Why would I want to type and deal with typos and memory errors when I can point and click? But Pat, the QBE builds such ugly SQL it hurts my eyes and churns my stomach - Well don't look at it Once you've built the SQL using the QBE, why do you need to look at it as SQL? From code, you run it as a querydef. You bind the querydef to your forms and reports. If you don't look at the SQL, it won't upset you.

I'm a consultant remember so I work with too many applications at one time to ever be able to remember all the table and column names so point and click is so much easier. I get to draw lines between tables to create the joins instead of fighting with Access over punctuation. Then, sometimes, the selection criteria gets too complex to create with the QBE, so I switch to SQL view. The entire QBE is unsatisfactory as a development tool and MS is never going to fix it no matter what they promise. There is a trick with the QBE. If you save your query in SQL view, it will reopen in SQL view and so Access won't rewrite your SQL unless you switch to QBE view. To protect myself, I keep a table where I store the formatted version of SQL so if I lose my mind and switch to QBE view and Access "helps" me by rewriting my SQL, I can just go to my save table and replace it.

Efficiency. Querydefs are compiled and execution plan is calculated and saved the first time a querydef runs. Then, subsequent executions reuse that saved querydef. The time saving is small, but it all adds up if you're running a lot of queries. With embedded SQL, Access has no place to store the execution plan and so it has to calculate it every time the embedded SQL runs.
Efficiency 2. In older versions of Access, the process of calculating an execution plan tool a large amount of space and Access was/is poor at garbage collection so all that space got saved when the database closed causing the FE to bloat. In more recent versions, this problem has been eliminated so the use of embedded SQL no longer causes bloat.