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 '