Hello,
I am new to ArcMap VBA coding and was wondering if there is any good websites to help me understand what I am doing wrong. I have a basic knowledge of VBA for coding in access.
thanks in advance
...And that's why I like web development. Javascript will ALWAYS be 'the thing'. But even if it isn't, .NET will. or PHP. I have 15 years of VBA experience - it might as well be COBOL experience. But my web experience - that's gonna be around for awhile.
Yeah, but it was hard to understand where you’re reference to the specific line number was… the extra line spacing caused some confusion..
Free free to email it to me directly… gregg@ptarmigansoftware.com <mailto:gregg@ptarmigansoftware.com>
I’ll gladly take a look at it…
Gregg Noland, did you get a chance to look at my original code?
Many replies have suggested switching to python. Python doesn't do every thing VBA scripts could do ... low level arcobject control. You can get that with VB.Net or C#. If you know VBA then going to VB.Net is not such a great leap.
My role has been converting our VBA ArcMap customizations to VB.NET. They involve a lot of arcobjects. I started out knowing some VB. I used the venerable book "Getting to Know ArcObjects" to get how arcobjects work. There are many little changes, but these get to be routine. Some relate to changes in VB from VBA to .Net, and some are specific to esri. Converting the use of Variant to Object can be a pain. However the biggest changes are in Windows forms e.g. how treeviews are traversed has been substantially changed.
The other area of knowledge upgrade was getting fully into object orientated programming paradigm which is not really are the core of VBA. This changes the way you program ....
VBA has been deprecated since ArcGIS 10.0 (years!)
That said, it is still available with 10.3.1 so you could use it. You (and all users of your app) do need to install "VBA Compatibility" which is a separate installer from the main Desktop install. All the normal VBA commands to control another application should work -- but you do need to tackle ArcObjects to interact with ArcMap, which is a heavy lift especially if you are unfamiliar with ArcMap. I'd get some assistance from someone who knows the software better.
If you decide to tackle doing this in python at some point (good idea) -- it sounds like you are trying to control the access application. If that's the case, I highly recommend installing PyWin32 and using win32com to control the application.
I found Ralf Schmidt answer most similar to my experiences. I uses VBA all the time for rapid prototyping of functions that I will ultimately want to convert into VB .NET. I find using Visual Studio a faff when all I want to do is test if Interface X works in the way I expect it to (because I can't tell from the API)? Rather than create an addin, waste time waiting for visual studio to compile then run in debug mode I just do it in VBA, way more quicker.
But as everyone says Python and its bewildering array of modules is here to stay and has replaced VBA as the de facto scripting environment.
I was curious about who still used VBA so created the following poll which I suspect some of you have already contributed to.
I agree that despite ongoing warnings, Esri probably will continue to have VBA compatibility in 10.x (it costs them nothing to roll it in after all), but I doubt they will offer any support at all. I suspect if you were to take a VBA issue to support they would respond with 'not a supported feature, please update your code'. That said, you never know when they might yank the carpet out from underfoot - they have been making this call for at least seven years.
Fair call on prototyping, and that is essentially what we were doing as well. Unfortunately a lot of the time schedule demands mean that the prototype gets badged as production for small projects instead of going through full product development...
Good rule of thumb…..
If you’re needing a more user friendly UI for casual users, interfacing with other enterprise systems via databases or web services, or need more fine grained access to AO for things like stepping through the feature connectivity in a geometric network, .NET is the way to go….
10.4 will be launched shortly and there will probably be a 10.4.1. Taking the 6 year of support cycle you will be "save" with ArcMap until 2022.
BUT, do you really want to get stuck with a solution in a 32 bits environment when you could do this in a 64 bits environment and benefit from what Pro has to offer?
Ralf Schmidt is totally right that using .NET can be very powerful and looking at the workflow as the OP posted in another thread here: Help with Run time error 91 it could be the way to go, since a selection is made with the tool which loads a form with a list of selected features allowing the user to select one of them and then it does something in Access.
BUT... I totally agree with James Crandall if you can change the workflow and move the data into a File Geodatabase a wealth of capabilities will be enabled.
A slide I like to include in my presentations on Python in the ArcGIS Platform is the one below:
... and yes, I truly believe that when you are not using Python you are probably doing thing the "hard way".
True that using .NET allows you to enhance the UI/UX of the workflow, but do you really want to introduce the additional complexity into your project.
To give you some pointers (based on a possible Python implementation):
BUT to show a list of features found in a form you will probably have to use something like TKinter.
The advance of Python is that you could make code that works for ArcMap and for ArcGIS Pro. You will not be able to do that with .NET, since ArcMap is based on ArcObjects and ArcObjects no longer exists in Pro.
So the question is, how long will ArcMap be around? Would it be safe to assume that 10.4 will likely be the last version of ArcMap? And then Pro will just take over from there?
I just want to note that VBA in ArcMap will be there for probably as long as ArcMap is there. Citation:
"Esri will continue to ship Microsoft Visual Basic for Applications (VBA) compatibility setup with ArcGIS as long as we continue to ship ArcMap or until Microsoft notifies us that we can no longer distribute this."
from: http://downloads2.esri.com/support/TechArticles/W32809_DEPRECATION_PLAN_FOR_ARCGIS_10_3_and_10_2_x__022715_without_10_1_…
Although we never used VBA in customer projects (because it's not suitable for professional software development) I still use it for prototyping, experiments, and one-off data processing because it's more powerful than python and you can change code during debugging to a much higher degree compared to C# and VB.NET.
Just move entirely into a File Geodatabase and model your existing Access tables there, relate them to any Feature Classes, set domains/subtypes, editor tracking and be done with it. You will be opening up your implementation into a whole new world and have capabilities you likely don't even know about (like easily publishing to the web, collector, Web AppBuilder, StoryMaps, and on and on...).
VBA is a dead-end street that has a bunch of foreclosed properties down it.
As far as connecting to an Access database I'd recommend the pypyodbc module:
import pypyodbc DB = r'C:\Folder\test.mdb'; DRV = '{Microsoft Access Driver (*.mdb)}'; PWD = '' conn = pypyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD)) SQL = 'SELECT * FROM Table' records = conn.cursor().execute(SQL).fetchall() for record in records: print record conn.close()
I also recommend looking into setuptools and pip for installing python modules that do not come with the standard install:
setuptools 20.0 : Python Package Index
pip 8.0.2 : Python Package Index
Hey, Ethan....
Been working with ArcMap/VBA since ArcGIS 8.0 beta, so I'd be glad to answer any questions you have about it. But like the others have suggested, moving to something supported would be a good idea. Python can do a lot of stuff, but it can't do all of the things VBA and ArcObjects could do, so I'd suggest that if you need that much power, you move to VB.NET. The express version is free. It's even easier to build tools for ArcMap not that it has been in the past.
I'll look for your original error message from your VBA and see what I can determine from just that.
Gregg@ptarmigansoftware.com
It's worthwhile noting that Esri has been warning users to switch over and threatening to get rid of VBA for many years, since 10.0, and you are developing in what is essentially an unsupported environment.
ArcGIS Desktop and VBA? | ArcGIS Blog (2009)
At ArcGIS 10.1 there will not be an ArcObjects VBA SDK nor will new VBA development with ArcGIS for Desktop be supported. There will be an optional separate setup for legacy VBA support in the ArcGIS applications. This will require an additional licence authorisation similar to ArcGIS 10.
VBA will be available if needed so existing solutions can continue to work while the code is being migrated to .NET. We recommend all users with VBA code start migrating now to an add-in or custom extension using the ArcObjects SDK for .NET or Java. This is the end of the support of VBA as a development or customisation language for ArcGIS."
- See more at: https://esriaustralia.com.au/products-arcgis-software-101-faqs#7 " (2011, emphasis mine)
To actually answer your question there's quite a bit of legacy code still available at places like stackoverflow and Geographic Information Systems Stack Exchange. I also use a lot of the original resources such as ArcObjects 10 VBA SDK Help.
Asides from the ArcObjects-specific help, there is a wealth of internet VBA resources, mostly centered around Microsoft Office, particularly Excel if you're looking for a non-GIS coding solution. So, if for example you want to get VBA to talk to Access then visiting MS support sites and forums would be the way to go.
Edit: Reworded last sentence so it didn't misconstrue Ethan's question.
I would use the win32com api to communicate with access via python. This way you can open the existing form.
Please note that win32com is no longer installed by default with the newer versions of ArcGIS
I have included a snippet of code below to help
import win32com.client # Create the Access object app=win32com.client.Dispatch("Access.Application") accessDB = r'path to the mdb here' try: # Make Application Visible app.Visible=1 app.OpenCurrentDatabase(accessDB) # https://msdn.microsoft.com/EN-US/library/office/ff820845.aspx app.DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs) # https://msdn.microsoft.com/EN-US/library/office/ff192676.aspx #app.DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) # https://msdn.microsoft.com/en-us/library/office/ff192075.aspx #app.DoCmd.RunMacro(MacroName) except: # Add your error handler pass # run these statements no matter where the program goes finally: if app: app.CloseCurrentDatabase app.Quit() del app
You could use pypyodbc 1.1.5 : Python Package Index to work with Access files in Python, but I also see you have forms in the code you posted here Help with Run time error 91 . I didn't look at it in detail and I don't use ArcObjects anymore.
I do believe there is a lot you can do with Python and it is better prepared for the future (VBA will disappear).
If you migrate your database into ArcMap, then it could be as simple as using the Info button or as complex as you like.. either through Arcpy functions and/or python. I just don't know what level of experience you have with ArcMap and I am not sure if you want to migrate your data into a new form
Here's what I am looking for with access. I have created a database of all of my company's work orders and their location in the county. So currently I am trying to create a tool that when you click on that location on the map. it opens access to that location in the form that has been created. Is this possible with Python? If so I am more then willing to attempt to learn that. As I do prefer to use the most current things.
I was once big time into VBA so I feel your pain in trying to switch over to something else. But, like others have said, it is time to make the switch and I would recommend Python.
I am not fully sure on opening MS Access with Python but I found this website that mentions how to connect to an Access database so I would imagine that this would be able to direct you in the path you want to go:
http://stackoverflow.com/questions/1047580/ms-access-library-for-python
Skip access, export to csv format, then anything can read it.
As you move forward from the python 2.7 etc to python 3.4 and ArcGIS PRO and ... dare I suggest ... maybe even 10.4, you have access to Pandas and Numpy
Importing Access files
or just bring it into Arc* for use there.
I can't answer that either but I bet one of these guys will chime in :
Darren Wiens
Xander Bakker
[edit] take a look here too: Another GIS Blog: Using pyODBC to Connect To Access with ArcPy
I don;t have any experience with that so I'll bow out, but didn't did find this
https://wiki.python.org/moin/Microsoft%20Access
re: python.
Might be a different answer if you are talking personal GDB that used Access. I'll let Dan Patterson or some of the other python-heads get into that. Might be more than you need re: you original question. But I bet the link to the other question you have could be re-written by someone in python (I have no time right now to get into it)
Does python have the ability to open microsoft access database?
Rebecca is spot on; python is well worth your while to pursue. It'll be the thing until the next big thing comes along, which has already happened to VBA.
I posted another question in here last week with the code and error and more info on it. its Help with Run time error 91.
Good reason (although long term, I would suggest thinking about a switch...just because many more resources).
It would help if you could post a snapshot of the error you are getting. Depending on the version of ArcMap, and when it was installed, it may be that you just need to install the "VBA compatibility" for the version. This can be downloaded in the myesri page under the "additiona products" for the ArcGIS version you have installed.
If you aren't the esri license manager/administrator, you may need to go ask them for permission or to download it for you.
I would also try doing a browser search for something like "esri vba help"
Hope that helps.
The only reason I am using VBA is due to the experience I do have with it. I am trying to create a tool. Or rather copy a tool from a ArcMap that my office has. but it has an error in it.
Just curious why you are going with VBA and not the newer Python (arcpy)? What version of ArcMap are you using?
Also, what are you trying to do with the code? Are you just using it in the field calculator? Or are you trying to create a tool?
Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
