VBA From DAO to ADO (1 Viewer)

Hello,
I am a VBA programmer for 20+ years but always in DAO and now I need to come up to speed on the ADO. Does anyone have any suggestions for a high level VB programmers guide to the ADO?:banghead:

See links by Allen Browne
http://allenbrowne.com/func-ADO.html
http://allenbrowne.com/func-ADOX.html

I may have it wrong, but from what I have read, ADO was a “fling”, a concept that MS explored but decided to forego.

AC2010 and beyond are actually designed to work with DAO.

But, as I said, it is entirely possible I could be mistaken.

I know some programmers use ADO regularly.

AFAIK, I've never chosen to use ADO in 20+ years working with Access

I have occasionally taken over ADO code written by others and found it much harder to work with...but that might be just my lack of experience with it

Gent, I recall as you do that ADO isn't "preferred" anymore. I also recall that there was a time when code would "break" when migrating from lower to higher versions of Access because the default for the higher version was ADO and the lower was DAO. So any code that contained unqualified recordsets had a chance to be qualified incorrectly. The simplest solution that didn't require global editing was to assure that the DAO library appeared BEFORE the ADO library in the references list, since search order mattered.

OF COURSE one should always properly qualify declarations if there is ANY chance of any doubt. Trusting library reference order was not a secure method, just a stop-gap. However, that change of defaults away from DAO caught a lot of people - including me.

Anyway, I can't recall the dates, but the default database and recordset type have been changed back to DAO, as though the ADO "experiment" was not working correctly. I will hazard a guess that it had to do with the Access Web initiatives, where ADO is more compatible with web ops but DAO is better for desktop databases. Maybe I misremember the details, but I don't think I do. (And I won't tell you what spell check does to "misremember" - but it IS funny.)

IIUC, ADO was mostly useful for ODBC connections where you may need to swap out file locations. Only time I remember talking to someone about this it was because they needed to store a table locally of "Sites", then select the correct one to connect to when they went to client location.

That was from back in the late 90's, so there may have been other issues at play.

dbStamps, here is an excellant video by Steve Bishop that explains both concepts in more detail then you will ever need, but in a way non-programmers like me can understand.
https://youtu.be/ouSVBL6fCng

Not entirely sure what he means by “stored procedures” however. I always thought compiled modules were stored procedures but from what SB is saying, they are not.

The more I learn, the less I know...

@our illustrious PC member...
Stored procedures are very useful, they are server side code that can be triggered to do things for you with your database when conditions are met.

Ever wish that, when records are changed or entered, the user who did it and when it was done would be recorded to an audit file the user can't see?

Ever wish that when a new sales entry is put into the system you get another entry put in to a separate table that increases the requirement for scheduling to accomplish your new project?

Ever wish that when a bank card or cheque was declined you'd get an email automatically sent to the customer?

These are some things that stored procedures can do, and have it done on the server side so you don't have to worry about user rights or users having permissions. They can also be triggered based on dates, number of users, what have you.

As the code processes on the server along with the data files you don't have to worry near as much about network bandwidth of end users messing things up.

After reviewing the video, it appears that some of the ADO stuff is being unofficially deprecated because of issues with OLEDB as opposed to ODBC connections.

The biggie for ADO is the ability to use server-side recordset cursors because this is where you let the back end (active) server do the work and return a relatively shorter recordset. With DAO, you have to bring everything to the client (workstation) and run the query from there. I.e. DAO requires network loading. ADO requires BE server loading. For web-based databases, you need ADO or its web equivalent, since the web page runs on the FE/Workstation and gets data from the BE.

Mark_,

All these features you mention can be done with standard Modules. If I am understanding you and Doc correctly, the main difference is that it is all done on the server; speed and efficiency with less overhead for the user to observe.

If I have that right, then I thank you for breaking it down. If not, I will stand here with my signature “dull eyed” expression while you try again...

NG,
Yes, on the server side. This does have some other features that are not always apparent. If you do not want an end user to have any way of telling if a table even exists or not, stored procedures can do updates without end user knowledge.

For applications that have fiscal responsibilities, this can do wonders. If you are trying to make sure your customer (company) is safeguarded against its less than honest employees this can allow you to do tracking and auditing without the end user even being able to tell it happens.

Likewise as the processing is done server side it greatly reduces network requirements.

While you CAN do the same types of things in an access FE, doing it server side often is far more secure and (depending on back end) often easier.

When you start getting into network security and rights issues, stored procedures can solve issues you otherwise cannot address in access. As the server will have rights on a network that the users won't, you can have the server do things the users would not be allowed to.

For you, if it is properly vetted, stored procedures may even be able to switch to red network cables and report back on the low side. B-)

