DAO Vs ADO 1

[comnetlimited]

Here are the following codes in DAO:

'Declare Database and recordset
Dim dbsNFL As Database
Dim rstAddBook As Recordset

'Open Database and Recordset
Private Sub Form_Load()
Set dbsNFL = CurrentDb
Set rstAddBook = dbsNFL.OpenRecordset("ADDBOOK", dbOpenDynaset)
End sub

What would the above code be in ADO.

Please help,...because exactly the code above and it gives me an run time error of "Type Mismatch"

Cheers....

 

[RoyVidar]

The reason for your type mismatch is probably that access isn't sure whether you're using dao or ado.

1 - In any module, hit Tools | References and select the Microsoft DAO 3.N Library
2 - Fully qualify the declarations

[tt]dim rstAddBook as dao.recordset
dim dbsNFL as dao.database[/tt]

Roy-Vidar

 

[RoyVidar]

btw - opening an ado recordset could look something like this:

[tt]dim rs as adodb.recordset
set rs = new adodb.recordset
with rs
.activeconnection = currentproject.connection
.cursorlocation = aduseclient
.locktype = adopendynamic
.cursortype = adlockoptimistic
.open "ADDBOOK", options:=adcmdtable ' if it's a table
if not .bof and not .eof then
' blah blah
end if
.close
end with
set rs = nothing[/tt]

btw2 - in your first post, are the declarations outside any sub? Are they supposed to be globals, then rather use:

[tt]Public dbsNFL as dao.database[/tt]

- and, important, if using access 2000, if you try to bind ado recordset to a form, it's read/only

Roy-Vidar

 

[RoyVidar]

- and of course, switched the .locktype and .cursortype, but you'll see them thru the intellisense dropdown... and in the help files...

 

[comnetlimited]

Roy I am using Access 2000. Do you think its unwise for me to use ADO to update a Table or add a record to a table.

Cheers

 

[comnetlimited]

I use DAO as adviced instead...

But I got error .... "Data type Mismatch in Criteria Expression".... would it to do with ADO vs DAO...


Private Sub txtAnum_AfterUpdate()
rsAB.FindFirst "Abanum='" & txtAnum & "'"
If rsAB.NoMatch Then
If MsgBox("Add Bew Record?", vbYesNo, "Confirm&quot = vbYes Then
NewRec = True
txtAnum.Enabled = False
cmdSave.Enabled = True
End If
Else ' Address Found
'Display Address Information
With rsAB
txtAname = !ABANAME
txtOccu = !ABOCCU
txtEmpl = !ABEMPL
txtRadd = !ABRADD
txtPadd = !ABPADD
txtDjnt = !ABDJNT
End With
txtAname.SetFocus
txtAnum.Enabled = False
cmdSave.Enabled = True
cmdDelete.Enabled = True
NewRec = False
End If
End Sub

Also...

I got problem in


dbab.close
rsab.close
docmd.close

 

[RoyVidar]

Don't think the data object library has anything to do with it, rather one of the following:

* the control (txtAnum) doesn't contain any value (try testing with isnull prior to .findfirst)
* the field (Abanum) is not text, if numeric:
[tt]"Abanum=" & txtAnum[/tt]
- no qualifiers

if date:
[tt]"Abanum=#" & txtAnum & "#"[/tt]
- hash as qualifier

If the Abanum field is text, and the txtAnum is an ordinary text control, you might also check into other functions like Trim, to remove leading/trailing spaces.

Using DAO vs ADO.

It seems most of the samples of recordset manipulation on this site is DAO...

A thing to a little about, is that Access 97 and previous versions defaults to DAO (but I think you can also install and use ADO there)

In Access 2000+ versions the default library for manipulating data is ADO - BUT the form recordsets (bound forms) defaults to DAO (don't know for sure about 2003), unless you're working with ADP/SQL backend. One thing to consider, is that allthough it's possible to assign/bind an ADO recordset to an Access 2000 form, the recordset will not be updateable (read only), which is why I often prefer working with DAO on forms (bound forms). In xp, ADO recordsets can both be bound and be updateable, but because I often work with db's that need to work both on a2k and xp, I use DAO (again, for bound forms).

When using unbound forms and for other recordset manipulation, let the preferences guide. It seems DAO is "slowly dying" and ADO is to replace it. So sooner or later, we'll all have to be able to use it, so I decided to jump on that train in 2000, and use ADO for all recordset manipulation outside bound forms.

I didn't think I was doing any recommandations in the previous replies, but only trying to address why the error popped up, and giving a sample of opening an ADO recordset. Should I be persuaded to give an opinion, it would be to start learning/looking into/using ADO, because we don't know how long Microsoft is going to support DAO.

I've seen several threads here discussing ADO/DAO, one of them in the Access Developer forum thread1121-782286, you'll probaly also find other threads thru a keyword search.

Roy-Vidar

 

[comnetlimited]

Thanks Roy

 

[lachesis]

Roy: Nice commentary on ADO/DAO. cheers L.