SQL Editing / Access VBA WISH

[mburns_08109@comcast.net]

WISH:
I'd like to have full intellisense for editing SQL queries and/or significant improvements in the Visual Query Deisgner.

Specifically, I' d love to much more easily use PrimalScript to do things like
Access VBA Development, but we'd need better support for "classic" VB and VBA syntaxes as well as Jet SQL.

Bring an existing Access/JET Query SQL into Primalscript to facilitate editing (especially to do search & replace of table/field/expression syntax)
Make Primalscript a little smarter about using Access database objects (like forms, reports, and queries also defined within the .mdb and the newer .accdb/.accdt files) such that primalsense can comprehend object/property references commonly found within Jet SQL queries, like [Forms]![myFormname]![myControl] (or forms!myFormName!myControl) and even to be able to read the forms and controls (and recurse through defined subforms and controls) to validate/invalidate the object references in the SQL Syntax.

Added bonus: Make the SQL evaluator learn to use the Jet Expression service to validate/evaluate the VBA-like Jet expressions like:
NZ(<expression>, <valueexpressionifexpressionevaluatestonullorzero>)
iif(<conditionexpression>, <valueiftrueexpression>, <elseexpression>).

BIG Request - allow pasting of SQL Query text into the SQL window and bacwards-infer the fields list and the Table/Relationships (to facilitate editing existing Access/JET query SQL definitions).

BIGGER Request: add a tree-view-like hierarchial view to show the nesting of queries - perhaps add a special node to the treeview for fields/expressions with (WHERE/HAVING) sub-query expressions as well
(i.e. 'SELECT A, B, C from myTable WHERE (A is not null) and (B in (SELECT Blah from myTableB Where myTableB Like "*23*")) and (C = "AB") ORDER BY C ASC, B DESC;').
This capability would make PrimalScript an even BETTER tool for query editing than the built-in Access query editor!

EVEN BIGGER: Make Primalscript able to interact with the VBA/VBE environment so that Primalscript could be used as a complete substitute for the VBA IDE.

Combine these things, and thousands of Microsft Office VBA developers out there will BUST YOUR DOORS DOWN to get a product that can actually improve their productivity for the first time in many years!

...You know what GREAT new VBA IDE editor features Microsoft gave Access developers in the new Access 2007 version? ...precisely ONE thing. We (finally?) get to use the mousewheel in the VBA IDE (insert imagry of my multiple backflips over that revolutionary new feature innovation for better & faster software development here). <-- (read as dripping with sarcasm)

[Alexander Riedel]

PrimalScript 2007 will have a new SQL QUery Builder control.
I am by no means a database person so I cannot really tell you that this is all you ask for. But I can tell you it is much better than what is in 4.1.
You *should* be able to paste queries into the query builder and have it reverse parse the visual representation.
You can also run your queries and live see and edit your data.
You can save the metadata and work offline on queries as well.

I would suggest that you check out PS2007 (release date is set for August 15th) and provide us with your feedback on that.
We are most certainly listening and will do what we can to make you "bust down our doors"

Thanks for the input!

[donj]

I'd like to add that, as an Office Developer, we really do value input like this. My impression is that a lot of our existing feedback comes from other audiences, and we don't necessarily hear about a lot of the more Office-specific things that you'd find helpful. So keep it up.Check out the trial of PrimalScript 2007 Enterprise later this week and let us know what you think of the Visual Query Builder. Although I realize your Access VBS code incorporates queries as string literals, you may find it easier to develop complex queries in a specific environment, and then paste them back into the VBA - provided, as Alex suggests, you're able to reverse the process when you need to edit the query. See what you think.