9

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

Here is where I added the access connection

Trying to add as a vector file 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. enter image description here

  • Did you follow my "accepted answer" (link to my answer here) instructions that are in the question "Can QGIS read an ODBC connection"? – RyanDalton Sep 29 '14 at 22:52
  • Yes, those are the instructions I am referring to. It fails when I get to QGIS to add in the .mdb file. – T.J. Sep 30 '14 at 0:24
  • I made an update to outline each step. Also I followed the instructions x64. I checked before hand. – T.J. Sep 30 '14 at 3:02
  • I've folowed all steps, and I'm able to create a ODBC conecction to a mdb file, but when I us ESRI Personal Geodatabase option, it gets me an error message. ¿Is there a problem with the file? ¿Is it possible that the file is not a geodatabase file? Thanks – user60890 Oct 16 '15 at 15:37
12

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.

Start Button

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

ODBC Data Source Window

Click on the System DSN tab

enter image description here

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.

Add Access Driver

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

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.

enter image description here

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

Final Select

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

enter image description here

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.

enter image description here

Now open up QGIS 2.4 and go to Add Vector Layer enter image description here

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

enter image description here

Now, on the same dialog, click the New Button

enter image description here

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.

enter image description here

Now click on the test button.

enter image description here

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

enter image description here

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

enter image description here

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.

enter image description here

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

enter image description here

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.

enter image description here

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.

enter image description here

  • Thanks for the help, but it will not load the tables. It says error but it is blank next error message. So now I don't know what to do. I did download Qgis 32bit and started all over. I even tried to just add and then click and drag and it pulls in the tables but they are all blank. Also Qgis 32bit on a 64 bit computer is running pretty slow and throwing errors when you try to open it. So I would not recommend using a 32 bit with a 64 bit. – T.J. Sep 30 '14 at 18:27
  • @GisDoodler, excellent post! – RyanDalton Sep 30 '14 at 19:38
  • Absolutely beautiful! The instructions as provided above worked perfectly! Thank you very much for your contribution and time spent! – user39254 Oct 15 '14 at 21:23
  • @GisDoodler: Thanks a lot! both longer and shorter method worked for me, once I shifted to 32-bit QGIS. However, one point to note: if one has tables inside the .mdb file with long names, they do not link properly. Only tables with names 8characters or less (and no spaces, no special chars) seem to link properly. – Sharad Nov 29 '16 at 12:09
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.