Converting DAO recordset to ADO

[Mark Dudley]

I have a database which started life in Access 2, and which has grown over time. It is now in Access 2000, but is now far too cumbersome and belatedly I am trying to split it into Front and Back ends. Unfortunately a lot of the code breaks when I split it, and the problem seems to be that all of the code uses DAO.

So I decided to rewrite it using ADO. The problem I now have is that certain of the methods no longer seem to work.

eg. The old database had:

Dim Msg, Style, Title, Help, Ctxt, Response, MyValue, MyName
Dim Db As Database, portset
Set Db = DBEngine.Workspaces(0).Databases(0)

Set portset = Db.OpenRecordset("tblStaffInOut")

portset.Index = "Initials"
portset.Seek "=", LGIN
If portset.NoMatch Then
MsgBox "Please use a valid Log In"
Else
MyName = portset!Name
If Time < #12:00:00 PM# Then
MsgBox "Good morning " & MyName & ". It is " & Now & ". You are now logged in."
Else
MsgBox "Good afternoon " & MyName & ". It is " & Now & ". You are now logged in."
End If
portset.Edit
portset![In/Out] = "In"
portset![Back] = Null
portset.Update
End If

I have changed this to:

Dim portset As New ADODB.Recordset
portset.Open "tblStaffInOut", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

portset.Index = "Initials"
portset.Seek "=", LGIN

If portset.NoMatch Then
MsgBox "Please use a valid Log In"
...

But the code stops at "If portset.NoMatch" with a compile error saying "Method or data member not found". Is this no longer allowed?

 

[namliam]

Try doing a SEARCH on either DAO or ADO or ADO vs DAO (or reverse)
Try searching for disambiguate. It is even a FAQ i believe.....

Yes i am making it easy on myself

Also you should not use this:
Dim Msg, Style, Title, Help, Ctxt, Response, MyValue, MyName

Allways add for each variable "As string" or something

 

[Mark Dudley]

Thanks for your help - I did try to find as much as I could myself, but never thought to search for 'disambiguate'!!!!!!

 

[Pat Hartman]

Unfortunately a lot of the code breaks when I split it, and the problem seems to be that all of the code uses DAO.

Converting to ADO won't solve the problem. Leave the DAO alone (but disambiguate it by prefixing all DAO objects with DAO.). The problem is probably that your code is using Seek and that won't work against a linked table. Eliminate the Seek. Use a query with a parameter to locate the record the seek is looking for. Then open the recordset made by the query rather than opening the table directly.

FYI, I would replace any Find's with the same technique - a query with paramters to return only the record(s) being sought.