Thanks m w- I've done all that. I think the problem lies in that Qlik is 64-bit so it's trying to use the 64-bit driver, so it can't find my 32-bit office.
I've tried forcing it to call 32-bit and I've found some suggestions to use windows scripting (wscript) to accomplish that, but the qlik vb editor isn't recognizing wscript.
Otherwise, anyone talking about forcing a 32-bit connection is talking about doing it from the command prompt which is why this becomes a Qlik specific question. If Qlik affords a way to force call a 32-bit OLE or ODBC connection- it would be so easy, but it seems not many want to do this. If I could do it, it would solve the woes of 3 major processes within my company.
This worked for me:
CreateObject("WScript.Shell").Run "C:\Windows\SysWOW64\wscript.exe C:\Users\ra053499\Downloads\test.vbs"
Here is the contents of my test.vbs:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=P:\Projects\Budget 2.0\Budget Reports\P31\ISD Budget.mdb;" & _
"Jet OLEDB:Database Password=spartans;"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rs1 = CreateObject("ADODB.Recordset")
ON ERROR RESUME NEXT
if err.number>0 then
msgbox "Error accessing the budget database. Try again later."
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM CoverSheet where Snapshot = ""Current""" , cn, adOpenStatic, adLockOptimistic
strSQL = "SELECT * FROM CoverSheet where Snapshot = """ & strSnapshot & """"
rs1.Open strSQL , cn, adOpenStatic, adLockOptimistic
if rs.Recordcount > 0 then ' delete existing snapshot data for today
It's not the most elegant thing I've ever done, but it will do the trick! Thank you m w! (Also my initials).
For future readers, I was able to combine this with arguments which are the parameters to be inserted into the Access database.
Call from Qlik Module:
CreateObject("WScript.Shell").Run "C:\Windows\SysWOW64\wscript.exe C:\ScriptName.vbs ""param 1"" ""param 2"""
(All those quotes allow the spaces)
Then in the .vbs file:
Then you can vba your heart away.