PDA

View Full Version : Solved: Editing Access Records from Excel / ADO DAO



interken
06-24-2004, 11:08 PM
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
06-25-2004, 12:22 AM
Hi interken,

Welcome to VBAX!

Are you using DAO or ADO?

interken
06-25-2004, 01:09 AM
I am using DAO.

Colo
06-25-2004, 01:32 AM
Hi!
I believe the method "edit" can be used in VBA.
What is the version of Microsoft DAO xx compatibility Library?

TonyJollans
06-25-2004, 02:19 AM
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
06-25-2004, 11:10 AM
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
06-25-2004, 12:09 PM
Yes, that's critical. DAO 3.6 is Office 2K and later. In 2K the default changed from DAO to ADO.

interken
06-25-2004, 05:50 PM
Solved! The DAO.Database & DAO.Recordset was the answer. Thanks guys for all your help.

Anne Troy
06-25-2004, 06:26 PM
Cool. :)

Marked Solved.