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

10 views (last 30 days)
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

Accepted Answer

Guillaume
Guillaume on 6 Dec 2016
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
  5 Comments
L
L on 12 Dec 2016
Edited: L on 12 Dec 2016
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
Guillaume on 12 Dec 2016
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.

Sign in to comment.

More Answers (1)

Sid Jhaveri
Sid Jhaveri on 6 Dec 2016
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.

Products

Community Treasure Hunt

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

Start Hunting!