Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)
)
share|improve this question

4 Answers 4

up vote 6 down vote accepted

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

share|improve this answer
    
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 '09 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 '09 at 23:56
    
Thanks. This is what I needed to know. –  DaveParillo Nov 17 '09 at 1:37
    
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 '09 at 2:52
    
what's the overhead when currentDb returns the database object? –  Nick Dec 2 '09 at 10:30

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!

share|improve this answer
    
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 '09 at 23:42

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.

share|improve this answer
    
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 '09 at 1:09

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.