MS Access ODBC connection prevents reload in management console

[#1]

I have a Qlikview application that I can't get to reload automatically through the management console.  I've narrowed it down to the data in an Access db that keeps in from running successfully.  I've tried changing the connection from ODBC to OLE DB without success.  The reload runs fine when I trigger it within the Qlikview designer.  I've verified that the Qlikview user has access to all the files/databases that I'm trying to hit, but it just won't run successfully.  Anyone have any suggestions?

[#2]

Bill,

I noticed that too, but when I set up the ODBC connection I used the network address and not the mapped drive.  I even went as far as to disconnect the mapped drive from the server, but that still didn't work.  I'll give it another shot and see what I can come up with.

[#3]

I tried to rebuild the connection in the Qlikview document and just like previous tries it writes out the network path in the script itself, but if the drive is not mapped on the server it throws an error when I do a manual reload and I still get the same error in the log when I try to trigger it from the management console.

Here's my connection line in the script: ODBC CONNECT32 TO [MS Access Database;DBQ=\\MSP-APPS01\Marketline$\StoneTimberRiver\Documentation\MarketLine Data Extract 20121120.mdb];

[#4]

Hi Jared - a few more things to check.

-did you open qlikview itself while logged into the server andbuild the string again? by that I mean go into the connection wizard and use the options it gives you.

Check the version of qlikview on the server. Is it X32 or X64? Try writing "connect" instead of connect32 this will connect according to the machine you are running on.

When you are logged in as the server, does it let you navigate to \\MSP-APPS01\Marketline$\StoneTimberRiver\.....?

I have had similar issues in the past!

Erica

[#5]

I rebuilt the string again and it gives me the exact same line as before.

When I write "connect" instead of connect32 I get an error because the machine is a 64-bit machine and trying to get to my access db won't work that way so I need the connect32.

The version on the Qlikview server is X64.

When I log into the server I am able to navigate to the folder that the db is stored in.

[Anonymous]

Jared

I am not sure if you can edit the title of this thread, as if you maybe change the thread title to show the issue has distilled to be one of MS Access ODBC connection set up then those familiar with may well notice.

I have never used an MS Access ODBC connection, so cannot offer any suggestions.

Best Regards,     Bill

[#7]

Good suggestion, I've renamed it.

[#8]

Hi Jared, did you get this working?

I took interest because we had a similar issue, which resolved eventually with an oledb rather than odbc connect statement. Our development environment is 32 bit but the server is 64.

For interest the connection string is:

OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\server.domain.loc\Shared_Info\...\the access db.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;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=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False];

The biggest issue we had was with access (i think) we had to install the drivers separately on the server before we could get it to work, but only got to that stage after loggin into the server as the QMC account to do our troubleshooting.

Best resolution: we are working to get this process off access...!

Erica

[#9]

Erica,

We haven't been able to get it to work yet.  We've installed drivers on the server and done some of the things you mention.  We did find a 64 bit ODBC driver for access and changed to that instead of the 32 bit, but that didn't make a difference either.  We've also tried the OLEDB, but that was unsuccessful.

We're at the point where we've contacted Qlikview to get their help on what is going on, but haven't received a response on how to fix it yet.  What I find odd is that when I run the reload on my local PC it seems to work just fine, but when the reload is run using the management console it tries to look for the access db in a place that it shouldn't be looking.  Meaning my db is in one folder, which basically contains a dumbed down version of the operational db, but it somehow found the folder where the operational db is and wants to look there.  I'm still not sure how it seems to "know" where the operational db is in the first place.

I'll definitely post the resolution when we figure it out.

Jared

[#10 · Accepted solution]

We finally got this one figured out...

Our situation is that we have Qlikview with a connection to an Access db and it was not refreshing on the Management Console.  The error we kept getting was that the database had an invalid path, but this whole time we've used the unc path or network address to get it to work.

Our particular situation was that the Access db we were using was just a copy of the operational Access db (this was done because the operational db had undecipherable table names and columns and we needed ones that were decipherable for use in excel) and when Qlikview Management Console would try to update it would somehow get the path to the operational db on the refresh.  Upon further digging we found that the copy had a mapped drive in the path it was using for the operational and so once we changed that to a unc path then it finally worked.

So, all this time it wasn't anything with Qlikview, it was because we had a mapped drive in the path in Access.