Precautions for using SQL statements in access, differences with SQL Server, and some tips)

The following SQL statement passed the test in Access XP Query
Table creation:
Create Table tab1 (
Id counter,
Name string,
Age integer,
[Date] datetime );
TIPS:
Use counter to declare the auto-increment field.
Fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names.

 

Index creation:
The following statement creates a repeatable index on the date column of tab1
Create index idate on tab1 ([date]);
After completion, the date index attribute of the access field is displayed as-Yes (repeated ).
The following statement creates a non-repeated index on the name column of tab1
Create unique index INAME on tab1 (name );
After the access is complete, the field name index attribute is displayed as-Yes (no duplicates ).
The following statement deletes the two indexes just created
Drop index idate on tab1;
Drop index INAME on tab1;

Comparison between access and SQL Server Update statements:
Update statements for updating multiple tables in sqlserver:
Update tab1
Set a. Name = B. Name
From tab1 A, tab2 B
Where a. ID = B. ID;
The SQL statement with the same function should be
Update tab1 A, tab2 B
Set a. Name = B. Name
Where a. ID = B. ID;
That is, the update statement in access does not have the from clause. All referenced tables are listed after the update keyword.
In the above example, if tab2 can be a table but a query, for example:
Update tab1 A, (select ID, name from tab2) B
Set a. Name = B. Name
Where a. ID = B. ID;

Access Multiple different access databases-use the in clause in SQL:
Select a. *, B. * From tab1 A, tab2 B in 'db2. mdb 'Where a. ID = B. ID;
The preceding SQL statement queries all the records associated with IDs in tab1 and db2.mdb (in the current folder) in the current database.
Disadvantage-the external database cannot contain a password.

Access other ODBC data sources in access
The following example shows how to query data in sqlserver in access.
Select * From tab1 in [ODBC]
[ODBC; driver = SQL Server; uid = sa; Pwd =; server = 127.0.0.1; database = demo;]
The complete parameters of the external data source connection attribute are:
[ODBC; driver = driver; server = server; database = database; uid = user; Pwd = password;]
Where driver = driver can be in the Registry
HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ odbcinst. ini \
.

Access supports subqueries

Access supports external connections, but does not include complete external connections.
Left join or right join
But not supported
Full outer join or full join

Date query in access
Note: The Date and Time delimiter in access is # Instead of quotation marks.
Select * From tab1 where [date]> #2002-1-1 #;
I used this in Delphi.
SQL. Add (format (
'Select * From tab1 where [date]> # % s #;',
[Datetostr (date)]);

The strings in access can be separated by double quotation marks, but sqlserver does not recognize them. To facilitate migration and compatibility,
We recommend that you use single quotes as the string separator.

Often on some BBs saw someone asking access to convert to ms SQL what to pay attention to, or writing stored procedures, now, I want to write some notes or common syntaxes here for your reference (some are common ASP functions)

-------------------

Access: Now ()

Ms SQL: getdate ()

-------------------

Access: Mid

SQL MS: substring (expression, start, length)
Expression-target to be searched, but cannot use aggregate Functions
Start-specify the start position of the string
Length-specify the length of the obtained string

-------------------

ASP: instr

Ms SQL: charindex (expression1, expression2)
The parameter is in the opposite position as the ASP instr.

-------------------

ASP: dim

Ms SQL: declare @ variable datatype
For example, declare @ varname varchar (50) declares that the variable @ varname is of the varchar type and the length is 50.

-------------------

Assignment:
Declare @ varname varchar (10)
Set @ varname = 'this is content'

-------------------

Data type conversion functions, such as asp cstr and clng

Ms SQL: Cast (expression as datatype)
Expression is the target object.
Datatype is the data type to be converted
Example:
Declare @ varname varchar (20) -- declare a variable named @ varname in the varchar type with a length of 20
Set @ varname = '2' -- assign a value to @ varname
Cast (@ varname as INT) -- convert to int type data

-------------------

String Connection Symbol: +

-------------------

Loop:
While boolean_expression
Begin
-- Statement to be executed
End

Boolean_expression is a Boolean expression. If the specified condition is true, statements are executed cyclically.

-------------------

If usage
If (boolean_expression)
Begin
-- Execute true branch ....
End

Usage of IF... else...
If (boolean_expression)
Begin
-- Execute true branch ....
End
Else
Begin
-- Execute the false Branch
End

-------------------