How to open recordset from SQL Server in MATLAB via MS Access front-end

Question

I've got code (below) to read data from a MS Access table into MATLAB, via an ActiveX server, that works very nicely. I'm now trying to use the same code to read data from an Access linked table connected to SQL Server, but am getting an error:

Invoke Error, Dispatch Exception: Source: DAO.Database Description: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. Help File: jeterr40.chm Help Context ID: 4c5966

So, the table in question has an IDENTITY column, and I need to specify the 'dbSeeChanges' option with OpenRecordset. My question is, how do I specify this option? I can see plenty of examples online of how to do it in VBA, but none that would be MATLAB-compatible. Here's my code:

% Setup environment
app = 'Access.Application';
DBAddress = 'O:\testData.accdb';

% Load an Activex server for Access
try
    svr = actxGetRunningServer(app);
catch err
    svr = actxserver(app);
end

% Load the required database file
accessDB = svr.DBEngine.OpenDatabase(DBAddress);

% Query the database for the required records
sql_query = 'SELECT * FROM dbo_PatientMeasurementResults;';
rs = accessDB.OpenRecordset(sql_query);

I've tried the obvious, but this does not work:

rs = accessDB.OpenRecordset(sql_query,'dbSeeChanges');

dbSeeChanges is a number (Const dbSeeChanges = 512 (&H200)). Have you tried rs = accessDB.OpenRecordset(sql_querry, dbSeeChanges); ? (or with the constant 512) — Andre
– Andre, Commented Nov 9, 2016 at 14:58
Thanks @Andre. dbSeeChanges = 512 gave me an Invoke Error (Description: Invalid argument.) error, but interestingly 'dbSeeChanges = 4 fixed the problem. Haven't got to the bottom yet of why that worked, however! — 4Oh4
– 4Oh4, Commented Nov 10, 2016 at 9:40

marc_s · Accepted Answer · 2022-01-20 21:04:13Z

1

Silly me, dbSeeChanges is a value for the Options parameter, which is the third, not the second parameter of OpenRecordset:

Database.OpenRecordset Method (DAO)

What happened now is: Const dbOpenSnapshot = 4 - you opened a "Snapshot" recordset, which is read-only. So dbSeeChanges isn't needed.

If you want to only read from the recordset, than this is indeed the best option (but you should change your constant in your code to avoid future confusion).

To open an editable recordset, use

Const dbOpenDynaset = 2
Const dbSeeChanges = 512
rs = accessDB.OpenRecordset(sql_query, dbOpenDynaset, dbSeeChanges);

edited Jan 20, 2022 at 21:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 10, 2016 at 9:52

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to open recordset from SQL Server in MATLAB via MS Access front-end

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related