A dream: What I do miss about Access. (1 Viewer)

[KitaYama]

I'm a novice Access user and my dreams are not as necessary as most of you, but well I do miss some functionalities being added to Access.
I know guys in control don't hear us, no matter how loud we shout. After all it took them more than 20 years to add a decent sql editor (which still is not decent).
I also am aware these are only dreams and will never come true, but at least mentioning my expectations here, can be a relief for me. I can say I asked for it, nobody answered.

I think Access can be better with these options:

1. Search utility for vba project window
   When you have a large project with hundreds of forms, reports, modules, .... it's a pain to find a specific object in the tree. A search box like navigation bar can help a lot.
2. An option for hiding Type column from Locals window
   If I'm debugging a code, I know my variable data types. The Type column takes one third of the width of Locals window, and every time I'm debugging, I have to drag and minimize it to be able to see Value column better (specially for variables holding sql statement or strings). Frequently I have to add a Debug.Print to see the actual value that is not fit in the width of Value column.
3. Being able to delete alias from query design
   When I inherit a database, or open a query that I've written long ago and uses alias for tables, and it has a complicated sql, It's more understandable to see the actual table names instead of a one letter alias. I prefer to see tblOrders.RegisteredOn rather than o.RegisteredOn. Some of you may have a intel core I15 brain and don't need it, but still I think clicking a table in a query design view and deleting the alias in property sheet, should revert the actual table name, instead of an error.
4. Having a way to list all functions in a project and search through them
   At present, I use a table for this purpose. Any time I add a sub/function, I have to add it to a table to be able to track it.
   When you want to add a functionality to your database, you write a new code that certainly calls some functions. You remember a lot of your functions, but my money is on nobody remembers a function he/she has written 10 years ago. So mostly we end up having duplicate functions that do the same task. I know your suggestion would be use a meaningful function name, but still there's a good possibility to end up functions doing similar tasks in a database with thousands and thousands of lines of code.
5. Keyword highlighting in vbe
   Clicking a keyword/variable..... , highlights all instances of the keyword in current function/sub. Even an application as simple as notepad++ can do it. why not Access?

What do you need to be added to current functionality of Access?

 

[theDBguy]

Not sure if this helps with number 4, but if I remember the name of a function I created a long time ago but not sure if I already put it in the current database, what I do is type its name in the Immediate Window and then hit Shift+F2 to quickly find it.

 

[KitaYama]

but if I remember the name of a function

That is my problem right there. That's a big IF.
The problem is one may have forgotten he's written a function with similar or exact same functionality several years ago.

A while back, I wrote a function to zip and add a password to a drawing and send it by mail. For this task, I wrote a function to find where the drawing is saved based on several rules to be able to zip it.
It was only days later that I found I had a function with a slightly different name to find the saved drawing on server.

 

[MajP]

I create a lot of standard modules and application specific. Example
MdlFormUtilities
MdlRelinkUtilities
MdlApplicationUtilities
...
Then if I know the procedure deals with form manipulation I simply type "MdlFormUtilities." And use intellisense.

 

[MajP]

@KitaYama
Do you use MZtools, rubberduck, or any Other Vbide addins?

 

[KitaYama]

@KitaYama
Do you use MZtools, rubberduck, or any Other Vbide addins?

Only MZtools

 

[Galaxiom]

It isn't hard to write a sub to read the VBA to list all the functions in a project and write them to a table. The parameter names and their datatypes could be included as fields.

Every Access developer should get the free V-tools plugin. Total Deep Search is incredibly useful an has a Replace function that goes many places where the Access Search doesn't look.

 

[KitaYama]

Every Access developer should get the free V-tools plugin.

The following form is what I have at present.
I can type a word in the combo, and press enter. All the functions that contains the keyword in their name or purpose are shown.
In bellow screen shot, I've searched for Drawing and can see a list of functions I have. From there, I can check if I need a new function or use something I already have.
If I search the project for Drawing keyword with MZ-Tools, I receive 1,828 hits. That doesn't help much.
Does V-Tools has an option to search only for functions/Subs names?

 

[Edgar_]

Take a look at Twin Basic. You could create an Add-In that helps with some of those. However, I do certainly believe the VBE is outdated as hell.

What do you need to be added to current functionality of Access?

VBE
> Code folding: collapsing blocks of code
> Multi cursor: editing multiple parts of the code simultaneously with the same keystrokes
> Listing methods in the debuggers
> Code completion
> Ctrl + Click to go to the definition

And more

 

[Josef P.]

It's time to update the VBA editor. In principle, Microsoft already has a suitable basis: the Monaco Editor.
However, my hope that Microsoft will come up with optimizations for the VBA editor is close to zero.

I have more hope for the project by Wayne Phillips (for me the real "master of VBA/VBIDE universe" ): "twinBasic for applications"

