01-06-2022 09:33 AM
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.
01-06-2022 10:30 AM
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.
01-10-2022 07:38 AM
Hi @Leland Adams , Did you go through this link?
01-10-2022 09:01 AM
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.
01-14-2022 11:19 AM
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.
06-28-2022 08:12 PM
HI I tried setting all the classpath still I am getting the same error. Please find the attached screenshots for the same.
06-28-2022 08:13 PM
06-29-2022 06:36 AM
I had tried every possible iteration of classpath to no success.
01-14-2022 01:05 PM
@Leland Adams - Thank you for the extra information.
04-13-2022 07:46 AM
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.
06-28-2022 08:13 PM
I am still having difficulty in accessing the mdb files and I have responded back too.
06-29-2022 06:33 AM
I am still unable to solve the issue.
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.