Edit Method in VB

[Guest]

I used to edit records in a module by using a tbl.Edit method. I created a
new database in Access XP and tried to use the Edit method, but it doesn't
recognize it. I checked the Microsoft DAO 3.6 library under Tools,
References, but it still doesn't work. I see a reference to the Edit Method
in the Visual Basic Help, complete with an example, but it won't work in my
database. Is there some other library that needs to be checked? If not, is
there an alternate method that can be used to edit a record in a recordset?

 

[Dirk Goldgar]

I used to edit records in a module by using a tbl.Edit method. I
created a new database in Access XP and tried to use the Edit method,
but it doesn't recognize it. I checked the Microsoft DAO 3.6 library
under Tools, References, but it still doesn't work. I see a
reference to the Edit Method in the Visual Basic Help, complete with
an example, but it won't work in my database. Is there some other
library that needs to be checked? If not, is there an alternate
method that can be used to edit a record in a recordset?

Do you do something like ...

Dim tbl As Recordset
' ...
Set tbl = CurrentDb.OpenRecordset("Some Table or Query")
' ...
tbl.Edit

.... and the VB editor tells you there is no such method?

If so, it's probably not a matter of another library that needs to be
checked, but rather one that needs to be *unchecked*. Specifically,
either change your Dim statement to

Dim tbl As DAO.Recordset

or open the References dialog again and remove the check mark next to
Microsoft ActiveX Data Objects 2.x Library. The ADO library also
defines a Recordset object, which doesn't have an Edit method, and
Access was thinking that was the type of recordset you were declaring.

 

[Guest]

Dirk,

Yes, that worked! How the problem came up is that I took a database that
someone else had created in Access 2000 and copied it to my computer, which
has Access XP, to work on it. I left it in 2000 format, but was getting the
error I mentioned. I had set up my program as you had outlined below.

After I posted my question, I also discovered that when I compiled the
database, I got an error message of "Data type mismatch" on my variable "tbl"
defined as a recordset. From what you said, it sounds like if I had defined
it as a DAO.Recordset, I wouldn't have gotten this problem, either. I
decided just to uncheck the ADO 2.x library because ultimately, this database
will be going back to the user running Access 2000, where that library was
unchecked to begin with.

One final question: Will I be "missing out" on any nifty programing tricks
by not using that library?

 

[Dirk Goldgar]

"> Yes, that worked! How the problem came up is that I took a database

that someone else had created in Access 2000 and copied it to my
computer, which has Access XP, to work on it. I left it in 2000
format, but was getting the error I mentioned. I had set up my
program as you had outlined below.

After I posted my question, I also discovered that when I compiled the
database, I got an error message of "Data type mismatch" on my
variable "tbl" defined as a recordset. From what you said, it sounds
like if I had defined it as a DAO.Recordset, I wouldn't have gotten
this problem, either. I decided just to uncheck the ADO 2.x library
because ultimately, this database will be going back to the user
running Access 2000, where that library was unchecked to begin with.

Access 2000 also normally supplies the ADO reference and not DAO by
default, and the reference settings travel with the database, so what
you describe is probably not *exactly* what happened. However, I think
you did the right thing by unchecking the ADO library, so long as the
database is not using it. Still, I prefer to explicitly qualify all my
declarations of objects from either of these libraries, just in case
something happens to the references.

One final question: Will I be "missing out" on any nifty programing
tricks by not using that library?

No. While ADO is simplified and optimized for accessing a wide variety
of database types, DAO is optimized for Jet (the database engine used
for the Access .mdb file), and provides functionality that ADO does not.
So if you're working with a Jet database, DAO is the best way to go. It
looked for a while as though Microsoft was trying to get rid of DAO and
Jet in favor of ADO and SQL Server, but that ill-advised movement has
ended. In Access 2003, the default DAO reference is back.