topic Re: Problem with Qlikview and MS Access 2007 database in QlikView

Problem with Qlikview and MS Access 2007 database

Tue, 04 Dec 2012 21:02:16 GMT

Hello, world!

I am trying to interface a Microsoft Access 2007 (.accdb) database with Qlikview v11 SR2. The database loads with no issues, but when I close Qlikview and attempt to modify any of the tables, Access locks up entirely. I have tried opening the Access DB in exclusive mode, I have tried restarting the computer then loading Access first, both to no avail. I can open the database in read-only mode, but this does not help me in modifying the database. I tried creating a blank database, as well, then importing the tables in to the new database, which also failed. I also tried with and without the disconnect statement and again, no change.

The added part (below the stock settings) of my loading script is as follows. Note that I transcribed this from the separate, standalone computer on which Qlikview is installed. Also note that I had the mode set to the default of "Share Deny None" and had the same results.

OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\users\user1\desktop\databasename.accdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=""; Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops:2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=True;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False];

//-------- Start Multiple Select Statements --------

SQL SELECT *

FROM Table1;

SQL SELECT *

FROM Table2;

SQL SELECT *

FROM Table3;

SQL SELECT *

FROM Table4;

//-------- End Multiple Select Statements --------

DisConnect;

I am at a complete loss. Does anyone have any suggestions for me?

Thank you!

David

Re: Problem with Qlikview and MS Access 2007 database

flipside — Wed, 05 Dec 2012 15:14:01 GMT

Have you tried Mode=Read instead of Mode= ReadWrite?

flipside

Re: Problem with Qlikview and MS Access 2007 database

Wed, 05 Dec 2012 15:45:42 GMT

Hi flipside,

Thanks for your reply. I hadn't tried it, but I just did and it did not change the results. Qlikview still accesses the database without issue but the file continues to hang Access upon opening any of the tables - I should emphasize that Access opens the database, per se, without any issue. It hangs only upon trying to access a table, unless it is opened in Access in read-only mode. That seems to indicate some sort of locking issue but I can't see any reason why that would be the case once QV has closed.

Re: Problem with Qlikview and MS Access 2007 database

flipside — Wed, 05 Dec 2012 19:28:06 GMT

Now I've just tried this at home and cannot replicate it BUT in QV10 SR4 I'm not sure the disconnect actually does disconnect the connection - I can still select data from a table in the connection (both OLEDB and ODBC) AFTER I've used the disconnect command. Have had a quick search on here but cannot see any logged bugs relating to this.

I wonder if instead of disconnect, you try a new connection to another datasource (or maybe even the same one) to see if it forces a disconnect that way. This might not be the reason, but it's worth a try. Even so, all connections should drop automatically at the end of the script.

Also, are you seeing the .laccdb file in the same folder as the .accdb file?

flipside

Re: Problem with Qlikview and MS Access 2007 database

Wed, 05 Dec 2012 19:37:41 GMT

We may be on to something here. I created a dummy QVW attached to a dummy Office 2007 Access db. When I hit reload to load the script for the first time, it hung Qlikview. Killing Qlikview and restarting it allowed me to load the dummy table in. However, upon closing Qlikview and reloading the Access DB I want to modify, it still hangs Access. When Access crashes it does leave its lock file behind, in the same folder.