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

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

Leladams — Thu, 06 Jan 2022 17:33:14 GMT

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.

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

Leladams — Mon, 10 Jan 2022 17:01:35 GMT

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.

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

Leladams — Fri, 14 Jan 2022 19:19:37 GMT

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.

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

Anonymous — Fri, 14 Jan 2022 21:05:50 GMT

@Leland Adams - Thank you for the extra information.

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

Anonymous — Wed, 13 Apr 2022 14:46:56 GMT

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.

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

Dhara — Wed, 29 Jun 2022 03:12:27 GMT

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

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

Dhara — Wed, 29 Jun 2022 03:13:13 GMT

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

Dhara — Wed, 29 Jun 2022 03:13:58 GMT

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

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

Leladams — Wed, 29 Jun 2022 13:33:14 GMT

I am still unable to solve the issue.

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

Leladams — Wed, 29 Jun 2022 13:36:20 GMT

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

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

malhardhawle — Tue, 21 Oct 2025 20:36:51 GMT

Hi, any chance you were able to read .accdb files? I am also trying to read those files in Databricks