topic Re: Qlik Macro - connect Access DB with vbs in QlikView

Qlik Macro - connect Access DB with vbs

megabyte23 — Tue, 11 Jul 2017 21:56:21 GMT

Hi All,

I've scoured the forums today and am finding no love. I'm trying to connect to an Access database with OLE.

When the code tries to open the connection, the error is:

Provider cannot be found. It may not be properly installed.

I've installed the Microsoft Access Redistributable package. I understand the issue to be with bits.

Here's my config:

Windows: 64-bit

Qlik: 64-bit

Office: 32-bit

I really can't change any of these, unfortunately due to our required environment. Is this impossible?

Thanks for your advise.

Re: Qlik Macro - connect Access DB with vbs

m_woolf — Wed, 12 Jul 2017 12:27:42 GMT

Video Link : 5176

Re: Qlik Macro - connect Access DB with vbs

megabyte23 — Wed, 12 Jul 2017 13:42:47 GMT

Thanks- I can connect to access in the load script with no trouble.

I'm trying to connect from a vb macro- that's when I get the error.

Re: Qlik Macro - connect Access DB with vbs

m_woolf — Wed, 12 Jul 2017 14:07:38 GMT

Try this:

How to Connect to Access Database with VBScript

Re: Qlik Macro - connect Access DB with vbs

megabyte23 — Wed, 12 Jul 2017 14:16:17 GMT

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.

Re: Qlik Macro - connect Access DB with vbs

m_woolf — Wed, 12 Jul 2017 16:24:40 GMT

This worked for me:

Sub ReadAccessDB

CreateObject("WScript.Shell").Run "C:\Windows\SysWOW64\wscript.exe C:\Users\ra053499\Downloads\test.vbs"

End Sub

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

cn.Open strConnection

if err.number>0 then

msgbox err.description

msgbox "Error accessing the budget database. Try again later."

end if

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

rs.MoveFirst

end if

msgbox rs.Fields("BudgetAllotment").Value

rs.Close

cn.Close

Re: Qlik Macro - connect Access DB with vbs

megabyte23 — Wed, 12 Jul 2017 17:58:23 GMT

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:

Sub WriteAccessDB

CreateObject("WScript.Shell").Run "C:\Windows\SysWOW64\wscript.exe C:\ScriptName.vbs ""param 1"" ""param 2"""

End Sub

(All those quotes allow the spaces)

Then in the .vbs file:

msgbox Wscript.Arguments(0)

msgbox Wscript.Arguments(1)

Then you can vba your heart away.