jdbc/Jackcess/Ucanaccess - Not able to connect to access tables linked to sharepoint list

Question

I have been trying to connect to access database on my local machine using jackcess/ucanaccess. This database is linked with sharepoint list. The connection happens and I can read from the tables. When I remove link to the sharepoint list for one of the access tables and link it again with the sharepoint list, I get following error:

TestJdbc.main()[CONSTANTS, f_0194596E3DC6402283A012D0661E7517_Attachments, f_41905A7F89E440EDBFA0B6B30C57EE68_CDAttachment, f_431F6C05710944EEA2F443EAC1A43A10_JOIN_XRef2Capability, f_6030D9CC0C874CF5A31AB134BBF1A10E_BackgroundAttachment, f_7EA55A8F5B1141E68CEECFD2DB672397_ProjectAttachment, LUT_Domain, LUT_Requirement_Types, LUT_XRef_Types, MASTER_ePlanProject, Name AutoCorrect Save Failures, t-Individual-Status, t-Role-System, TABLE_BackgroundAttachments, TABLE_Benefits, TABLE_Capabilities, TABLE_DepartmentImpact, TABLE_LogHistory, TABLE_ProjectAttachments, TABLE_Requirements, TABLE_Terminology, TABLE_XRefs, UserInfo] Exception in thread "main" java.io.FileNotFoundException: given file does not exist: https:\workspace.web.att.com\sites\ISDE\SmartIA;LIST={4131DABC-4E9F-4F75-95DC-CE26A0F63EC3};VIEW=;RetrieveIds=Yes at com.healthmarketscience.jackcess.impl.DatabaseImpl.open(DatabaseImpl.java:365) at com.healthmarketscience.jackcess.DatabaseBuilder.open(DatabaseBuilder.java:252) at com.healthmarketscience.jackcess.DatabaseBuilder.open(DatabaseBuilder.java:291) at com.healthmarketscience.jackcess.util.LinkResolver$1.resolveLinkedDatabase(LinkResolver.java:42) at com.healthmarketscience.jackcess.impl.DatabaseImpl.getTable(DatabaseImpl.java:984) at com.healthmarketscience.jackcess.impl.DatabaseImpl.getTable(DatabaseImpl.java:928) at com.healthmarketscience.jackcess.impl.DatabaseImpl.getTable(DatabaseImpl.java:81) at com.attprism.rest.common.TestJdbc.main(TestJdbc.java:54) 14:17:24.843 ERROR: [PacketDispatcher.cpp:154] Run: Exception in executing command: TransportException [510/201] there isn't an open connection to a debugger

My code looks like this

package com.attprism.rest.common;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Set;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;

public class TestJdbc {

    public static void main(String[] args) throws IOException, SQLException,
            ClassNotFoundException {

        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        Database db = DatabaseBuilder.open(new File(
                "C://Users//sm501y//Desktop//SmartIA_ODBC.accdb"));
        Set<String> tables = db.getTableNames();
        System.out.println("TestJdbc.main()" + tables);
        Table table = db.getTable("CONSTANTS");
        for (Row row : table) {
            System.out
                    .println("Column 'a' has value: " + row.get("Project_ID"));
        }
    }
}

Any help please?

Answer 1

Currently, UCanAccess is only able to work with "native" Access tables, i.e., tables that are actually stored in an .accdb or .mdb file (or related variants like .accde, .mde, etc.). UCanAccess can operate on native linked tables (links to Access tables that are stored in a separate .accdb/.mdb file), but it cannot work with ODBC linked tables (links to tables stored in SQL Server, MySQL, ...) and it apparently cannot work with tables linked to SharePoint.

You might want to do a little test in C# using System.Data.Odbc to see if it can work with the SharePoint linked table. I haven't tried it, but I do know that .NET applications using System.Data.Odbc are able to work with ODBC linked tables on SQL Server, so perhaps it might work with the SharePoint table, too.