Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
megabyte23
Contributor III
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
m_woolf
Master II
Master II

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

View solution in original post

6 Replies
m_woolf
Master II
Master II

megabyte23
Contributor III
Contributor III
Author

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.

megabyte23
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

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.