Where can I find a ODBC Access command line interpreter?

[Guest]

I have been working with databases for many years. I have recently used
Access to manage my current research data. Now I need to "play with the
data," meaning try out different queries and filters. I find using GUI query
builders extremely inefficient and really frustrating. I very much want a
traditional Command Line Interpreter (CLI) for the ODBC or direct Access
(2003) connection. What are my middleware options?

Regards,
Rob vH

 

[Sylvain Lafontaine]

Right click of the mouse and then choose SQL View from the contextual menu
(this option is also available on the Query Design toolbar; you will have
what you want. Choose RUN from the menu to execute your query and see the
result.

Another possibility would be to use Excel to link to your table and then
analyse the data.

 

[TC]

Similar to what Sylvain said, if you're happy entering the SQL
directly, just write a form with a textbox in which you can enter it.
Then execute that SQL through code, and show the result as a datasheet.
I'd create a single querydef for this, and alter its SQL attribute as
required. Use CreateQueryDef to create the querydef, and OpenQuery to
run it. This approach might cause some database bloat, depending on how
often you ran it. You might just need to compact the database
occasionally, to take care of that.

If you were happy to do a bit more coding, you could even parse the
usr's SQL and do clever things ih it. For example, you might have a
predfined alias "cust" fo "tblCustoimerDetails", so your form expande
the former to the latter when appropriate.

I have a similar form which lets me retain the original layout &
spacing of every query that I write. So I never have to work in the
abortionate compressed form that Access changes it to. I can not see
the purpose oif it makiung that change, because the SQL of a stored
query is never executed directly from source - it is always parsed into
a (hidden) tokenized form, first. So there is absolutely zero benefit
to munging the layout, afaics.

HTH,
TC

 

[Albert D.Kallal]

You can actually use the "debug" window.

Myself having come from FoxPro, and even using a lot of mini/mainframe
systems like pick/prime and IBM's U2 systems, then I often do use a command
line systems.

In ms-access, that is simply ctrl-g..and you are in the code debug window.
At that point, you can execute sql, such as:

docmd.RunSql "update tblCustomers set City = 'New York' where city = 'N.Y'"

However, why not use the query builder (as others suggested here), and
simply switch it into sql view mode. That way you can go back to the old
(long) way of typing in sql.

Note that using DDL commands, you either have to turn on ANSI compatibility,
or use current project (ado). such as:

currentproject.Connection.Execute "create table ......"

If you turn on ANSI compatibility, then you can execute data definitions
commands (ddl) in the query builder also. However, I would NOT turn on ANSI
compatibility for a existing application, as you likely would wind up
breaking existing code....