4

Currently there is the following software in use: Win10 Clients, MS Access, ArcView 3.2 from year 1999

There are 3 common workflows, I describe one:

  1. Within the MS Access Form a Dataset is selected
  2. Then I click the „Show me in GIS“ Button in MS Access Form.
  3. MS Access opens ArcView and an ArcView Project with a DDE Connection, not only is this DDE Connection opening ArcView, it also takes the ID of the selected Dataset and runs a script within ArcView.
  4. The script in ArcView gets the ID from the DDE connection, goes to a known shapefile and looks into the attribute table if the ID is present, if yes, it zooms to the proper Geometry.

So no Access Database is directly connected to ArcView, but only some data is sent and received.

How it is possible to achieve the same workflow with QGIS? (The MS Access Database will stay literally forever.) I can't use a DDE connection with QGIS as far as I know.

I am not asking here for a perfect solution. A starting point would be good (PyQGIS, batch-file, ...)

Improve this question
5
  • what's the reason you can't use ODBC? What do your files look like if you export to Tab-separated text? Can you export to dBase (.dbf) format?
    – Steven Kay
    Commented Jul 7, 2017 at 22:59
  • I want to click a button IN Access, this button opens QGIS and goes to a certain geometry. How can that be done with ODBC? With ODBC I would open an Access DB in QGIS, which is not what I want or need. There is an access database and shapefiles, nothing more.
    – AndreasK
    Commented Jul 8, 2017 at 9:46
  • So the reason for the "no ODBC" condition is only that you think it's not possible?
    – Matthias Kuhn
    Commented Jul 10, 2017 at 8:18
  • 1
    Well, I did not only "think";-), I've searched extensively and tried a lot. I am still open to reasonable solution using ODBC, but I have not found any similar usage (the described workflow) of an ODBC connection.
    – AndreasK
    Commented Jul 10, 2017 at 8:26
  • AndreasK, your code worked fine, it was very useful for me! I am trying to do the same you did, passing an ID from Access to a QGIS project. I am beginning with Python and i have doubts with that part and with the way you created the environment variable in VBA. Is "AV_ID" the environment variable name, the variable QGIS will looks for? What is gstr_AV_id? is the name of a MS Access form Control or field? And on the macro part, how did you make that the python macro looks for the environment variable? Thanks for considering my request
    – Marcelo Castillo
    Commented Jun 20, 2022 at 6:17

2 Answers 2

Reset to default
3

button in Access could first write a text file with ID of data set and then launch QGIS. QGIS is started with initial script which reads the text file and does all the rest.

....
cd H:\test
start "QGIS" /B "%OSGEO4W_ROOT%"\bin\qgis-bin.exe --code external_id.py
....

It seems the file you want to be executed needs to be in current folder. Before starting QGIS through a batch file just change to the folder where your python script is (first line).

Rob

Improve this answer
3
  • Good idea, as DDE (was a crazy comfortable way) won't work at all, I try this way. My VBA skills are very basic, but it looks not too complicated.
    – AndreasK
    Commented Jul 10, 2017 at 14:36
  • Alternatively set a environment variable with the id. Less dependency on paths and will even work when triggered in parallel.
    – Matthias Kuhn
    Commented Jul 10, 2017 at 16:44
  • Thank you for the suggestion. Some search and I found it how to do with VBA, passing the variable from Access to QGIS works.
    – AndreasK
    Commented Jul 12, 2017 at 14:22
0

Here is my solution for the MS Access Part. I create the environment variable and then I start QGIS and the adequate project. In QGIS I have a Macro that looks for the variable, selects the layer and zooms to the geometry with the AV_ID

Public Sub connect56()

    Set objShell = CreateObject("WScript.Shell")
    Set colUserEnvVars = objShell.Environment("User")
    colUserEnvVars.Item("AV_ID") = gstr_AV_id

    Dim x As Variant
    Dim Path As String
    Dim Project As String

    Path = "C:\Program Files\QGIS 2.18\bin\qgis.bat"
    Project = "H:\test.qgs"

    x = Shell(Path + " " + Project, vbNormalFocus)

    Exit Sub

End Sub
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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