MATLAB Answers

Parsing the Ms access data base

9 views (last 30 days)
I have a database with two tables. I tried to extract the data from the tables and i was able to extract also using the command below,
rs=invoke(hopen,'OpenRecordset','Data');
fieldlist=get(rs,'Fields');
ncols=get(fieldlist,'Count');
for c=1:double(ncols)
fields{c} = get(fieldlist,'Item',c-1);
resultset.columnnames{c} = get(fields{c},'Name');
end;
nrecs=0;
while get(rs,'EOF') == 0
nrecs = nrecs + 1;
for c=1:ncols
resultset.data{nrecs,c} = get(fields{c},'Value');
end;
invoke(rs,'MoveNext');
end
Only problem is when I have a multiple values in the cell i can able to extract only first element in the cell.
For example,
in the above figure, the column carsSelected consists of multiple value in the cell. In this case i cannot able to extract all the elements in the cell? How can I do this?
Thanks a lot
  4 Comments

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 28 Mar 2017
Well, I learnt something today. Access does allow multivalued fields. However, it does so by hiding much of the machinery that you'd use in a normal database design (that is it hides the table modeling the relationship together with the queries required to retrieve the records).
It looks like you're using DAO to connect to the database. When I first tried, using the older ADO technology, the value property of the field is the display string (i.e. '1; 2; 3; 4'). However, with the newer DAO, according to MSDN the value property of a multivalued field of a recordset is itself a recordset. So to retrieve all the values, you'll need another loop iterating over this child recordset.
So I assume this will work:
for c=1:ncols
if fields{c}.IsComplex %true for multivalued
rs = fields{c}.Value;
value = [];
rs.MoveFirst;
while ~rs.EOF
subfield = get(rs.Fields, 'Item', 0);
value = [value, subfield.Value];
end
resulset.data{nrecs, c} = value;
else
resultset.data{nrecs,c} = fields{c}.Value;
end
end
Note that you can replace most of the get(...) access by direct dotted access as I've done above. The exception appears to be the Item property that matlab refuses to see other than with get.
  2 Comments
Guillaume
Guillaume on 29 Mar 2017
Do'h! Yes of course, you need to move through the records.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!