I don't know what you mean by "all-purpose console," but perhaps inside Access it's the Immediate Window that will serve your needs. If you hit Ctrl-G on the keyboard from the main Access window, that will take you to the Immediate Window in the VBE and there you could type:
CurrentDB.Execute "CREATE TABLE Foo (Foo_Id int NOT NULL, Name varchar(255) NOT NULL, CONSTRAINT pk_FooID PRIMARY KEY (Foo_Id,Name))"
It's a single line interface, so no SQL formatting, but it will execute the SQL.
The problem with using CurrentDB as your execution object is that it returns a new database object each time you call it (to insure that all its collections are up-to-date). An alternative would be:
DBEngine(0)(0).Execute "CREATE TABLE Foo (Foo_Id int NOT NULL, Name varchar(255) NOT NULL, CONSTRAINT pk_FooID PRIMARY KEY (Foo_Id,Name))"
Since DBEngine(0)(0) always returns the same object, you can then check things like:
DBEngine(0)(0).RecordsAffected
The "problem" with that is that if you're changing the database's collections, you have to remember to refresh them before you use them. For example, if you add or delete a table, you'd have to refresh the TableDefs collection to the collection be up-to-date:
DBEngine(0)(0).TableDefs.Refresh
Another alternative would be to use a cached database object, as I do in all my apps, but it doesn't avoid the collections refresh problem, but just makes it easier to type (I find typing DBEngine(0)(0) to be quite error-prone, and find my dbLocal function much easier to use).
Old conversation, but I have just released something very like this. Check out www.arrow-of-time.com . David F and Dave P, if you email me through the arrow of time site I'm happy to give you a free copy of the full version. – Ben McIntyre – 2015-09-08T15:10:49.217
I'm familiar with vba & the immediate window, but I was looking for a simple console that was part of the client. Something like the SQL editor for a Query, but not tied to SQL statements define queries. I'm contemplating writing a simple form that will allow me to do that, but didn't want to do that if it already existed. – DaveParillo – 2009-11-13T23:40:57.590
Other people have already done it (I've seen announcements of it), though it's something I've never tracked as I feel no need for it. Not sure how you'd Google it, unfortunately. – David W. Fenton – 2009-11-14T23:56:17.437
If you find utilities that you like, why don't you post back to the thread and let us know what you liked. – David W. Fenton – 2009-11-17T02:52:37.213
what's the overhead when currentDb returns the database object? – Nick – 2009-12-02T10:30:53.823
Overhead in what regard? CurrentDB refreshes the collections and returns a copy of the database object. DBEngine(0)(0) does not refresh the collections and returns Access's base pointer to the db currently open in the UI, rather than a copy of it. This latter can in very rare circumstances (such as after running a wizard) return a pointer to something other than what the user sees as the db currently open in the UI. CurrentDB never has that problem. CurrentDB has been the recommended method since A97 forward. – David W. Fenton – 2009-12-03T04:29:31.953