topic Re: Load .mdb without Access installed? in QlikView

Load .mdb without Access installed?

Fri, 08 Nov 2013 16:48:40 GMT

How can I load a .mdb file in QV when I dont have MS Access installed? It seems like it needs some kind of Access standard driver!?

Re: Load .mdb without Access installed?

Gysbert_Wassenaar — Fri, 08 Nov 2013 16:50:40 GMT

You don't need MS-Access, but you do need the odbc or olebd drivers. You can install the Microsoft Access Database Engine 2010 Redistributable package that you can download from Microsofts site to install the drivers you need.

Re: Load .mdb without Access installed?

Fri, 08 Nov 2013 17:23:36 GMT

hmm, thx but perhaps a little to fast. Still it does not work. For the ODBC Connector wizard there are nothing to choose. Writing the script manually did not work either, is it still like: "ODBC connectxx to [ms access database;DBQ=...."?

- or can it be because I run the personal Edition?

Re: Load .mdb without Access installed?

Gysbert_Wassenaar — Fri, 08 Nov 2013 17:28:33 GMT

I think you will first need to define an odbc source using for example the odbcad32.exe tool.

Re: Load .mdb without Access installed?

Anonymous — Fri, 08 Nov 2013 18:14:35 GMT

And, if you're on a 64-bit Windows, you have 64- and 32-bit ODBC admins. You need the 32-bit, it is in C:\Windows\SysWOW64

Re: Load .mdb without Access installed?

jaimeaguilar — Fri, 08 Nov 2013 18:35:35 GMT

Hi,

It's easier if you follow these settings:

OLE DB (selecting force 32-bit option) > connect > Provider: Microsoft jet 4.0 > Connection: Browse for your .mdb file and type your credentials. Also is important to check the option "Allow saving password". > then click in "Test connection" to ensure that everything's right and you're done.

It's important to check the option force 32-bit because this OLEDB connector is just available in 32 bits, and you don't need to install MS Access to use this method.

regards

Re: Load .mdb without Access installed?

Fri, 08 Nov 2013 21:54:54 GMT

Sorry, I am new with all this, from your descriptions it seems like there is no such thing as ODBC 64, but in the QV edit script I can choose connect32 or 64?

Anyway Gysbert and Michael: So I found the odbcad32.exe file. I have never used this before, but I tried to guess some different things, but with no luck, so plz let me know what to do?

Jaime: I tried it, but it says "Test connection failed" (my translation, I don't use the English version). The blank password is checked, if I choose to make a password I just get another message that something is wrong with provider bla bla. bla., any further suggestions plz?

BTW: If I just drag the .mdb file into QV, I do get the File wizard :, but the data is all nonsense.

Re: Load .mdb without Access installed?

Gysbert_Wassenaar — Sat, 09 Nov 2013 09:04:45 GMT

There are no only 32-bit odbc drivers for ms-excel and ms-access. For 'real' databases there are 64-bit odbc drivers available.

OLEDB is probably easier to set up since you don't need to define an odbc data source. Here's an example:

OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;Data Source="D:\TEST\AdventureWorksSample.mdb"];

SQL SELECT * FROM Product;

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 09:20:51 GMT

Please follow these steps :

Step 1 : Open Qlikview

Step 2: File/New/ (Cancel the default excel loader dialog box)

Step 3: File/EditScript

Step 4: At last there is Data Tab go to Database and select OLE DB

Step 5: Click Connect

Step 6: In Provider Tab select Microsoft office Access Database (for .mdb or .accdb files)

if you are using Open office then you can select Microsoft Jet Provider (First Option) for Open Office Base file. You can also try for Microsoft office Access Database (for .mdb or .accdb files)

Step 7: Click Next and give the file path in Data Source

Step 8 :Test Connection and Open.

Thats all

If you find your answer please mark as correct answer .

Thanks.

Hope this will help you.

Re: Load .mdb without Access installed?

Sat, 09 Nov 2013 13:47:53 GMT

Did you mean "... no 64-bit..."?

Anyway it worked :-). I just need to get the SQL right then! I make a Load statement first and do get to wizard where I can fields in QV, but I still get a garbage message!?

Re: Load .mdb without Access installed?

Sat, 09 Nov 2013 13:51:44 GMT

Thx, but I dont have the options mentioned in step 6, how can that be?

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 13:59:55 GMT

what you get first option in provider tab ?

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 14:00:09 GMT

Please attach screen shot

Re: Load .mdb without Access installed?

Sat, 09 Nov 2013 14:31:26 GMT

The first option is the jet 4.0, no luck there.

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 15:56:26 GMT

First option

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 19:16:55 GMT

Select Microsoft Jet 4.0 OLE DB Provider and click next after this provide your file path .

If not working then please attach your .mdb file

Re: Load .mdb without Access installed?

Sat, 09 Nov 2013 19:27:00 GMT

Well for somehow reason it worked this time, thx.

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 19:27:55 GMT

Problem solved ?

Re: Load .mdb without Access installed?

agni_gold — Sat, 09 Nov 2013 19:29:03 GMT

always welcome

Re: Load .mdb without Access installed?

Sat, 09 Nov 2013 19:33:30 GMT

Well I think the case is closed, thx to all.

I am new in here and not sure about this "correct answer" thing. Jaime had it right, even though I could not make it work the first time is not his fault, can I mark two correct answers?