Access SQL Server , . .
1. Office → New (Office → ) ( Access, , Blank Database ( )).
Getting Started ( ).
2. , .
New Database ( ).
3. Save as type ( ) Microsoft Office Access Projects
(*.adp) ( Microsoft Office Access (*.adp)).
4. , (adp-), File name ( ) (, ZooAnimals.adp) .
Access Getting Started ( ) New Project ( ) .
5. Create (), .
Access SQL Server.
6. No () .
. (. . 20.6).
7. , , Finish ().
Access SQL Server ( adp- ). Access .
8 , .
SQL Server
SQL Server Access Access, . . , SQL Server , Access.
Access ( adp), , , , , .
Create → Tables → Table Design ( → → ). . Design View (). . Design View ().
, , , , Access. , . ,
|
|
. ( SQL Server , , .)
(. 20.10), .
■ Column Name ( ). ( Access). .
■
Data Type ( ). , ( Access). , SQL Server, , Access.
. 20.10. Design View SQL Server Customers BoutiqueFudge
■ Length (). Field Size
( ) Access. . ; , , .
■ Allow Nulls ( ). Required ( ) Access. , SQL Server , , . . .
■ Description (). . .
. , . Table Tools | Design → Tools → Primary Key ( | → → ). , .
, . Access . Access, .
, , Access , . , Access SQL Server, , . .
SQL Server
, SQL Server Access . .
, . , Access SQL Server, Access. ( , Access, - , .) . 20.1 - SQL Server, Access.
|
|
SQL Server , Access. , , , .
20.1. SQL Server Access
Access | SQL Server |
(Text) | nvarchar ( 4000 , Access, 255 ) |
Access | SQL Server |
Memo (Memo) | ntext |
(Number) ( (Field Size) (Integer)) | smallint |
(Number) ( (Field Size) (Long Integer)) | int |
(Number) ( (Field Size) (Single)) | real |
(Number) ( (Field Size) (Double)) | float |
(Number) ( (Field Size) (Decimal)) | decimal |
(Number) ( (Field Size) (Byte)) | tinyint |
(Number) ( (Field Size) (ReplicationID)) | uniqueidentifier |
/ (Date/Time) | datetime |
(Currency) | money |
(AutoNumber) | int ( Identity Yes ()) |
(Yes/No) | bit |
(Hyperlink) | nvarchar |
(Attachment) | nvarchar ( ) |
20.1 ()
, , SQL Server . , , SQL Server. .
1. ID (), int.
2. Columns (), , Identity Yes ().
Identity SQL Server. .
3. Identity Seed ( IDENTITY) Identity Increment ( IDENTITY).
□ Identity Seed ( IDENTITY) . Access, 1.
o Identity Increment ( IDENTITY) , SQL Server . , Identity Increment 5, 1, 6, 11, 16 . . , SQL Server , Access, .
SQL Server , Access. , , Lookup () (. 20.11) . , .
■ Display Control ( ) Combo Box ( ), , .
■ Row Source Type ( ) Tables/Views/Functions (//), , . ( , Value List ( ).)
. 20.11. CreditCardID Orders
■ Row Source ( ) . , SQL- SELECT, ID (). , SELECT ID, ProductName FROM Products ORDER BY ProductName , ID Products.
|
|
SELECT , , , . Access, .
■ Bound Column ( ) , ( Row Source ( )) , . , SELECT ID ( ), 1.
■ Column Count ( ) , . 2 ( ), ID , .
■ Column Heads ( ) , . No (). , , , , , .
■ Column Width ( ) . . , 0 ";1" 1 .
, . , , .
, Access , .
5, , Access : . Access . .
.
1. .
, . (, Products ProductCategories. ProductCategorylD , Products.)
2. Table Tools | Design → Show/Hide → Property Sheet ( | → → ).
Properties () (. 20.12), , Property Sheet ( ), Access.
. 20.12. , OrderDetails ( ) Products ( ). OrderDetails ProductID
|
|
3. Relationships ( ).
4. New () .
5. , Primary key table ( ), .
6. , , .
( , , , .)
7. , Foreign key table ( ), .
, , , .
8. , , Check existing data on creation ( ).
, . , .
9. Enforce relationships for INSERTS and UPDATES ( INSERT UPDATE), .
. , , . , , , .
10. , .
, SQL Server. SQL Server , ( ) .
, Access , SQL Server. , , SQL Server .
■ View (). (select query) (, ) .
■ User-defined function ( ). . ( , . .)
■ Stored procedure ( ). SQL Server. , , , . , Access. SQL Server, , .
.
Access, . , , , .
1. Create → Other → Query Wizard ( → → ).
New Query ( ), SQL Server . , .
2. Design View () .
Add Table ( ).
3. ( , ) Add (), . , Close ().
(query designer). , Access . (. 20.13).
. 20.13. , , . Products
|
|
4. , .
, , . , (. 20.14). Access, . ( Access .)
. 20.14. , Output,
. , , , , .
5. , Sort Type ( ) Ascending ( ) Descending ( ).
, Sort Type ( ) , . , Sort Order ( ), , . , , , Sort Order ( ) 1 LastName () 2 FirstName ().
, , Output ().
6. , Criteria () .
, , Access. (, +, -, /, * =, < > ). , SQL Server Access " , . " .
. , , Or (), . 20.15.
. 20.1 5. 10 50
7. , , Column () Alias () (. 20.16).
. 20.16. , , ( Price dbo.OrderDetails.Price). , Price , OrderDetails, (dbo) ( SQL Server , . .)
SQL Server . ColumnName: Expression (: )
Alias (), Column ().
, , , . . 20.16 , , Price*Quantity, .
, , SQL Server Access.
, .
Access, SQL Server. SQL Server . , SQL ( ), Access, , SQL Server. .
, , .
(), . , -
'Maple Magic', "Maple Magic".
, . , '1/30/2008', #1/30/2008#.
+, &. , FirstName + ' ' + LastName, FirstName &+ " " & LastName.
Access. SQL Server , , Access, . , , SQL Server Books Online (. . 20.2, ). , , Access.
8. , Datasheet View ( ) ( Function & View Tools | Design → Tools → Run ( → → )).
Access . , , Access. .
21