File Exchange

image thumbnail

ADO OLE Database Connection

version 1.0.0.0 (13.1 KB) by Martin Furlan
Queries datasource and insers data into datasource.

1 Download

Updated 10 Jan 2007

View License

This code basis on Tim Myers code (oledb*.m) and uses ADO OLE DB instead of OWC - Office Web Component. When OWC not installed (e.g. MS Office 2000) using ADO OLE DB can be a solution. The syntax is similar to Tim Myers code.

The Code is tested only with MS Access database.

Cite As

Martin Furlan (2021). ADO OLE Database Connection (https://www.mathworks.com/matlabcentral/fileexchange/13621-ado-ole-database-connection), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (22)

Salvador

Great! Thank you so much for the solution.
Only one question...
The code lines about "Timeout Connection", ¿should not they go before "Open connection" lines?

Larry Wang

Great work!
But what about the adotest.mdb is not in the current directory?
Anybody knows?

Olivier

Solution provided by super heasy worked for me (Win7x64, MATLAB R2016a). Great work!
Do not forget to edit adodbcnstr.m

super heasy

for this problem "Error using COM.ADODB_Connection/Open",
because using Winx64,and "Microsoft.Jet.OLEDB.4.0"is not support .
So download and install "AccessDatabaseEngine_X64.exe",then replace "Microsoft.Jet.OLEDB.4.0" with "Microsoft.ACE.OLEDB.12.0" in <adodbcnstr.m>.
It works in Win10x64 and MATLAB 2016a.

sweta cccc

thanks alot for the code...
jus need a little help...
i need to add a variable value into the database. eg: var1=1234; i need to store this variable value into the database.

mingke

there is an error in open connection. Has anybody had this problem ?

Error using COM.ADODB_Connection/Open
Invoke Error, Dispatch Exception:
Source: Microsoft OLE DB Provider for SQL Server
Description: Cannot open database "E:\database\MyFinancialDatabase.mdf" requested by the login. The login
failed.

Error in adodbcn (line 29)
invoke(cn,'Open', cnstr);

Error in demoFromIlovematlab (line 13)
cn=adodbcn(s);

David malins

I'm having an error invoking the ADODB.connection. Has anybody had this problem and managed to resolve it?

??? Invoke Error, Dispatch Exception:
Source: ADODB.Connection
Description: Provider cannot be found. It may not be properly installed.
Help File: C:\WINDOWS\HELP\ADO270.CHM
Help Context ID: 12ee4f

Error in ==> adodbcn at 29
invoke(cn,'Open', cnstr);

Error in ==> PI_WL at 9
cn = adodbcn(cnstr)

I'm using win xp and 32-bit matlab R2010a.

Robert Daly

This is a great tool, I have been using it for a while with out a hitch. Just recently it started to go wrong and gave the error:
"No public field CursorLocation exists for class COM.ADODB_Connection"
I found if I commented out the folowing line from adodb_connect
ado_connection.CursorLocation = 'adUseClient'; % Uses a client-side cursor supplied by a local cursor library
this error went away.
After this it had a problem with the line from adodb_query...
if (ado_recordset.State && ado_recordset.RecordCount>0)

I found if I delete this (and the corresponding end) it works fine for my purposes. I'm not exactly sure what this did (anybody?) and what errors will crop up now that it isn't there.
I wonder if there has been an update to the ADODB set up and now some of the methods and classes have changed since this code was written.

Brian

I'm getting this error on Win7 64 box:

Make sure adotest.mdb is in the current directory
??? Invoke Error, Dispatch Exception:
Source: ADODB.Connection
Description: Provider cannot be found. It may not be properly installed.
Help File: C:\Windows\HELP\ADO270.CHM
Help Context ID: 12ee4f

Error in ==> adodbcn at 29
invoke(cn,'Open', cnstr);

Error in ==> adodb_demo at 30
cn=adodbcn(s);

Kevin

does somebody have an example of running this with Oracle i keep getting an error.
??? Invoke Error, Dispatch Exception:
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Oracle][ODBC][Ora]ORA-12154: TNS:could
not resolve the connect identifier specified

Fredrik

Vinod Karanth

Very effective. Thanks heaps.

Guyennon Nicolas

Thanks a lot !!! Amazing work, even faster than Tim Myers code (oledb*.m)
I'm working with your toolbox to query access 2003 database.
I was looking for table, field and query information. Tooks me 4 days ... hope that could help someone else

%% get table name
sqltablename='SELECT name FROM MSysObjects WHERE Type=1 AND Flags=0 ORDER BY name';
tablename=adodbquery(cn,sqltablename);
%% get query name
sqlqueryname='SELECT name FROM MSysObjects WHERE Type=5 AND Flags=0 ORDER BY name';
queryname=adodbquery(cn,sqlqueryname);

note that in both case you should first change users authorization to read for MSysObjects opening your access database. ("user and group authorization" menu in tools)

the hardest part was to get fields or columns name ...

function field_name=tablefieldname(cn,table_name)
%% find all field name from table
sqlfiledname=['SELECT * FROM ' table_name];
invoke(cn,'BeginTrans');
r = invoke(cn,'Execute',sqlfiledname);
invoke(cn,'CommitTrans');
nbfileds=r.Fields.count;field_name='';
for ff=1:nbfileds
fld=r.Fields.Item(ff-1);
field_name=strvcat(field_name,fld.name);
end

bye
Nico

Leen K.

want to use variables in the sql queries...
kindly suggest a way.
e.g. sql=['select * from TestTable where firstname=',myname ,'];
5this statement wont replace variable myname

Jasleen K.

Thanks a tonne..!!!!
solved my problem.!!

deepa deepa

Laszlo Sragner

h=actxserver('ADODB.Connection');
h.Open(connstr);
res=h.Execute(sqlstr);
x=res.GetRows()';
h.Close();
delete(h);

Maybe the leak was due to the 'release' command at the end instead of 'delete'

hardik nadiyana

really help ful thanx,
hardik

jairo Ortiz

Christy Lynch

This is a fantastically easy interface to use for Access databases. The problem is a memory leak. Releasing the connection and clearing the connection variable does not release the memory used up by the connection. Only restarting Matlab seems to work.

Martijn Koopman

Very easy to use and extremely fast interface with Microsoft Access *.MDB files.

Ryan Schaezler

Works well. I was having problems with OWC even though it was install on one computer. Using the ADODB connection worked without any issues.

MATLAB Release Compatibility
Created with R2006b
Compatible with any release
Platform Compatibility
Windows macOS Linux
Acknowledgements

Inspired by: database connection mfiles

Inspired: adodb_tools

Community Treasure Hunt

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

Start Hunting!