Clueless: Why use ADO in MS Access?

[ag90fox]

I've been building Access97 programs for my office for about 8 years using a split front-end/back-end setup soley using Access. I'm intrigued by ADO and all the discussions regarding it, but am wondering if learning it would be worth my while?

I can link to my back end using the add-in in Access, and create either stand alone queries, or queries built into forms/reports, so off-hand I'm not sure as to how ADO would help me.

Any insight into what ADO offers beyond the normal Access data linking, or how learning ADO might multiply my capabilities would be appreciated.


Thanks

 

[LittleSmudge]

ADO in versions 2k onwards replaces DAO in version 2 to 97

All the things that you've done in DAO - ( like working with recordsets ) you'll need to start converting to ADO.

This is all part of MicroSoft's drive towards the commonality of the ActiveX architecture. Whilst you can import the resourses into Access2000 databases to make your old DAO code work, the use of DAO is not recommened ( and activly discouraged ) by MS.

ADO doesn't necessarily get rid of 'BackEnd' and 'FrontEnd' database splits though.
Horses for courses and all that!


G LS

 

[ag90fox]

I've seen that '2000 uses ADO, not DAO, but I've never even really used DAO that I know of. I do my queries in the Access windows, some VBA for form controls, etc... but no recordsets, connections, addnew.....any of that.

Am still wondering if using ADO (in '2000) is more efficient that using the linked table capability, etc....

 

[LittleSmudge]

Well I use lots of 'linked tables' and I also use a lot of ADO ( and lots of DAO in '97 databases which I still work on a lot )

In my mind they do completely different things.
The DataStore database holds the tables in one Access file.
The FrontEnd holds the Forms, Reports etc
In the FrontEnd on an expenses and time logging database for example I would open a recordset to look at the expenses for a person for the month by

Dim rst as New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblExpenses WHERE EmployeeRef = " & cboEmployeeLookup & " AND Month(ExpenceDate) = " & MonthValue

then cycle through the records doing actions according to the data in the fields.
The "CurrentProject.Connection" tells the ADO that the tblExpenses is the table in the FrontEnd ( the local end of the link ) your code doesn't need to know about the existance of the DataStore.


All in all you can do some fairly clever stuff with DAO and now ADO but if you can work on Access 2000 & beyond then start learning ADO because DAO is obsolete.
However, if you're stuck for now on Access97 then take some time to learn about DAO and the opportunities it gives you. The techniques you pick up will carry over to a large extent.

When I first started using ADO I simply build up a set of 'conversion' packs and worked on the basis of "This is how I'd have done it in DAO" so the ADO equivalent is ".. .. .. "


G LS

 

[jgoodman00]

I agree with all of the above. In addition to this, there are many things which you can do in ADO which cannot be done via queries/linked tables etc.

A good example of ADO is that of handling a combo-boxes not
in list event. Using ADO it is very simple to add the newly entered selection (after asking the user if they are sure). This can be quite tricky using other methods of data manipulation.


Private Sub StewardID_NotInList(NewData As String, Response As Integer)
On Error GoTo Steward_Err
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
If MsgBox("Do you want to add '" & NewData & "' to the items in the control?", vbOKCancel, "Add new name?&quot = vbOK Then
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open ("tblLUPSteward&quot, cnn, , adLockPessimistic
On Error Resume Next
rst.AddNew
rst.Fields("Steward&quot = NewData
rst.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

Steward_Exit:
Exit Sub

Steward_Err:
MsgBox Error$
Resume Steward_Exit

End Sub

Another excellent use of ADO is direct manipulation & connection to SQL server (or MSDE) databases. Using ADO you can access both tables & stored procedures. For example, passing multiple parameters into a stored procedure can prove quite tricky. Using ADO this becomes much simpler, using the following methods & collections:
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryGenerateResults"
cmd.Parameters("@HierarchyID&quot = Me.txtAssetCriteria
cmd.Parameters("@LocationID&quot = Me.txtBRSCriteria
cmd.Parameters("@Priority&quot = Me.txtPriorityCriteria


I believe queries also execute more quickly using the querydef object. However I am not familiar with this object & its methods...

There are many microsoft articles on ADO/DAO & their uses. Generally, if I want to manipulate data on a row-level basis I use ADO. If I want to manipulate data at table-level, I look to using stored procedures & views (SQL server)...


HTH
James Goodman

 

[MichaelRed]

DAO (and it's poor step sister RDO) are older 'technologies'. MS has a recurring theme in its products - retiring the old horses in a somewhat forced manner.

At some point, the old approaches will simply become 'incompatible' with an object (program?) which you are asked to interface with. You will be drug into the 'next generation' theme park -willing or not. As a minor illustration, Ms. A. ver 2K (and onward) use ADO as the default, so when you are drug into 2K, most of your wxisting data access code will simply STOP. You will (if not prepared) be rushing through the conversion (probably needing to add a reference to the DAO library, and doing the global search / Replace (Database to DAO.Datebase ... ) - all the while still NOT learning the 'new' technology. Shortly afterwards, you will become dis-satisfied with your position and find that your value in the marketplace is near ZERO - because you are not (bussword) current in the technology

Today's world expects a continuious educational process. Quit learning and starve is the defacto motto of the 21st century. Do whatever suits you but be prepared for the results of your actions (or lack thereof).


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[vr]

In my opinion....linking tables works fine if the volume of records is small. I normally use ADO because of the improved performance. It can handle large volumes much faster than DAO or linked tables.