4

I know you can open an SQL console in Access when creating a query or a view, but is there an 'all purpose' console that would allow me to execute statements like:

CREATE TABLE Foo
(
Foo_Id int NOT NULL,
Name varchar(255) NOT NULL,
CONSTRAINT pk_FooID PRIMARY KEY (Foo_Id,Name)
)
Improve this question

4 Answers 4

Reset to default
7

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).

Improve this answer
6
  • 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
    Nov 13, 2009 at 23:40
  • 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
    Nov 14, 2009 at 23:56
  • 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
    Nov 17, 2009 at 2:52
  • what's the overhead when currentDb returns the database object?
    – Nick
    Dec 2, 2009 at 10:30
  • 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
    Dec 3, 2009 at 4:29
3

Open up the Design View in the Queries Database Object. Close the Show Table forum, then Right click in the empty area at the top of the window, below the toolbars. Select SQL View. That will give you a spot where you can execute sql queries. Use the "Run !" from the toolbar.

That view is designed for you to create and save queries, but you don't have to.

Improve this answer
1

I don't believe that Access was ever really thought of as being used for general purpose SQL use like creating tables and what not. You can not even use 'stored procedures' like you would on a regular SQL server. I think you will find yourself getting frustrated if you start to use Access without using the Visual Designer.

Good luck!

Improve this answer
1
  • I think that you are probably right! I would really like to have a simple 'scratch pad' I can use to play around with ideas. I guess I'm getting old, but the amount of clicking and mouse BS you have to endure to accomplish the most basic tasks in Access is mind boggling.
    – DaveParillo
    Nov 13, 2009 at 23:42
0

Using an ADP to connect to SQL Server may give you what you want. However I don't know to what extent Access 2003 allows you to create views and stored procedures in SQL Server 2005 or 2008 as they were released after A2003.

Improve this answer
1
  • Thanks. Do you have any documentation / links? I know Access 2003 doesn't support stored procedures. I think the assumption is that 'stored procedures' would be implemented in the VBA IDE.
    – DaveParillo
    Nov 28, 2009 at 1:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .