What is the best way to read in a ms access .accdb database into Databricks from a mounted drive?

[Leladams]

I am currently trying to read in .accdb files from a mounted drive. Based on my research it looks like I would have to use a package like JayDeBeApi with ucanaccess drivers or pyodbc with ms access drivers.

Will this work?

Thanks for any help.

[Leladams]

Hi @Kaniz Fatma​, Thanks for this. I don't think this is a viable solution. The .accdb file is in databricks and needs to be read while sitting in databricks. I am unable to use outside programs due to security.

[Leladams]

The below code presents the error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 given file does not exist:

import jaydebeapi, jpype
 
connectionProperties = {
  "driver" : "net.ucanaccess.jdbc.UcanaccessDriver"
}
 
url = "jdbc:ucanaccess://dbfs/FileStore/tables/ASampleDatabase.accdb"
df = spark.read.jdbc(url=url, table="Asset Items", properties=connectionProperties)

This code presents the error:

TypeError: Class net.ucanaccess.jdbc.UcanaccessDriver is not found

import  jaydebeapi, jpype
 
# Initiate Java runtiome file location 
 
ucanaccess_jars = [
        "dbfs:/FileStore/jars/UCanAccess-5.0.1.bin/ucanaccess_5_0_1.jar",
        "dbfs:/FileStore/jars/UCanAccess-5.0.1.bin/lib/commons_lang3_3_8_1.jar",
        "dbfs:/FileStore/jars/UCanAccess-5.0.1.bin/lib/commons_logging_1_2.jar",
        "dbfs:/FileStore/jars/UCanAccess-5.0.1.bin/lib/hsqldb_2_5_0.jar",
        "dbfs:/FileStore/jars/UCanAccess-5.0.1.bin/lib/jackcess_3_0_1.jar",
        ]
 
# classpath = $PATH$ parameters for Java runtime file location
 
classpath = ":".join(ucanaccess_jars)
 
# Initate connection to MS Access files
cnxn = jaydebeapi.connect(
    "net.ucanaccess.jdbc.UcanaccessDriver",
    "jdbc:ucanaccess:///FileStore/tables/ASampleDatabase.accdb",#.accdb file
  
    ["", ""],
    classpath
    )
 
# From connection initiate cursor
crsr = cnxn.cursor()
 
# Run Query
crsr.execute("SELECT * FROM 'Asset Items'")
 
# Fetch Query result
for row in crsr.fetchall():
    print(row)
 
# Close cursor
crsr.close()
# Close connection
cnxn.close()

Regarding the file path I have tried 3 slashes, 2 slashes. with colon without colon. I think I have tried every possible combination for the filepath with no success.

[Dhara]

HI I tried setting all the classpath still I am getting the same error. Please find the attached screenshots for the same.

[Leladams]

I had tried every possible iteration of classpath to no success.

[Anonymous]

@Leland Adams​ - Thank you for the extra information.

[Anonymous]

Hi @Leland Adams​ 

Hope you are doing well. Thank you for posting your question and giving us additional information. Do you think you were able to solve the query?

We'd love to hear from you.

[Dhara]

I am still having difficulty in accessing the mdb files and I have responded back too.

[Leladams]

I am still unable to solve the issue.