A quick side thought:
What is Microsoft doing right now? ... They want to reactivate the version control system they shut down some time ago (and hopefully make it better than before).
I see the positive side of this as a sign that they have once again recognized that Access can be used for application development. But how high is the demand for this adjustment? How many access developers actually use source code management? And among those who do, who really needs something new?
It would be more helpful, for example, to optimize the VBA editor for the use of source code management. For example, the capitalization of code “words” (variables, function names, ...) should not be adjusted project-wide, but only where there is a reference. (My expectation that this will happen is close to 0.)

Fortunately, VBE add-ins are available.

 

[AHeyne]

In terms of Access, I would like to have for example controls like 'FlowLayoutPanel' or 'TableLayoutPanel' in Forms to better organize controls.

In VBA I miss e.g.:
- Reflection
- Programmatic access to the call stack
- Constructor/Parameter overloading
- Generic types
- Nullable types
- Support for the 'Decimal' type

 

[AHeyne]

It would be more helpful, for example, to optimize the VBA editor for the use of source code management. For example, the capitalization of code “words” (variables, function names, ...) should not be adjusted project-wide, but only where there is a reference. (My expectation that this will happen is close to 0.)

It should be noted that the Microsoft Access team, which is well-disposed towards our Access developer community, unfortunately has nothing directly to do with the VBA/VBE team.
Therefore, my hopes for an extended VBA (see above) are unfortunately close to zero, too.

 

[GaP42]

In addition, I would also like to suggest improvements to the relationships diagramming tool.
It is frustrating that you cannot set up separate subject area diagrams showing just those selected tables and relationships needed to focus on with a particular area of work
Multiple annotation blocks on the diagram would be nice, capable of being expanded to show/enter further text
It is also annoying that the efforts at arranging the tables and relationships in the back end are not preserved in the view of the relationships from the front end. I would expect that if the database is split then access should know where to get the relationships info to present in the diagram. Side and temp front end tables - hmm - subject area concept may assist - as a "type". The Relationships diagramming tool would then allow selection of the appropriate diagram : global / by subject area name.
Printing - to pdf etc - again the subject area concept may help to produce manageable output.
Not asking a full blown CASE tool.

 

[CJ_London]

I nearly finished a QBE app to complement my access studio app, but life got in the way and with the advent of Monaco I’m not sure it is worth completing - although if that doesn’t improve I may do so.

The QBE could probably be adapted to work with relationships but has the issue of form size - the current limit of 22”. Might be worth pursuing when widescreen support becomes available. In addition to the relationships table it may also be necessary to have a complementary table to store grouping data

If there is genuine interest I could do a feasibility review to see what could be done

One thing it wouldn’t have that the current relationships window has is the ability to go to table edit view if you were viewing relationships in a different file - unless of course you wrote an app to do that

 

[DickyP]

It would be more helpful, for example, to optimize the VBA editor for the use of source code management.

Unlikely since MS deliberately removed perfectly good, reliable integration with Source Code Management when it abandoned VSS!

 

[KitaYama]

I've not updated office for a very long time. I hate the height of the title bar. And I don't think I will update it, unless one of what I've wished for in #1 comes true. (Which I'm sure it won't)

Monaco Editor? I had a decent editor for a long time and don't need Monaco. No matter how it improves.

In the previous database I was working on, I imported Improved SQL Editor, then added a table for saving the formatted sql of the queries.
I changed the behavior of the buttons on the main form:

• Selecting a query from the combobox, pulls the sql of the selected query from the table, not from the query itself.
• Save button, not only saves the sql to the query, but also saves the sql to the table.
• Run button, executes Save button, then opens the query.
• Edit in button? oh no. I don't need it anymore. Every edit would be done throuh this form

After that, I never went back to the sql view of queries. Any time a query needed to be edited/added, everything was done through the table and the improved sql editor.
This way, all my queris had very nice and formatted sql, and I was very pleased with what I had to work with.

 

[Josef P.]

Unlikely since MS deliberately removed perfectly good, reliable integration with Source Code Management when it abandoned VSS!

They did not develop the add-in any further. The Access object options are still available. However, the locks don't fit distributed VCS, so I hope they don't reuse that with the Git integration.

 

[tomasm]

I just noticed with a recent update [Microsoft® Access® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20336) 64-bit] that I can FINALLY open a saved query in SQL view by right clicking the object in the navigation pane. The absence of this feature has puzzled and plagued me for several years.

 

[isladogs]

The feature had been requested for many years by Access MVPs. It was finally added at my request with version 2311. See my article

Open Queries Directly in SQL View

This article describes a new feature which for the first time allows you to open any query directly to SQL View from the navigation pane, VBA code or a macro. This will be a huge time saver for developers who prefer to use SQL view rather than the query build editor (QBE)

isladogs.co.uk

 

[tomasm]

I wonder if I had that feature all last year and didn't notice until yesterday. I've noticed some cosmetic changes over the last year with updates but I don't think I had the SQL view feature. Maybe I completely overlooked it for months. Either way, it's great that it's finally implemented.