SQL statement execution in ACCESS

SQL statement execution in ACCESS

 

Simply put: Query-create-design view-select a table or not-right-click to create an SQL View

No.

 

 

 

 

 

 

 

 

 

What is the difference between access using SQL statements and SQL statements?

Access provides query objects. The design view and SQL view can be used during design, which is very convenient. SQL statements in the SQL view can be used in SQL SERVER, but is it completely available? The answer is no. The table summarizes the important differences between Microsoft Access and Microsoft SQL Servers SQL syntax. Table 1

SQLSyntax Element	Microsoft Access	Microsoft SQL Server
Identifier	It cannot exceed 64 characters. Keywords and special characters are allowed. It can start with any character.	For SQL Server 6.5: It cannot exceed 30 characters. Keywords and special characters are not allowed. It must start with a letter. The SQL Server 7.0 identifier is fully compatible with Access.
Output Field	Multiple Output fields can have the same name.	Multiple output field names are not supported in the view.
Date separator number	Pound sign (#)	Marker (')
Boolean constant	True, False; On, Off; Yes, No.	Integer: 1 (true), 0 (false)
String connection	And number (&)	Plus sign (+)
Wildcard	The asterisk (*) matches zero or more characters. Question mark (?) Matches a single character. Exclamation point (!) This means that the instance is not in the list. The pound sign (#) indicates a single number.	Percent (%) matches zero or more characters. The underscore (_) matches a single character. The prefix (^) indicates that it is not in the list. No character corresponding to the pound character.
TOP	If there is an order by clause, the hierarchy is automatically included.	SQL Server 6.5 is not supported. SQL Server 7.0 requires a clear WITH TIES clause.
CREATE INDEX	Allows you to create ascending and descending indexes. The primary key can be declared. There is no Null value and the Null value is ignored.
DROP INDEX	Syntax: Drop Index <index name> ON <table name>	Syntax: Drop Index <table name>, <index name>
DISTINCTROW	Supported (a single record can be selected ).	Not supported.
OWNERACCESS	Supported (permission control during execution ).	Not supported.
Table in UNION	Supported (the following syntax can be used to specify a TABLE: TABLE <tablename>	Not supported.
Order by in Unions	Yes. Multiple sorts can be achieved by clauses in the Union query.	Yes. A Sort can be performed by clauses at the end of a statement.
TRANSFORM	Yes. Used for cross tabulation query.	Not supported.
PARAMETERS	Supported (recorded in SQL ).	Not supported.

Take the northwind database as an example. To obtain the same query result, the SQL statement changes accordingly. Access: SELECT * FROM Products where productname like 'C * '; SELECT * FROM Products where productname like 'C? Ang '; SQL Server:SELECT * FROM Products WHERE productname LIKE 'C % 'SELECT * FROM Products WHERE productname LIKE 'C _ ang'

From: http://hi.baidu.com/enderby/blog/item/69eea92d860234321f3089cc.html