Integrate .mdb with ArcGIS

B

Barry

I am not experienced with VBA programming, but have recently attended a
one-day class in which we touched on it (with a couple of simple scripts).
Now I am wondering if it will be possible to integrate some events between
Access and ESRIs ArcGIS (a mapping program). ArcGIS has a modelbuilder, in
which you can build events and export the model to Python(.py), JScript(.js),
or VBScript(.vbs).

I am most curious with the exported VBScript's use in Access. Is it
possible to import a .vbs script into Access, or copy/paste and use it in
Access, or is this script type not the same as the VBA language in Access? I
think it may work because ArcGIS also uses VBA for scripting within the
mapping program. Any help is appreciated.

Thanks.
 
T

Tom van Stiphout

On Mon, 21 Jan 2008 11:51:06 -0800, Barry

Automation Servers such as ArcGIS or Microsoft Outlook or any other
one that implements VBA have a great feature that you can set a
reference to them (code window > Tools > References). Then you can
create objects supported by that object model, and you declare (some
of) these variables "WithEvents". That allows you to "listen in" on
the events fired by the Automation Server, and write your own code.

Example:
(in a class module)
'This is the main automation server object
Public WithEvents m_olapp As Outlook.Application
'Some other object exposed by this server.
Public WithEvents m_olAppointmentItems As Outlook.Items

'In some initialization function for this class.
Set m_olNameSpace = m_olapp.GetNamespace("MAPI")
Set m_olAppointmentItems =
m_olNameSpace.GetDefaultFolder(olFolderCalendar).Items


'Automatically called when user changes an appointment in Outlook
Private Sub m_olAppointmentItems_ItemChange(ByVal Item As Object)
'TODO: Your Thing
End Sub


Yes, COM can be a beautiful thing.

-Tom.
 
B

Barry

I have imported my sample (.vbs) script into Access' Visual Basic Editor, and
ran as is (see script below post). Errors came up...the first of which
stated "Compile error: Invalid outside procedure", so I looked it up in the
Help section and added "Property" to second line (of the actual script, under
header), and ran again...then an error stated "Compile error: Argument
required for Property Let or Property Set", but I did not understand this
Help section. The "=" was highlighted on the second line of script, in this
instance.

Can anyone please look at my sample script below and help me to adjust so
that I can run the outside process from within Access (2003)?

Script starts below:

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0", "0"
 
B

Bugman

In short, yes. The best way to find out is to open MS Access and search for a
reference to a ArcGIS control (ActiveX, etc.). Look under References in MS
Access.
 
B

Barry

I have imported my sample (.vbs) script into Access' Visual Basic Editor, and
ran as is (see script below post). Errors came up...the first of which
stated "Compile error: Invalid outside procedure", so I looked it up in the
Help section and added "Property" to second line (of the actual script, under
header), and ran again...then an error stated "Compile error: Argument
required for Property Let or Property Set", but I did not understand this
Help section. The "=" was highlighted on the second line of script, in this
instance.

Can anyone please look at my sample script below and help me to adjust so
that I can run the outside process from within Access (2003)?

Script starts below:

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0", "0"
 
B

Barry

I have imported my sample (.vbs) script into Access' Visual Basic Editor, and
ran as is (see script below post). Errors came up...the first of which
stated "Compile error: Invalid outside procedure", so I looked it up in the
Help section and added "Property" to second line (of the actual script, under
header), and ran again...then an error stated "Compile error: Argument
required for Property Let or Property Set", but I did not understand this
Help section. The "=" was highlighted on the second line of script, in this
instance.

Can anyone please look at my sample script below and help me to adjust so
that I can run the outside process from within Access (2003)?

Script starts below:

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0", "0"
 
B

Barry

Here is the latest information on the subject,
http://forums.esri.com/Thread.asp?c=93&f=1729&t=244414. It still is bringing
up errors, though.
--
Barry Guidry


Barry said:
I have imported my sample (.vbs) script into Access' Visual Basic Editor, and
ran as is (see script below post). Errors came up...the first of which
stated "Compile error: Invalid outside procedure", so I looked it up in the
Help section and added "Property" to second line (of the actual script, under
header), and ran again...then an error stated "Compile error: Argument
required for Property Let or Property Set", but I did not understand this
Help section. The "=" was highlighted on the second line of script, in this
instance.

Can anyone please look at my sample script below and help me to adjust so
that I can run the outside process from within Access (2003)?

Script starts below:

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0", "0"
--
Barry Guidry


Barry said:
I am not experienced with VBA programming, but have recently attended a
one-day class in which we touched on it (with a couple of simple scripts).
Now I am wondering if it will be possible to integrate some events between
Access and ESRIs ArcGIS (a mapping program). ArcGIS has a modelbuilder, in
which you can build events and export the model to Python(.py), JScript(.js),
or VBScript(.vbs).

I am most curious with the exported VBScript's use in Access. Is it
possible to import a .vbs script into Access, or copy/paste and use it in
Access, or is this script type not the same as the VBA language in Access? I
think it may work because ArcGIS also uses VBA for scripting within the
mapping program. Any help is appreciated.

Thanks.
Click to expand...
Click to expand...
 
B

Barry

It is working now, but will not update an existing dataset. Please see thread:

http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&lang=en&cr=US
--
Barry Guidry


Barry said:
Here is the latest information on the subject,
http://forums.esri.com/Thread.asp?c=93&f=1729&t=244414. It still is bringing
up errors, though.
--
Barry Guidry


Barry said:
I have imported my sample (.vbs) script into Access' Visual Basic Editor, and
ran as is (see script below post). Errors came up...the first of which
stated "Compile error: Invalid outside procedure", so I looked it up in the
Help section and added "Property" to second line (of the actual script, under
header), and ran again...then an error stated "Compile error: Argument
required for Property Let or Property Set", but I did not understand this
Help section. The "=" was highlighted on the second line of script, in this
instance.

Can anyone please look at my sample script below and help me to adjust so
that I can run the outside process from within Access (2003)?

Script starts below:

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0", "0"
--
Barry Guidry


Barry said:
I am not experienced with VBA programming, but have recently attended a
one-day class in which we touched on it (with a couple of simple scripts).
Now I am wondering if it will be possible to integrate some events between
Access and ESRIs ArcGIS (a mapping program). ArcGIS has a modelbuilder, in
which you can build events and export the model to Python(.py), JScript(.js),
or VBScript(.vbs).

I am most curious with the exported VBScript's use in Access. Is it
possible to import a .vbs script into Access, or copy/paste and use it in
Access, or is this script type not the same as the VBA language in Access? I
think it may work because ArcGIS also uses VBA for scripting within the
mapping program. Any help is appreciated.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AccessVB versus VBS 5
Problem with VBScript that imports Access database if new version 3
Best method / software for graphical output from dynamic excel data 0
Can you use source control with VBA? 4
Automate Access table actions based on Filesystem resources? 6
ASP.Net not working with JQuery in separate JS file or in HTML 2
Surpress Confirmation Messages while updating records with VBA 3
Using VBScript behind Outlook forms with a COM-AddIn 1

Top