How do I issue SQL to a Access database from command line

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Sorry, I am used to Oracle and SQL Server where we use a command
window like SQLPLUS or ISQL/W which will enable me to run SQL commands
against the database.

The View > SQL option is very cumbersome in Access is there a simple
command line tool that I use to issue any SQL statement against a
Access database that can execute the query and return the results.

Also, is there a SQL command to find out all Table names in a Access
database and all queries(views) in a Access database.

Thanks
Karen
 
Access is pretty crippled in that regard when compared to SQL Server
(I don't know too much about Oracle). Access SQL is poorly documented
and quite feature-poor when compared with T-SQL. It does not contain
any of the extensions that T-SQL has, nor has it any of the
programming language features.The expression service allows VBA
functions to be called from queries, which is the way Access handles
this lack of programmability. Most developers end up writing a fair
amount of VBA code to compensate for these deficiencies. Command line
functionality isn't supported in the same way it is for SQLS and
Oracle.

--Mary
 
Mary's answered most of your questions, but to answer your last question,
you can query the (normally hidden) MSysObjects table.

SELECT MSysObjects.Name AS TableName
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)
AND ((MSysObjects.Flags)=0));

will give you all of the tables in the database. Type = 6 will give you all
linked tables, Type = 4 will give you all tables linked through ODBC, and
Type = 5 will give you the queries.
 
In Access go to the Query Tab.
Click New [Query].
Select Design View and click the OK button.
Add your tables [if you want] then click the Close button.
In the upper left-hand corner under File click on the drop down and select
SQL View.

This is as close to ISQL/W as you can get without buying or building your
own front end.

Just remember to end you command with a semicolon ";".

Good luck!

BillB

Sorry, I am used to Oracle and SQL Server where we use a command
window like SQLPLUS or ISQL/W which will enable me to run SQL commands
against the database.

The View > SQL option is very cumbersome in Access is there a simple
command line tool that I use to issue any SQL statement against a
Access database that can execute the query and return the results.

Also, is there a SQL command to find out all Table names in a Access
database and all queries(views) in a Access database.

Thanks
Karen
 
Back
Top