How do I use excel 2010 vba to update Access 2010 table

[jscriptor09]

Hi Folks

I have office 2010. In my Excel Macro, I added reference to DAO 3.6 object library. My goal is to be able to update a record in a table or add a record to the end of the table.

I et an error as soon as I reach this line

Dim db As Database, rs As Recordset, r As Long
'
Set db = OpenDatabase("C:\Documents and Settings\some.accdb")


The error is un-recognize database format

I need to do this via vba as opposed to manual. Any help with example on how to access 2010 db and update records will be greatly appreciated.

Mike

 

[Banana]

I would think that because you're referencing DAO 3.6, it is not able to recognize the newer ACCDB file format. Either use a MDB file format or reference instead "Access database engine object nn.n object library", where nn.n would be 14.0 for Access 2010 or 12 for 2007. Note that this is still DAO, albeit with a different name (DAO used to be a Windows component but since Access 2007, it's not privatized to Access and hence the name change from DAO to ACEDAO but it's still the same library).

You can still use the same DAO 3.6 with ACEDAO library (as well do additional things that's introduced since 2007 but I don't tend to find it needed at least for the moment) as well open ACCDB file format.

HTH.

 

[boblarson]

"Access database engine object nn.n object library", where nn.n would be 14.0 for Access 2010 or 12 for 2007.

Just so it is easier to find - it is

Microsoft Office Access 14.0 Database Engine Object Library

or

Microsoft Office Access 12.0 Database Engine Object Library