Facing difficulty in connecting access db from databricks

[Krishna008]

Unable to connect Microsoft Access DB in Databricks, I tried with JDBC & Pyodbc connectors but I'm not able to read the file from accessdb. Please suggest or help to resolve this issue

[Khaja_Zaffer]

Hello @Krishna008 

Please share the steps you to followed and along with error:

[Krishna008]

Installed office365-REST-Python-Client==2.5.13

UcanaccessDriver

 # File location and type

file_location = "/FileStore/tables/ABU.accdb"

file_type = "accdb"

import pyodbc

# Connection string (Windows only, or if driver is available)

conn_str = (  # Change to a valid connection string for the environment or comment out if not applicable

    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"

    f"DBQ={file_location};pwd=test123"

)

conn = pyodbc.connect(conn_str)

cursor = conn.cursor()

# List tables

tables = [table.table_name for table in cursor.tables(tableType='TABLE')]

file_location = "/FileStore/tables/ABU.accdb"

df = spark.read.format("jdbc") \

    .option("url", "jdbc:ucanaccess://dbfs/FileStore/tables/ABU.accdb") \

    .option("driver", "net.ucanaccess.jdbc.UcanaccessDriver") \

    .option("dbtable", "tbl_location") \

     \

    .load()

display(df)

file_location = "/FileStore/tables/ABU.accdb"

df=df = spark.read.format("jdbc") \

    .option("url", "jdbc:ucanaccess:///dbfs/FileStore/tables/ABU.accdb") \

    .option("driver", "net.ucanaccess.jdbc.UcanaccessDriver") \

    .option("dbtable", "tbl_location") \

    .load()

Getting below errors

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found (0) (SQLDriverConnect)")
Py4JJavaError: An error occurred while calling o410.load. : java.lang.ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver at java.net.URLClassLoader.findClass(URLClassLoader.java:387) at java.lang.ClassLoader.loadClass(ClassLoader.java:419)

An error occurred while calling o465.load. : net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding. at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)