I think its possible to consume Microsoft Access through VBScript. So based on this fact, here is an idea...
In excel you could create a VB Subrutine/function that brings data from an Access table, then you could use the Presto Excel Connector an create a service from that excel spreadsheet.
Another way could be: instead of using the Presto Excel connector, you could use the Apache POI library, where you can use Java to consume microsoft office files, and so you could create a Java function (which will read the excel file with the access data) and then call that function from a Mashup.
Hope it helps,
I have not tried this myself but I know that you can use a java JDBC to ODBC connector to get data from access. You could use this Java API and deploy a class behind presto to expose the data.
private static final String accessDBURLPrefix = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
private static final String accessDBURLSuffix = ";DriverID=22;READONLY=false}";
// Initialize the JdbcOdbc Bridge Driver
static {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(ClassNotFoundException e) {
System.err.println("JdbcOdbc Bridge Driver not found!");
}
}
/** Creates a Connection to a Access Database */
public static Connection getAccessDBConnection(String filename) throws SQLException {
filename = filename.replace('', '/').trim();
String databaseURL = accessDBURLPrefix + filename + accessDBURLSuffix;
return DriverManager.getConnection(databaseURL, "", "");
}
Hi guys,
thanks for you replies. Sorry I didn't respond sooner ... so many ideas, so little time.
Anyway, thanks for your suggestions. I have successfully set up a test MSAccess db as a published database service and have tried it out in Wires successfully. If anyone is interested I'll post back to this topic how I did it. All I can say at this stage is that it seems to work ok, but that's with a simple MSAccess db with one table and one row.
As an aside, does anyone know where I can download a copy of the jdbc:odbc:Driver from. I did try ... this was my first option, but couldn't find a copy so I opted for something commercially available just to try out the concept.
Thanks everyone for your ideas.
Hi mashers,
This actually turned out to be much simpler than I originally expected, but it took a fair bit of research to finally get a working test mashup going. For anyone coming along later, here is an example of my working test emml that gets its data from an MSAccess database (defined as an ODBC datasource) using the Sun jdbcOdbc driver.
<mashup xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.jackbe.com/2008-03-01/EMMLSchema/ ../src/schemas/EMMLSpec.xsd"
xmlns="http://www.jackbe.com/2008-03-01/EMMLSchema"
xmlns:macro="http://www.jackbe.com/2008-03-01/EMMLMacro"
name = "SQLTest">
<output name="recordSet" type="document" />
<variables>
<variable name="dsURL" type="string" default="jdbc:odbc:SHAREDDATA" />
<variable name="dsDriver" type="string" default="sun.jdbc.odbc.JdbcOdbcDriver" />
<variable name="SQLQuery" type="string" default="SELECT surname,[first name] AS firstname FROM "></variable>
<variable name="SQLTable" type="string" default="CLIENT" />
</variables>
<datasource url="$dsURL" driverClassName="$dsDriver"/>
<assign fromexpr="concat($SQLQuery,$SQLTable)" outputvariable="$SQLQuery"/>
<display variable="SQLQuery" />
<sql query="$SQLQuery" outputvariable="$recordSet" />
</mashup>
I discovered that you can't define a jdbcOdbc datasource in the Presto Service Explorer because you can't define a DB Driver for it since Service Explorer expects you to provide a JAR file for the driver, which in this case doesn't exist. It is in fact a DLL file which you need to drop in to the %WINDIR%\system32 directory to make this work. The jdbcOdbc.dll file comes with the JDK you will have had to install.
Hope this helps. I should point out that the jdbcOdbc bridge is not supposedly only for development purpose, but it's free.
Cheers, Innes (NZ)
Hi mashers,
Based on what I've been learning and building as part of my mashup education, I can see just how powerful this technology can be and the potential for it to make a real difference in our organisation. However, as is sadly often the case in these situations, there is an impediment that stands in the way of us taking full advantage of the benefits I perceive.
One of the things I like most about the mashup concept is that there is the opportunity to bring together data from disparate systems wherever it lives in a mashable form and then go from there.
Unfortunately, in our organisattion, we have a lot of data locked up in MS Access, in particular our primary business system is a commercial application that uses an Access back-end db. I'd like to expose some of the data as mashable services, but am not sure the best way to go about that. It seems contrary to the "mashup way" to have to pull the data out to an intermediate repository just to be able to access it in mashups.
Perhaps I'm just being to picky and getting the benefit from being able to mashup this data outweighs the annoyance of having to pull data out periodically to place it in a mashable data respository.
Can I have some ideas from the community on the best way to address this annoying road-block.
Cheers,
Innes (NZ)