QGIS 2.4 access .mdb add error

Question

So I followed the instruction here: Can QGIS read an ODBC connection? for adding a .mdb into QGIS. There are great instructions about 1/3 of the way down. But I still can't get it to open. I on Windows 7 with QGIS 2.4 I restarted QGIS and my computer. I am still getting the error: "Invalid Data Source: ODBC:co2014ProdSumm (btw this is the .mdb) is not a valid or recognized data source. "

These are not vector layers nor is this a ESRI personal geodatabase. This is an Access database with 2 tables I need out of it.

I also found a few other posts How to access a .mdb file (Microsoft Access) in Qgis (with windows 7)? How to import info from mdb file into a project?

and this one might be the answer but I don't know what it's talking about: Microsoft Access mdb "is not a valid or recognized data source" in QGIS

Is there any way to add a .mdb into QGIS? Also I do not have Access on my computer.

Edit 1:I hope this clarifies my issue. Each time I get Invalid Data Source: C:...Co 2014 Annual Production Summary-xp.mdb is not a valid or recognized data source. Here is where I added the access connection

Trying to add as a vector file

Trying to add a database, I also tried local:host 3000 The error in the pic is the same no matter the source, name etc... I've tried changing all fields around.

Answer 1

Note there are two ways of doing this. The way you are going about doing it is the long way but that is the way I am going to show how to do it here. The other way is by just going to Add Vector Layer and then selecting file and then browsing to where your .mdb file is located and opening the .mdb file.

First, you need to make sure QGIS 32 bit version is being used. Download and install QGIS for Windows 32 bit. You need a 32 bit version of QGIS because Microsoft does not make a 64 bit driver for Access(.mdb). Esri ArcMap is 32 bit too so it can work with .mdb files.

Second, you need to create a DSN(Data Source Name). To create a DSN, Go to your start button and click it.

Next in the search box, type %windir%\SysWoW64\odbcad32.exe and click on the odbcad32.exe program.

Click on the System DSN tab

Click on the add button to add a dsn so ODBC and Esri Personal Database.mdb can be accessed. Once you click on the Add button a new dialog will appear. On this dialog, select Microsoft Access Driver(*.mdb) and click finish.

After clicking finish, another dialog will appear. In this dialog, put QGIS for the Data Source Name.

Next click on the Select button under Databases. Once you select this button, another dialog will appear.It is the select database dialog.

On this dialog, you need to navigate to your .mdb file. Once you are in the folder the .mdb file will appear in the left box. Select your .mdb file and click OK

Now you should see the ODBC Microsoft Access Setup Dialog. The Dialog should show you your .mdb file like the dialog box below.

Now click OK on the dialog box. Also make note, the DATA Source Name is QGIS. We will use this name when setting up an ODBC .mdb / Esri Personal GeoDatabase.mdb. Once you click ok you should see a dialog box like the following, which shows the DSN you just created. Click ok on this dialog box to close it out.

Now open up QGIS 2.4 and go to Add Vector Layer

Now, Select database and then select your Encoding you want and then under Database Type select Esri Personal GeoDatabase

Now, on the same dialog, click the New Button

A new dialog box will appear. On this dialog box, fill out the Type, Name and Database fields. For the Type field, select Esri Personal GeoDatabase. For the Name, type whatever you want. For the Database field, type QGIS. Note that QGIS is the same name as the DSN that we created earlier.

Now click on the test button.

If everything is setup correctly, you will see this after clicking on the test button.

Next click ok to close out the test and then click Ok to finish the database connection.

Now you should see the name you gave the connection and once you do, click the Open Button. Once you click open, it will ask for a password. Just click ok, unless the database has a password.

After clicking ok on the password dialog box, you will see a dialog like below:

Select your layer and click ok. After selecting the layer you want and clicking ok you will see another dialog box. This dialog box is for setting coordinate references.

And we are done! You can do this with regular .mdb or Esri Personal GeoDatabase. To do it this way, you need to create a DSN and then set up a connection. There is a simpler way and that is just by going to add vector and making sure File is selected and then just browsing to your .mdb. Note, you need QGIS 32 bit to work with .mdb databases.

Answer 2

Adding to the ODBC post from GisDoodler, you can use a Virtual File to connect to a table of an Access database, and define the Geometry columns manually. The Virtual file can look like this:

<OGRVRTDataSource>

<OGRVRTLayer name="worms">
    <SrcDataSource>ODBC:DISEASE,worms</SrcDataSource>
<SrcLayer>worms</SrcLayer>
<GeometryType>wkbPoint</GeometryType>
    <LayerSRS>WGS84</LayerSRS>
<GeometryField encoding="PointFromColumns" x="x" y="y"/>
</OGRVRTLayer>

</OGRVRTDataSource>

You still have to do the ODBC-DSN stuff, and need a 32-bit version of QGIS. Instead of opening a database connection, you add the vrt file to the table of content.