0

I got below code to working in Excel VBA.

Dim con, com

Const adCmdStoredProc = 4
Const scon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Accessdbpath.accdb;Persist Security Info=False;"

Set con = CreateObject("adodb.connection")
Set com = CreateObject("adodb.command")

con.Open scon
Set com.activeconnection = con
com.CommandType = adCmdStoredProc
com.CommandText = "accessqueryname"
com.Execute
Set com.activeconnection = Nothing
Set con = Nothing

I was trying to run the same routine in a server (no ms office).
So I tried creating a VBS file to do it instead of via Excel VBA.
However I can't seem to make it work. I got below error:

enter image description here

Edit1: I did check using the process explorer if I am running the script at 64/32 bit and I'm running at 64 bit.

enter image description here

L42
  • 19,427
  • 11
  • 44
  • 68
  • @AnsgarWiechers There's no accepted answer in the thread posted and the top answer doesn't work for me. – L42 Sep 10 '18 at 00:38
  • Is the provider even installed? If not check [this thread](https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-quotthe-microsoftaceoledb120-provider-is-not-registered-on-the-local?forum=vstsdb). – Ansgar Wiechers Sep 10 '18 at 10:14
  • @AnsgarWiechers Of course it is, it works in Excel VBA. Does *VBS* and *VBA* use different providers considering I used the same connection string which should point to the same driver. Btw, I am currently testing this on the same machine where I test the Excel file with the exact same code. – L42 Sep 10 '18 at 21:57
  • If the provider is installed correctly the usual reason for the error you observed is that the script was run with the 64-bit interpreter instead of the 32-bit interpreter. If you actually did run the script with the 32-bit interpreter (please double-check with Process Explorer) then you need to provide more information. – Ansgar Wiechers Sep 10 '18 at 23:28
  • @AnsgarWiechers I did try what [Niles](https://stackoverflow.com/a/5221712/2685412) pointed out in his post but the same thing. Is that what you meant? Or do I need to check somewhere else like the *process explorer* you are referring to? I don't think I have that installed. – L42 Sep 10 '18 at 23:55
  • 1
    That's how you'd normally start the 32-bit interpreter, yes. In Process Explorer you can add a column that shows if a process is 32-bit or 64-bit. Use that to verify that the lauchned VBScript interpreter instance is actually 32-bit. Process Explorer doesn't require installation. Simply download the zip archive, unpack it, and run the executable from there. – Ansgar Wiechers Sep 11 '18 at 08:12
  • @AnsgarWiechers See my edit. Btw, thank you pointing that it doesn't need to be installed. – L42 Sep 12 '18 at 07:03
  • Try starting `C:\Windows\SysWOW64\cmd.exe` (that CMD instance should be 32-bit in Process Explorer) and runnig your VBScript with the 32-bit `C:\Windows\SysWOW64\cscript.exe` from there. But even when run from a 64-bit CMD `C:\Windows\SysWOW64\cscript.exe` and `C:\Windows\SysWOW64\wscript.exe` should be launched as 32-bit processes, not 64-bit like in your screenshot. – Ansgar Wiechers Sep 12 '18 at 08:32

0 Answers0