I'm attempting to run an Access function through Java using Jacob using Application.Run (https://msdn.microsoft.com/en-us/library/office/ff193559.aspx). I am able to open and close an Access database, but not run a function. I suspect the run call actually does go through but that I have opened the file read-only (maybe? not sure I did) which then causes the Access error: Run-time error 3073: Operation must use an updatable query. The query simply appends two strings onto a test table I created, and that query works by hand, but so far not through Java.
If the error is that I've opened it read-only, how can I open it not read-only? If it's something else, how do I call a function (or a macro, either will work) using Jacob? Or you may know some other Java technique besides using Jacob, I'd take that too.
Minimum example:
Java program
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.LibraryLoader;
import com.jacob.com.Variant;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author evans
*/
public class Test {
public static void main(String[] args) {
// Load library/.dll
try {
String libFile = System.getProperty("os.arch").equals("amd64") ? "jacob-1.18-x64.dll" : "jacob-1.18-x86.dll";
FileInputStream inputStream = new FileInputStream(new File(libFile));
File temporaryDll = File.createTempFile("jacob", ".dll");
try (FileOutputStream outputStream = new FileOutputStream(temporaryDll)) {
byte[] array = new byte[8192];
for (int i = inputStream.read(array); i != -1; i = inputStream.read(array)) {
outputStream.write(array, 0, i);
}
}
System.setProperty(LibraryLoader.JACOB_DLL_PATH, temporaryDll.getAbsolutePath());
LibraryLoader.loadJacobLibrary();
temporaryDll.deleteOnExit();
} catch (IOException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}
// Open thread
ComThread.InitSTA(true);
// New application
ActiveXComponent ComBridge = new ActiveXComponent("Access.Application");
// Open database
Dispatch.put(ComBridge, "Visible", new Variant(true));
ComBridge.invoke("OpenCurrentDatabase", new Variant("C:/Users/evans/Documents/Book Business/Building Reports/Book Business.accdb"));
// Run function
ComBridge.invoke("Run", new Variant("Test"));
// Shutdown
ComBridge.invoke("Quit");
ComThread.quitMainSTA();
ComThread.Release();
}
}
Access query:
INSERT INTO tblTest ( Test, Test2 )
SELECT "a" AS Expr1, "B" AS Expr2;