Editing Access Records from Excel / ADO DAO

[interken]

I have an Excel sheet that through a form, accesses an Access database. It also has a command button that updates records with data from the sheet. In my VBA code I'm am trying to do a 'rs.edit' but the intellisense doesn't have it listed and I get a 'Method or datamember not found' error when I run the command anyway. I have checked my references and am pretty sure I have all that I need. All my other database functions (like rs.addnew) work fine. Does VBA not support editing records from a database? Or am I forgetting something?

[TonyJollans]

Hi interken,

Welcome to VBAX!

Are you using DAO or ADO?

[interken]

I am using DAO.

[Colo]

Hi!
I believe the method "edit" can be used in VBA.
What is the version of Microsoft DAO xx compatibility Library?

[TonyJollans]

The default depends on your Office version, but if you are explicit, the DAO Edit Method should work.

The Reference you need is Microsoft DAO 3.x Object Library, ..

.. and the code should be something like ..

Dim db as DAO.Database 
Dim rs as DAO.Recordset
':
':
Set db = DAO.Opendatabase("Drive:\Path\Name.mdb")
Set rs = db.Openrecordset("Name"),....)
':
':
rs.Edit
rs!Field = something
rs.Update
':
':
rs.Close
Set rs = Nothing
Set db = Nothing

[interken]

I have the Microsoft DAO 3.6 Object Library. But I have been dimensioning my db variable as 'Database' not 'DAO.Database' would that make a difference?

[TonyJollans]

Yes, that's critical. DAO 3.6 is Office 2K and later. In 2K the default changed from DAO to ADO.

[interken]

Solved! The DAO.Database & DAO.Recordset was the answer. Thanks guys for all your help.

[Anne Troy]

Cool.

Marked Solved.