Home » Questions » Computers [ Ask a new question ]

Does MS Access 2003 contain a general purpose SQL console

Does MS Access 2003 contain a general purpose SQL console

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:

Asked by: Guest | Views: 35
Total answers/comments: 2
Guest [Entry]

"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)."
Guest [Entry]

"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!"