The_Doc_Man said:

After reviewing the video, it appears that some of the ADO stuff is being unofficially deprecated because of issues with OLEDB as opposed to ODBC connections.

Click to expand...

The OleDB Provider for Microsoft SQL-Server was deprecated, so you could assume ADO was on its (very long) way out. - This changed fundamentally about 3 months ago when Microsoft announced a new OleDB Provider for SQL-Server.

The_Doc_Man said:

With DAO, you have to bring everything to the client (workstation) and run the query from there. I.e. DAO requires network loading. ADO requires BE server loading.

Click to expand...

This is incorrect.
First, it has nothing to do with DAO vs. ADO, but with linked tables vs. direct queries. DAO had ODBC Direct Workspaces (removed in current versions) that operated very similarly to ADO. You still got DAO PassThrough Queries that send the query to the server and only return the result.

Second, even with linked tables, the Jet/Ace-Engine will pass through as much of the query as possible to the server for processing. Only parts of the query that cannot be processed on the server, e.g. local (VBA) function calls, will be processed on the client.
This is done by the Jet/Ace-DB-Engine and there is no difference if you connect to the local DB-Engine via DAO or ADO (not recommended).

Ok, thank you everyone. My goal had been to achieve some type of ODBC connection with my online PHP/MySql-based application. I was able to achieve limited ODBC by uploading and downloading whole tables. When I went to do more, I found that it would require ADO. The thought of throwing out all my previously developed DAO routines etc was not one I looked forward to. I thought learning ADO might at least benefit me as I mistakenly thought I would at least be "catching" up.
Thanks to all of your advice, I will not chase Microsoft down their rabbit hole of dead end language "progress." Since I did not really need real-time integration, I will happily stick with DAO and use tried and tried export/import routines. Thank you all again!!!

This is why I love creating applications using AJAX & PHP with MySql. All the programming can be done out of sight making it much more secure and flexible.

I keep wondering if I need to start looking into .Net. Rumor has it this it’s the future.

I have enough trouble with VBA now, not enough room in the brain for another language.

It seems my previous comment about never using ADO was actually incorrect.

I've just found several examples which I wrote several years ago mostly for connecting to external sources.
For example the code below is used to test whether SQL Server connection is available, and if not, the database is closed 'gracefully'


The question is I suppose whether I could have done that just as easily using DAO?

Sonic8, you are correct - I over-generalized, maybe a bit too much.

Indeed, a DAO recordset DOES have a pass-thru mode that is indispensable for server-side action. And if you are lucky enough to have one of those server-side setups to exploit, great! However, most of DAO is client-side oriented. And as you yourself pointed out, ODBC Direct is in the category of "ain't there no more."

Perhaps I should have clarified that DAO is better for native Access databases even with split FE/BE, since in such cases the BE is passive, not active and there ARE no ways to get the server to do more than pass file content.

As to whether JET/ACE gives the active BE things to do, I believe that is more in the nature of the SQL that you write, since it is possible to write intractable SQL in any system. (I've cleaned up too many messes on that subject.) But if you say that the DAO and ACE combination can still result in sending queries to the active BE, I'll believe you. I attribute that to product improvement over the years, because that wasn't always the case in the earliest versions of Access. I knew that if the tables in question were indexed, the queries were going to use the indexes to select items to return, so there would be that much of an assist. But I don't recall it being more than that.

I'll toss in another couple of cents worth, though. ADO is "ActiveX Data Objects" and at least some of the deprecation of ADO came about because "ActiveX-anything" has a REALLY bad name in some military circles. Part of that is bad PR, because early hackers would "play with" your active X to do stupid things to your screens - and worse.

Microsoft REALLY wants to pander to its biggest customers. Some years ago, there was a Navy/Marine Corps Intranet (NMCI) contract in which MS and some lucky PC vendor would get to provide new desktop units in 10,000-lot quantities, more than one lot, and with tech-refresh (translate - automatic upgrade) every 3 years. Who would NOT pay attention to customers operating in those quantities? And that was just Navy & Marine Corps. The Army and Air Force each had their own versions of same.

For what it is worth, I believe the Navy has finally decided that ADO isn't the anathema that they first thought, or at least it is not as bad as some other ActiveX situations. They were heading that way when I was preparing to retire. Gent, you are still with the Navy. Have they relented on ActiveX - at least for ADO - in the last couple of years? Or has that ever come up with you?