Tips for using Access

1. problems with using selecttop in ACCESS if Orderby is used after the TOP query statement and there are duplicate values in the Orderby field, the TOP may be invalid and all records will be returned, for example: selecttop5fromNewsorderbyCreateDate

1. if 'ORDER BY' clause is followed by 'ORDER BY' and the 'ORDER BY' field contains duplicate values, this top may be invalid, all records will be returned, for example, Select top 5 from News order by CreateDate. If there is a duplicate value in CreateDate, it is very likely that

1. Problems with using select top in ACCESS

If Order by is used after the TOP query statement, and the Order by field contains duplicate values, the TOP may be invalid,
All records are returned.
For example:


Select top 5 from News order by CreateDate
If there are duplicate values in CreateDate, it is very likely that all records will be displayed, and this TOP function will be invalid.
However, if no duplicate value exists in CreateDate, the TOP function is still valid.

Therefore, when using the TOP function in ACCESS, pay attention to whether the order by field has duplicate values. For example, if CreateDate is "year-month-day hour: minute: Second, that's basically not a problem.
For the sake of insurance, we can also use the "add primary key" method. Otherwise, we must ensure that the TOP and order by statements do not appear in the same layer of queries at the same time.

2. The number of file share locks overflows.

An error is returned when you Access the database and perform a large number of operations (more than 9500 records.
Error message:
Microsoft JET Database Engine error '80040e21'
The number of file share locks overflows. (Error 3052)
Solution:
Modify the registry.
[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Jet 4.0]
"MaxLocksPerFile" = dword: 05f5e0ff
Change the default value of MaxLocksPerFile to 9500 in decimal format. You can change the value to 99999999 in decimal format.

3. Differences between access 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 ON Syntax: Drop Index , 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 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. The main difference is the change of wildcards. 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'