Some tips for using access

Source: Internet
Author: User
Tags alphabetic character create index

problems with select top in 1.ACCESS

This top is likely to be invalidated if you use ORDER by after the top statement of the query and if there are duplicate values in the order by field.
will return all records
Like what:


Select Top 5 from News order by CreateDate
If there are duplicate values in the CreateDate, it is likely that all the records will be displayed, and this top feature will fail
But if there is no duplicate value in the CreateDate, the top function is still valid

Therefore, use the top feature in access to see if the field of the order by has duplicate values, and if it's like CreateDate is "year-month-day: minutes: Seconds", that's basically not a big problem.
However, for the sake of insurance, we can also use the "Join the primary key" approach, otherwise we must ensure that the top and order by can not be in the same level of query.

2. File share lock number overflow

Access database, simultaneous operation of a large number of records (more than 9,500) The times are wrong.
Error Tip:
Microsoft JET Database Engine error ' 80040e21 '
The number of file share locks overflowed. (Error 3052)
Solution:
Modify your registry.
[Hkey_local_machine/software/microsoft/jet/4.0/engines/jet 4.0]
"MaxLocksPerFile" =dword:05f5e0ff
MaxLocksPerFile the default decimal value of 9500, you can change to the decimal 99999999

3.access The difference between SQL statement and SQL Access provides query objects that can be designed with Design view and SQL view, which makes it easy for SQL statements in SQL view to be used in SQL Server, but are fully available. The answer is no, the table summarizes the important differences between Microsoft Access and Microsoft SQL Servers SQL syntax. Table 1

SQL syntax Elements Microsoft
Access
Microsoft SQL
Server
Identifier The limit is no more than 64 characters. Allows you to use keywords and special characters. You can start with any character. SQL Server 6.5: Limit no more than 30 characters. Use of keywords and special characters is not allowed. Must begin with an alphabetic character. The identifier for SQL Server 7.0 is fully compatible with Access.
Output fields Allows multiple output fields to have the same name. Multiple identical output field names are not supported in the view.
Date separator Symbol Sterling character (#) Apostrophe (')
Boolean Constants True, False;on, Off;yes, No. Integer: 1 (True), 0 (false)
string concatenation and number (&) Plus (+)
Wildcard characters The asterisk (*) matches 0 or more characters. The question mark (?) matches a single character. The exclamation mark (!) means not in the list. The pound sign (#) means a single number. A percent semicolon (%) matches 0 or more characters. The underscore (_) matches a single character. The upper caret (^) means that it is not in the list. There are no characters corresponding to the Sterling (#) 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 an explicit with TIES clause.
CREATE INDEX Allows you to create ascending and descending indexes. Allows the declaration of a primary key, no null value, and ignores null values.
DROP INDEX The syntax is: Drop Index <index name> on <table name> The syntax is: Drop Index <table name> <index name>
Distinctrow Support (allows you to select a single record). Not supported.
Owneraccess Support (Control of permissions at execution time). Not supported.
Table in UNION Support (allows you to specify tables using the following syntax: Table <tablename> Not supported.
Order BY in unions Support. Allows multiple sorts to be implemented through clauses in a federated query. Support. Allows a sort to be implemented through a clause at the end of a statement.
TRANSFORM Support. For crosstab queries. Not supported.
PARAMETERS Support (recorded in SQL). Not supported.
Take the Northwind database as an example, to get the same query result, the SQL statement changes accordingly. Mainly is the change of wildcard character Access:select * from the products where the ProductName like ' c* '; SELECT * from the products where ProductName like ' C?ang '; SQL Server:SELECT * from the products where ProductName like ' c% ' select * where ProductName like ' C_ang '

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.