cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Leladams
New Contributor III

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.

11 REPLIES 11

Kaniz
Community Manager
Community Manager

Hi @Leladams! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question first. Or else I will get back to you soon. Thanks.

Kaniz
Community Manager
Community Manager

Hi @Leland Adams​ , Did you go through this link?

Leladams
New Contributor III

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
New Contributor III

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
New Contributor III

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

Dhara
New Contributor III
 

Leladams
New Contributor III

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
New Contributor III

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

Leladams
New Contributor III

I am still unable to solve the issue.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.