Qlik Macro - connect Access DB with vbs

[megabyte23]

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.

[m_woolf]

Video Link : 5176

[megabyte23]

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.

[m_woolf]

Try this:

How to Connect to Access Database with VBScript

[megabyte23]

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.

[m_woolf · Accepted solution]

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

[megabyte23]

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.