Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
megabyte23
New Contributor III

Qlik Macro - connect Access DB with vbs

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.

1 Solution

Accepted Solutions
mwoolf
Honored Contributor II

Re: Qlik Macro - connect Access DB with vbs

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 OLEDBSmiley Very Happyatabase 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

6 Replies
mwoolf
Honored Contributor II

Re: Qlik Macro - connect Access DB with vbs

megabyte23
New Contributor III

Re: Qlik Macro - connect Access DB with vbs

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.

mwoolf
Honored Contributor II

Re: Qlik Macro - connect Access DB with vbs

megabyte23
New Contributor III

Re: Qlik Macro - connect Access DB with vbs

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.

mwoolf
Honored Contributor II

Re: Qlik Macro - connect Access DB with vbs

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 OLEDBSmiley Very Happyatabase 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
New Contributor III

Re: Qlik Macro - connect Access DB with vbs

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.

Community Browser