topic Re: Opening MS Access Form with Query in Transportation Questions

Opening MS Access Form with Query

Anonymous User — Sat, 11 Dec 2021 18:50:58 GMT

Original User: wjackson

My little Python script is intended to run in a Python-Addin tool where the user clicks on a parcel, grabs the PIN attribute and then opens an MS Access form with the PIN as a parameter to a query. I'm very close to getting this working but can't seem to get Access to open the form with the parameter.

Thanks again to Mike Hunter for the assist with the parcel feature selection.

import arcpy
from arcpy import env
import pythonaddins
import win32com.client, types, pythoncom, sys, os, string 
import subprocess

class ToolClass2(object):
    """Implementation for ArcGISAddins_addin.tool (Tool)"""
    def onMouseDownMap(self, x, y, button, shift):
        
        # set up environment
        arcpy.env.workspace = "C:/temp/python/test.gdb"
        Parcels = "C:/temp/python/test.gdb/Parcels"
        mxd = arcpy.mapping.MapDocument("current")
        df = mxd.activeDataFrame

        # build point geometry, run your getSearchDistanceInches function    
        pointGeom = arcpy.PointGeometry(arcpy.Point(x, y), mxd.activeDataFrame.spatialReference)
        #searchdistance = getSearchDistanceInches(df.scale, 3)

        # make an arcpy.mapping layer obj and do the selection by location
        plyr = arcpy.mapping.Layer(Parcels)
        arcpy.SelectLayerByLocation_management(plyr, "INTERSECT", pointGeom, "0 meters","NEW_SELECTION")
        arcpy.CopyFeatures_management(plyr, "climate")

        fc = "C:/temp/python/test.gdb/selected_parcel"
        # Create a search cursor 
        #
        rows = arcpy.SearchCursor(fc)
        # Create a list of string fields
        fields = arcpy.ListFields(fc, "", "String")

        for row in rows:
            for field in fields:
                if field.name == "PIN":
                    pin = row.getValue(field.name)
                    out = "%s: Value = %s" % (field.name, pin)
                    pythonaddins.MessageBox(out, "My Coordinates")

        # all done, now add our layer to the map
        #arcpy.mapping.AddLayer(df, plyr, "BOTTOM")
        #arcpy.RefreshActiveView()

strDbName = "BloomingtonQuad.mdb"
#print strDbName
PIN = "2123301001"
path = "N:/Work/20130419_COBTA_Application/Query_BlmTransfer"
accapp = win32com.client.Dispatch("Access.Application")
accapp.Visible = True
accapp.opencurrentdatabase(path + '/' + strDbName, False)
accapp.OpenForm(frmQuad)
accapp.Forms("frmQuad").controls("TxtID").Value = PIN
#accapp.Application.CloseCurrentDatabase()
#accapp.Application.Quit()

Re: Opening MS Access Form with Query

TonyAlmeida — Tue, 07 May 2013 20:11:04 GMT

Hope this helps.

import subprocess
subprocess.Popen([r'C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE',r'\\vopcc\gis\GISOPS\StreetNetwork\Routing.mdb'])

import subprocess
subprocess.Popen([r'C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe',r'C:\GIS Projects\TaxChange\Export\Change.xls']

Re: Opening MS Access Form with Query

Anonymous User — Sat, 11 Dec 2021 18:51:02 GMT

Original User: wjackson

Tony,

Thanks, I was able to get this working similar to VBA using the following:

import win32com.client, types, pythoncom, sys, os, string

strDbName = "access.mdb"
path = "C:/Some_Path"
accapp = win32com.client.Dispatch("Access.Application")
accapp.Visible = True
accapp.opencurrentdatabase(path + '/' + strDbName, False)
accapp.openform("frmMyForm")
accapp.Forms("frmMyForm").controls("TxtID").Value = pin

Hope this helps.

import subprocess
subprocess.Popen([r'C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE',r'\\vopcc\gis\GISOPS\StreetNetwork\Routing.mdb'])

import subprocess
subprocess.Popen([r'C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe',r'C:\GIS Projects\TaxChange\Export\Change.xls']

Re: Opening MS Access Form with Query

Pierre-ÉtienneLord — Mon, 16 Dec 2013 19:10:05 GMT

I am currently trying to make the same development. I am not able to display to the MS Access form. I managed to open the DB but not the form with the clause "where".

Did you create an event on your MS Access form? Thus, would it be possible to share this event code or the complete MS Access mdb?

Thanks,

Pierre