Can't quite make the connection between Access & ADO...help!! 1

[ag90fox]

o.k. everyone can quit laughing now!! Really, I've been programming in MS Access for 10 years at one amatuer level or another (primarily for various project management positions I've had).

I constantly see people talking about connectionsets, recordsets, etc....in relation to Access BUT WITHIN Access.

Considering I just make my form, tell it what table/query to draw from; same with reports, etc... I'm lost as to why someone within Access would need to connect to the tables in its own database to make it work?!

Am I making any sense? I just can't make the connection as to why you would need to use ADO within Access....excel maybe, ACAD sure, but within itself?

Thanks for your good humor and hopefully responses.

Joe

 

[fneily]

Been programming in Access for 10 years and never had to do any background processing with tables? Hee hee hee. Oh, sorry.
Let's say you have a form, input or display, and depending on what the user inputs/changes, it will effect data in another table that is NOT bound to the form. You use DAO/ADO to access that table and make the changes, sort of in the background. You can also add/delete records in the other table. Etc.
example, a client/patient makes a visit so that Visit date is entered. However, you are also tracking the Last Visit date in another table. You want the new Visit Date to update the Last Visit Date. Instead, of opening another form, you can update that field by using DAO/ADO.

 

[PaulBricker]

ADO vs DAO" has been around since ADO was incorporated into Access. I don't think one is inherently better to use inside Access, but right now, DAO is written for the Jet database engine and is therefore likely a bit faster(although faster will be influenced by other factors even more).
What your plans are for the database play a large part in the decision. Will someone be migrating it to SQL Server at some point or do you need to access the database from an Intranet? Both of those would require ADO. If it's just a .mdb on a small network, DAO would probably always be the better way to go. But, there have been rumors for a few years that Microsoft isn't going to support Jet much longer. If that's the case, then learning ADO is a good thing. It sounds like using DAO is fine for your application, but don't ignore ADO because it has it's purpose.

Paul

 

[JoeAtWork]

Most of my applications have business rules that would be impossible to enforce if all I had to work with was the simple data-binding between form and table/query.

An example from my current work. A customer makes an order for a special product (one that is not kept in stock). Some of the things that need to happen:
1. Customer needs to be told when the product will be available to be picked up: this is based on how many lead days the supplier needs to deliver the products, and what the regular delivery days are of the supplier.
2. Entry is made in a transaction table to indicate we need to order some of this product.
3. An update is made in the QuantitySold column of the Products table.
4. If for some reason the salesperson tries to mark the item as "taken" by the customer - there is a lookup on the transaction table to see if the item was ever ordered. If not, an error message is displayed and the "taken" flag is not set (this is to prevent one customer from taking a product ordered for another).

All of the above happens on the Orders screen, but these interactions are with other tables such as Supplier, Products, Transactions, etc. I could not possibly do any of this without using DAO or ADO to open tables in the background.