I am attempting to INSERT with the UCanAccess Driver.
I first tested with a Python Script to ensure the UCanAccess Driver could successfully INSERT. I was able to successfully INSERT into the Access Database using the following Python Code.
import jaydebeapi
# Initiate Java runtiome file location
ucanaccess_jars = [
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\ucanaccess-5.0.1.jar",
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\lib\commons-lang3-3.8.1.jar",
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\lib\commons-logging-1.2.jar",
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\lib\hsqldb-2.5.0.jar",
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\lib\jackcess-3.0.1.jar",
r"C:\Users\ignitionuser\Downloads\UCanAccess-5.0.1.bin\loader\ucanload.jar",
]
classpath = ";".join(ucanaccess_jars)
# Initate connection to MS Access files
cnxn = jaydebeapi.connect(
"net.ucanaccess.jdbc.UcanaccessDriver",
"jdbc:ucanaccess://PATH TO DATABASE.accdb",
["", ""],
classpath
)
# From connection initiate cursor
crsr = cnxn.cursor()
# Run the INSERT query
insert_query = "INSERT INTO TLType (ID, Type) VALUES (?, ?)" # Modify Column1, Column2, Column3 with actual column names
# Data to be inserted
data_to_insert = [
("11", "Ignition"), # Modify Value1, Value2, Value3 with actual data
]
# Execute the INSERT query for each set of data
for data in data_to_insert:
crsr.execute(insert_query, data)
# Commit the changes
cnxn.commit()
# Close cursor
crsr.close()
# Close connection
cnxn.close()
I could then run SELECT and INSERT without any compiling error. However, after checking the Microsoft Access Document it did not insert. This was the script I ran in Ignition without any issues.
system.db.runPrepUpdate("INSERT INTO TLType (ID, Type) VALUES (?, ?)", [12,"Joe Test"], "MA")
(This seems similar to this issue only he was not able to INSERT with UCanAccess at all. I was from a Python script Cannot WRITE to MS Access Database)