Connecting from Excel

[Middlemarch]

I'd like to work with an Access table in Excel, and normally this works fine. But the target db is Office 2019 and Excel 2010.
It's going to an error concerning the wrong version or format. My connection code is

 Set cnn = New ADODB.Connection
    Set rx = New ADODB.Recordset
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBPath
    cnn.Open
    rx.Open "Select * from TableAA", cnn, adOpenStatic, adLockReadOnly
    rx.MoveFirst

Also tried a DAO connection but struck the same problem.


Is there anything I can do to get this working?

[madpiet]

I'd like to work with an Access table in Excel, and normally this works fine. But the target db is Office 2019 and Excel 2010.
It's going to an error concerning the wrong version or format. My connection code is

 Set cnn = New ADODB.Connection
    Set rx = New ADODB.Recordset
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBPath
    cnn.Open
    rx.Open "Select * from TableAA", cnn, adOpenStatic, adLockReadOnly
    rx.MoveFirst

Also tried a DAO connection but struck the same problem.
Is there anything I can do to get this working?

Excel 2010??? But what about the 2019 or whatever version of Excel?
I was going to say that you could use PowerQuery to do this and it's about as hard as falling down. Specify Access as source, choose database, select table or query OR push a button and copy & paste in a SQL statement.

[Middlemarch]

I have Office 2019 on one computer and Office 2010 on another. I use mapped drives over LAN.
The problem is a newer Access, I think. Can I connect to it with some change to my connection string/method?
Sorry I don't follow your last sentence. I want to use this in code I have... not sure if PowerQuery (whatever that is) would fit.

[madpiet]

PowerQuery basically lets you import and transform data from whatever source. (well, mostly). I almost never use connected tables in Access unless I'm connecting to something where I can't use PowerQuery.

Can't remember about Office 2010 anymore. I think some versions had Powerquery, but I think it was the first version that had it. (Maybe as an add-in). I've been using Access since like 1997, so I've learned a bunch of VBA, but the big problem with doing data manipulation (and especially transformation) in Access is that it uses strict typing which is a hassle when you want to do some weird transformations. (like unpivot columns etc).

If you're using Office 2019 to read everything, then I'd skip Access completely and do all the stuff in PowerQuery in Excel. Unpopular opinion maybe, but Powerquery can run circles around VBA for data transformation.

oh, about "using the code you have"... I'm not sure you can "address" PowerQuery using VBA. VBA has been around for like 30 years, and PowerQuery for maybe 15. But my point was that you don't need VBA for this at all. You can just tell Excel where to get the data, and then you can transform it (if you wish), and then just use it. But since you're the one doing it, it's your call. Do whatever you find easier.

[Middlemarch]

I had a bit of a look at PowerQuery but dunno... I'm more happy with what's familiar.
If there is a way to connect with VBA I'd like to use that. At the moment I'm using a third db that both can read/write to. Works but is messy.

[madpiet]

What problem are you trying to solve?

[Middlemarch]

Updating fields in Access from Columns in Excel
One connected, create and update recordset.

[madpiet]

Easiest way might be to create a linked table to the Excel file, run the update query. (I don't use recordsets unless I really need them).

That's how I'd do it.

Why would you even use a recordset in this instance? Only do row by row stuff if you absolutely have no other choice. Try that on a table with a couple million rows.

[madpiet]

Updating fields in Access from Columns in Excel
One connected, create and update recordset.

It's not that difficult. No need for recordset messiness.

All you need to do is to create a linked table to the your Excel file, and then you can create an update query like this...

UPDATE Sheet1 
    INNER JOIN Products_Access
    ON Sheet1.ProductID = Products_Access.ProductID 
SET Products_Access.UnitCost = [Sheet1].[UnitCost];

Save recordsets for when you really gotta have them. (When you have to process a table, or a subset of it, one record at a time.) This just isn't one of those cases.

[CJ_London]

Can I connect to it with some change to my connection string/method?

you would need an access 2019 ‘driver’. Later versions can connect to earlier versions but not the other way round (no forward compatibility). I doubt excel 2010 has that - check your references in the excel vba, you might be able to copy the relevant 2019 file from your 2019 machine- might work, might not

since it is only the tables and I assume the db is split, move your table back end to a 2010 version. Also assumes you are not using datatypes (such as bigint) which do not exist in 2010.

or perhaps time to upgrade from 2010 to at least 2016 where the library should be the same