Update a field in Access from Excel

[AlexanderBB]

Hi.. run into a problem. Although my code did work with an .accdb file, attempting this with a particular file results in
The database you are trying to open requires a newer version of Microsoft Access.

Function getConn() As ADODB.Connection
    Dim rv As New ADODB.Connection
    Dim strConn As String
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = O:\DB001.accdb"
    rv.Open strConn
    Set getConn = rv
End Function

I guess this means there are different types of accdb. I also tried DAO but the same problem.
Is there a solution? I need to update one record in DB001 which triggers a further event (in Access).
Excel is Office 2010 and Access Office 2019, and O: is mapped drive on LAN.

 

[Micron]

I have not experienced this error but

I guess this means there are different types of accdb.

no, there is not (per se). However, there are differences between Access versions and if the one you are trying to open uses advanced features, an older version will not be able to open it. You should also look into provider connection strings that match your versions - you may need to use OLDB.16.0 and not 12.0

 

[AlexanderBB]

I'd like to try OLDB 16 but can't find a 32bit download link that I'm sure is right. Would you have one ?

 

[Micron]

Sorry, no. I've had little use for ADO over the years. I can suggest that you can push/pull data from one app to the other using what was (is?) called automation. For that I'd suggest you go here.

Your thread title suggests you want to update Access table(s) from Excel as you posted in Excel questions, but I think you're trying to update an Access table from another Access db and there might be other/easier ways than ADO. One might be the automation link I provided. Another might be to link the remote table in your active db so that you have access to it. Another might be a query that accesses the remote db using the IN operator and the full path to the remote db. However, I cannot recall if that works for action queries (queries that append, delete or update) or just Select queries but you could check it out. The syntax for Select would be like

SELECT Field1, Field2... FROM tblTheTable IN ("C:\Databases\...\TheDatabase.accdb")

I guess you could also try that with update, append or delete.

 

[AlexanderBB]

Thanks for the info and some interesting stuff in that link. It is Export Excel to Access that's wanted and the target db is the wrong Format. My routine works with some dbs but I'm not sure why the difference. I had thought it was just the mdb or accdb extension, The idea of trying Ace v16 instead of 12 was worth a try, but I still can't find it or even a filename for it.
You never know what you can trust from Google... or what malware will show up as a result! They usually want you to log in or sign up etc.
So I can either try using a text file, or write a compliant Access file (a copy of one that works) and the target db links to that.