VBA Access, DAO (Data Access Objects), ODBC (ODBC Direct), ADO (ActiveX Data Objects) . Access, DAO ADO VBA. . . () .. Access ; .7.13.
MS Access:
1. Application ;
2. Control ;
3. DoCmd VBA ;
4. Form ;
5. Module , ;
6. Reference , ;
7. Report ;
8. Screen ;
. (Properties), .. Access , , , , . , Appliction.Forms("").Controls(0).Properties(0). , , , . : Forms("") Forms!.
.7.12. Access
DAO Access, (.).
DAO:
Database ;
DBEngine Microsoft Jet ( );
Error ;
Field , , ..;
Index ;
Parameter ;
QueryDef ;
Recordset ;
Relation ;
TableDef ;
Workspace .
.7.13. DAO
VBA , :
Me ( );
ActiveControl ;
ActiveForm ( Screen);
ActiveReport ( Screen);
Application ;
Parent , ;
DBEngine Application.
. , - . :
|
|
Me![ ] = Me![] * Me![]
Me.Filter = "[] Like '*" & Me![] & "*'"
Me.FilterOn = True
, , Visible False. Access . . Recordset. Recordset table, dynaset, snapshot forwarrd-only:
1. Table: . , , ( Seek);
2. Dynaset - . . Dynaset ;
3. Snapshot - , . ;
4. Forward-only .
Recordset OpenRecordset:
Set variable = database.OpenRecodset (source [type, options, lockedits]),
Set variable = object.OpenRecodset ([type, options, lockedits]),
database Database; object TableDef QueryDef; source TableDef QueryDef; type ( : dbOpenTable, dbOpenDynaset, dbOpenSnspshot dbOpenForwardOnly); options : dbAppendOnly, dbReadOnly, dbForwardOnly, ; lockedits , ( : DbReadOnly, dbPessimistic, dbOptimistic). :
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("", dbOpenDynaset)
Recordset ( , ) RecordsetClone. RecordsetClone ,
Dim rstOrders As Recordset
Set rstOrders = Forms![].RecordsetClone
Me.RecordsetClone
Recordset , SQL
Set rst = db.OpenRecordset("SELECT * FROM WHERE > 1000", dbOpenDynaset, dbReadOnly)
. VBA. Close, Nothing. , rst.Close Set rst = Nothing.
Recordset current position. Bookmark ( , ). : MoveFirst, MoseLast, MoveNext, MovePrevions, Move[n]. :
|
|
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveNext
Me.Bookmark = rst.Bookmark
BOF (before of file ) EOF (end of file ). , BOF EOF True. , BOF EOF = False. , BOF = True, , EOF = True.
RecordCount. , , dbOpenTable. , . EOF. EOF True, RecordCount 0.
Public Function RecCount() As Long
Dim rstCount As Recordset
Dim dbs As Database
Set dbs = CurrentDB()
Set rstCont = dbs.OpenRecordset("")
If rstCount.Eof Then
RecCount = 0
Else
rstCount.MoveLast
RecCount = rstCount.RecordCount
End If
rstCount.Close
Set dbs = Nothing
End Function
. , , Seek, , ( ). , . Recordset NoMatch. NoMatch False, .
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("", dbOpenTable)
rst.Index = "Primaryey"
rst.Seek "=", Me![]
If Not rst.NoMatch Then
MsgBox " "
DoCmd.GoToControl ""
End If
rst.Close
Seek , . FindFirst, FindLast, FindNext FindPrevions. , :
intCount = 0
rstOrders.FindFirst "=" & Me![]
Do While rstOrders.NoMatch
rstOrders.FindNext "=" & Me![]
intCount = intCount + 1
Loop
. , SQL ORDER BY. Filter Recordset OpenRecordset. , :
Function FilterField(rstTemp As Recordset, strField As String, strFilter As String) As Recordset
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp.OpenRecordset
End Function
. dbOpenTable dbOpenDynaset. , ( Edit), Update.
rst.Edit
rst![] = "Pentium 100"
|
|
rst![] = 100
rst.Update
Cancel . Delete. - . MoveNext. AddNew. Update, , . , .
Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("", dbOpenDynaset)
rst.Filter = "[]=" & Me![ ]
Set rst = rst.OpenRecordset
If Not rst.EOF Then
With rst
Do Until.EOF
.Edit
![] = rst![] * 1.2
![] =![] * 2
.Update
.MoveNext
Loop
.Close
End With
End If
dbs.Close
. , . Fields Recordset. , : rst.Fields(Field1), rst.Fields(4) rst![Field1]. Field , :
Dim fld As Field
fld.DefaultValue = 1
fld.ValidationRule = "BETWEEN 1 AND 1000"
fld.Value = 100
7.1. Access.
7.2. Access .
7.3. , Access.
7.4. , .
7.5. .
7.6. Access.
7.7. .
7.8. Access.
7.9. Access.
7.10. Access.
7.11. .
7.12. .
7.13. Access .
7.14. Access , .
7.15. .
7.16. .
7.17. Visible .
7.18. . .
7.19. .
7.20. Access.
1. , 1, . . , 6 , 1 ∞ ( ).
1
|
|
2. Access. ( ). , . . :
- , , ( ); ( ); , (); ( , /);
- , , ( ), ( ), , , ( );
- , , , , ;
- , , , , .
3. . .
4. ( ).
5. .
6. Access (, , ).
7. , , . ( SQL):
o , ,
o c ( )
o ( ),
o ,
o ( , ),
o ,
o ,
o ,
o UNION.
8. ( ) , :
o ;
o / ;
o / , , .
: ; ; (, , , ..); , .
9. , , :
o , , ;
o ( ) ( ).
: , , , .
10. . ( ). :
- () ;
- ;
- VBA. : , , ;
- , , ( );
- (, ). / / . .
|
|
I
- .. . . .: , 1991. - 271 .
- . . .: , 1997. 704 .
- . MS Office Access 2003. . 2005. 1168 .
- - , ., . . .: . . .: , 2003. 1088 c.
- . . . Microsoft Access 2003. , , 2004. 1072 .
- . . . 8- .: . . .: , 2005. 1328 .
- . . , . . , , : , 2000. 1120 .
- . . . .: , 1991. 664 .
- . Access 2003. . : . . . 2005. 256 .
- . . . . .: , 2002. 672.
- R.Elmasri and S.B.Navathe. Fundamentals of Database systems. Adisson-Wesley, 2000. 1038 .
- L.G. Hernandez, A.I. Zmitrovich. Ed. Pucbloy Educacion. Habana, 1988. 245 p.
2.
2 .
8 , , , .
9 . , , , .
10 : , , . . .
11 , , : , , .
12 , , .
13 , .
14 Data Mining.