How can I create a new table in a Microsoft Access database and import data to it using Matlab?

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 14x13 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

[Guillaume]

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

[L]

Hello Guillaume,

Thank you for your post. As I begin to go through it and see how it works, I have but one questions for now. What is ADO? I think my biggest problem is that I am new to this who database stuff and SQL.

These SQL commands/syntax, is this referring to the commands in Matlab that make things happen between Access and Matlab?

I greatly appreciate your assistance with this, I'll try and figure out your code and let you know how it works for me.

Thank you,

L

[L]

Guillaume,

Also, as I have been scratching my head trying to figure out how to adapt your code to my needs, I'm not sure what it what. Perhaps you can break down your code a little more for me? I'm not sure what parts of my data I am supposed to define as the variables in the code above.

One thing I ran into is, "SplitAtColon" seems to be a function that isn't specified in the code, or perhaps it is from some toolbox I don't have.

Your help is very much appreciated.

Thank you,

L

[Guillaume]

ADO is ActiveX Data Object, a microsoft protocol for accessing databases and similar.

The idea of my post was to show you how to build a CREATE TABLE query. You don't have to use ADO, you can use matlab's database toolbox if you have it, or any other method of your choosing, to execute the query.

SplitAtColon is a function I have written. From the name, you can infer that it splits a string in two at the : character.

The function is already well documented. it takes a cell array that describes the columns of the table and build the corresponding query. Example of an input:

       DropsDefinition = { 'Drops'                                                     ...
                {'ID'           'auto'      'primary'}                                  ...
                {'ImageID'      'int'       'reference:Images'}                         ...
                {'Diameter'     'double'    'required' 'indexed'}                       ...
                {'Contrast'     'double'    'required' 'indexed'}                       ...
                {'CentroidX'    'double'    'required'}                                 ...
                          };

which results in a table named Drops with first column ID, which is a primary key of type autonumber, 2nd column ImageID of type int, linked to the primary key of another table named Images, 3rd and 4th column Diameter and Contrast of type double, which are both indexed and required, etc.

The corresponding query is

CREATE TABLE [Drops] 
   ([ID] AUTOINCREMENT PRIMARY KEY, 
    [ImageID] INTEGER REFERENCES [Images] ON UPDATE CASCADE ON DELETE CASCADE,
    [Diameter] DOUBLE NOT NULL 
    [Contrast] DOUBLE NOT NULL
    [CentroidX] DOUBLE NOT NULL)

Rereading the code, it looks like I never implemented the indexed cconstraint.

[L]

Guillaume,

Thank you for your responses. I don't fully understand them, but that is no fault of yours. It is just my own fundamental lack of understanding in this subject. I'll continue to beat my head on it, and try to figure out these commands and all the trickery.

Additionally, I stumbled across a command that is supposed to do what I need, but I'm not sure I know how to use it correctly. exec(conn,'Create table(exceldata)'); and that gives me an object call a cursor. I think I'm close to simply just creating a new table in the database and then sending the data to it, but I'm not quite sure on the right syntax. Any more help would be appreciated.

I appreciate your help thus far.

EDIT: I do have the database toolbox by the way. What I don't have is the understanding to utilize to its fullest capability, but I'm trying :).

Thank you,

L

[Guillaume]

As far as I know, what the database toolbox calls a cursor is what Access (VBA) calls a recordset. You'll always get one when you execute a query as it allows you to navigate the rows returned by the query. For a CREATE TABLE query, it's probably not useful as you don't get any row but for a SELECT query, it's critical.

It does sound like you need to learn a bit more how to use a database. Even if you create the table directly in access, you still need to know how to use cursors to append or query data.

Once you've build up the query SQL, you indeed need to execute it. That's the purpose of your exec(conn, sql) and my this.Connection.Execute(sql).

By the way, this is the link to the syntax of the CREATE TABLE query in access sql.

Answer 2

[Sid Jhaveri]

1) You can refer to the following documentation link for general information on how Database Toolbox works: https://www.mathworks.com/help/database/getting-started-with-database-toolbox.html

2) As far as creating a new table goes, you will have go the SQL syntax for creating a table. Once you have the SQL statement ready, you can use it like " curs = exec(conn,sqlquery) " where "sqlquery" is the SQL statement for creating table.