1

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;
Holly
  • 55
  • 1
  • 7
  • I can (sort of) reproduce your issue using JACOB 1.18. The Access instance launches and the database is opened, but when I switch over to Access I see that the database is opened read-only (and, in my case, active content is disabled). Do you really need to run VBA `Sub` and/or `Function` code in that database (possibly including custom VBA functions called from Access saved queries)? – Gord Thompson Feb 25 '17 at 20:29
  • @Gord Thompson In my actual database, my code runs a bunch of queries, some conditional on whether linked tables are empty (which I didn't know how to do with a macro). It could probably be expressed as a macro and not as a function (if I figure out the linked table issue). But in the future, there are other things I know I will want to be functions, not macros. Another option was to translate the entire VBA code to Java by JACOB (I know you can call specific queries and such) but if I can't simply run a macro or function, I figured translating it entirely probably opens a new can of worms. – Holly Feb 27 '17 at 17:09
  • You're quite right to be leery of "can[s] of worms". Is there a compelling reason to stick with Java instead of switching to C#? (... or would that "can" be even bigger? :) – Gord Thompson Feb 27 '17 at 18:13
  • Yeah, never done C# so probably quite the can – Holly Feb 27 '17 at 23:03
  • You might be surprised. [This](http://pastebin.com/5qE5e7D0) is your sample application in C#. – Gord Thompson Feb 27 '17 at 23:49
  • Wow that is easy peasy. Hopefully I can make this work. Thanks Gord – Holly Feb 27 '17 at 23:59

0 Answers0