Some tips for using access

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 '