MATLAB: How to create a new table in a Microsoft Access database and import data to it using Matlab

Question

Hello,

I am trying to export test data into an existing Access database, but I am having difficulty finding the correct commands to achieve this goal. I've been searching up and down the internet to try and find an example to go off of, but a lot of the info refers to a table already existing and simply inserting data into it.

The data I have for this instance is a 14×13 cell where the first column are names and the first row contains characteristics, everything else is the data. Another note, I expect different tests to very in their size so I'm trying to avoid anything hard coded where it can't pull NxM types of data, if that makes sense. I want to store everything in the database and I want it to look just as it does (format and what not) when I look at it in Matlab.

So far I know how to make the connection to the database, I just need someone to give me a hint or a tip to export this data. Let me know if you need more information. I'll also continue to work on it and if I figure anything out I'll keep this post updated.

Thank you in advance,

L

Answer 1

Access SQL syntax documentation is arguably one of the worst documentation produced by Microsoft (who can also produce some of the best documentation). It's full of errors and missing information.

Nonetheless, a few years back I managed to piece together how to create databases, tables, parameter queries, etc. in access. Note that I don't use / have the database toolbox. I communicate directly with access using ADO. Nonetheless, the following function (which I wrote a few years back) should show you the SQL syntax for a CREATE TABLE query. It's been tailored to my needs so does not support all types of columns / constraints

       function CreateTable(this, tabledefinition)
            %CREATETABLE Create an access 2010 table.
            %   CreateTable(tabledefinition)
            %       tabledefinition:    {tablename columndefinition+} (cell array).
            %       tablename:          The name of the table.
            %       columndefinition:   {columnname columnntype constraint*} (cell array)
            %       columnname:         The name of the column (string).
            %       columntype:         'typename[:typesize]' (string).
            %       typename:           'auto' | 'int' | 'double' | 'longtext' | 'text'
            %       typesize:           Only applies to 'text', size of column (default is access default).
            %       constraint:         'constraintname[:constraintoptions]'
            %       constraintname:     'primary' | 'required' | 'reference'
            %       constraintoption:   Required for 'foreignkey', Table[(field)]
            sql = [];
            tablename = tabledefinition{1};
            for iter = tabledefinition(2:end)
                column = iter{1};   %iteration over cell arrays always return a single-cell array holding the content of the iterator
                columnname = column{1};
                [columntype, columnsize] = SplitAtColon(column{2});
                switch columntype
                    case 'auto'
                        sqltype = 'AUTOINCREMENT';
                    case 'int'
                        sqltype = 'INTEGER';
                    case 'double'
                        sqltype = 'DOUBLE';
                    case 'longtext'
                        sqltype = 'TEXT';
                    case 'text'
                        if isempty(columnsize)
                            sqltype = 'CHAR';
                        else
                            sqltype = ['CHAR(' columnsize ')'];
                        end
                    otherwise
                            error('unknown column type: %s', column{2});
                end
                columnsql = ['[' columnname '] ' sqltype];
                for constraint = column(3:end)
                    [constraintname, constraintoption] = SplitAtColon(constraint{1});
                    switch constraintname
                        case 'primary'
                            columnsql = [columnsql ' PRIMARY KEY']; %#ok<AGROW>



                        case 'required'
                            columnsql = [columnsql ' NOT NULL']; %#ok<AGROW>
                        case 'reference'
                            columnsql = [columnsql ' REFERENCES [' constraintoption '] ON UPDATE CASCADE ON DELETE CASCADE']; %#ok<AGROW>
                        case 'indexed'
                        %    columnsql = [columnsql ' INDEXED']; %#ok<AGROW> %is this legal access ddl? No it isn't
                            warning('indexed constraint not implemented'); %#ok<WNTAG> warning is only temporary anyway.
                        otherwise
                            error('invalid constraint: %s', constraint{1});
                    end
                end
                if isempty(sql)
                    sql = columnsql;
                else
                    sql = [sql ', ' columnsql]; %#ok<AGROW>
                end
            end
            sql = ['CREATE TABLE [' tablename '] (' sql ')'];
            this.Connection.Execute(sql);
        end