3

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 attibute 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.

The question: How it is possible to achive 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, ...)

  • 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 Jul 7 '17 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 Jul 8 '17 at 9:46
  • So the reason for the "no ODBC" condition is only that you think it's not possible? – Matthias Kuhn Jul 10 '17 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 Jul 10 '17 at 8:26
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

  • 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 Jul 10 '17 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 Jul 10 '17 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 Jul 12 '17 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

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.