Link to MS Access database

Support
#1

Hi there,
I am trialling the Cuba platform and have my first app ready to start. We have some legacy databases in Microsoft Access. Does anyone know a way of interacting via UCanAccess or similar? Any other workaround is welcome. In fact I have struggled to even link to a Mysql db but I will save that for another question.
Thanks in advance.

#2

Why not migrate your access database to SQL Server Express?

Microsoft makes a tool that does this easily.

https://docs.microsoft.com/en-us/sql/ssma/access/getting-started-with-sql-server-migration-assistant-for-access-accesstosql

#3

Hi, Good shout actually. It sounds obvious now you say it. Whilst I don’t have control of those databases (I could persuade them to split the database and keep their frontend. I could then also use SQL server express. The limits look to be fine for what I intend to use it for.

I will post back if I have some success. I was also wondering if replication between ms access and ms sql would help if the owner refused but it looks to have been dropped in 2000. I wish Microsoft had committed more to developing ms access as I strangely like it.

Thanks for the suggestion. Hopefully Cuba will help my learning curve and become a new friend!

#4

Hi Matt,

You can connect your CUBA application to any database as long as it has a JDBC driver.

So an integration approach can be as follows:

  • Define an additional data source in your modules/core/web/META-INF/context.xml file like this:

      <Resource
    name="jdbc/MyAccessDb"
    type="javax.sql.DataSource"
    driverClassName="some driver"
    url="some url"
    username="user"
    password="pass"/>

  • Create a middleware service to work with your database.

  • Obtain your data source from JNDI and use QueryRunner to work with the database:

      package com.company.sample.service;

  import com.haulmont.bali.db.MapListHandler;
  import com.haulmont.bali.db.QueryRunner;
  import org.springframework.stereotype.Service;

  import javax.naming.*;
  import javax.sql.DataSource;
  import java.sql.SQLException;
  import java.util.List;
  import java.util.Map;


  @Service(NewService.NAME)
  public class NewServiceBean implements NewService {

      @Override
      public void test() {
          DataSource ds;
          try {
              Context context = new InitialContext();
              ds = (DataSource) context.lookup("java:/comp/env/jdbc/MyAccessDb");
          } catch (NamingException e) {
              throw new RuntimeException(e);
          }
          QueryRunner queryRunner = new QueryRunner(ds);
          try {
              List<Map<String, Object>> rows = queryRunner.query("select name, email from customer", new MapListHandler());
              for (Map<String, Object> row : rows) {
                  String name = (String) row.get("name");
                  String email = (String) row.get("email");
                  // etc.
              }
          } catch (SQLException e) {
              throw new RuntimeException(e);
          }
      }
  }
#5

Hi Konstantin,
Thanks for the level of detail! So, with this approach we can consider the main app in any supported format but in theory, connect to multiple sources by connecting with middle-ware. It seams really powerful but I am pretty novice so it may take me a bit of time to experiment…
Matt.

#6

Yes, and for supported databases you can also use additional data stores for smooth integration with any number of databases.

For Access it’s unfortunately impossible because we don’t have automatic mapping to entities in this case, but I should mention that you can map data to transient entities to show them in UI using our standard datasources and visual components like Table.