6 Replies Latest reply: Jul 12, 2017 1:58 PM by Meg Widholm RSS

    Qlik Macro - connect Access DB with vbs

    Meg Widholm

      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
            Meg Widholm

            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
                  Meg Widholm

                  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 w

                      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
                          Meg Widholm

                          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.