5-1 DAO ACCESS Office Visual Basic Visual C++ Excel Word PowerPoint Access DAO ADO 5-1-1 DAO DAO Data Access Object Access DAO Access DAO Access Access DAO Access DAO DAO Recordset << >> ADO 5-1-2 DAO 3.6 DAO 3.6 Access 2000 DAO Access 97 2000 DAO 3.6 Access 2000 Visual Basic DAO ADO
5-1 5-1 DAO ADO 5-1 ADO DAO DAO 3.6 CurrentDB 5-1-3 DAO DAO 24-1
5-2 DAO 5-20 Access 97 TableDef TableDef TableDefs 5-2 Database Database TableDefs QueryDefs 5-1 DBEngine Workspace User Group Microsoft Jet Database Engine Access Workspace PID Workspace Workpace
Database TabDef QueryDef Recordset Relation Container Document Field Index Parameter Error Property Workspace Database Database Database Database Database Container TabDef QueryDef Recordset Relation Index TabDef QueryDef DAO DBEngine 5-1 DAO ACCESS Object DAO DAO ( ADO) ACCESS Object DAO Def TableDef Recordset << >>
DAO Access 2000 DAO 3.6 DAO
5-2 TableDefs DAO TablDef TableDefs TableDef s 5-2-1 DAO 5-2 s DAO (DBEngine) Workspace Database TableDef 0 0 A 1 B 5-3 5-3 5-3 Ch5\5-2\Collection.mdb Module1 a DBEngine.Workspaces(0).Databases(0) Workspaces Databases Workspace
Database ( 0) Workspaces Databases Access a 5-3 a( ) b TableDefs(" ") TableDefs (" ") b a << >> CurrentDB Set db=currentdb CurrentDB 5-3 CurrentDB 5-2-2 Database TableDefs QueryDefs... DAO 5-3 5-4 5-4 5-4 5-3 a Workspace (0) Workspaces Workspace
(0) Databases Database Databases Workspace 5-2 Workspaces DBEngine Set a=dbengine(0)(0) 5-2 DBEngine Workspace Database TableDef QueryDef Workspaces Databases TableDefs Fields Parameters 5-2: DAO << >> DAO Recordsets Workspaces (Open) (Close) Access Containers Documents Parameters Container Document Append Delete For For Each Count ( )
5-2-3 5-3 Append Delete Refresh Count Workspaces Databases Users TableDefs Groups QueryDefs Relations Parameters Recordsets Containers Documents Fields Indexes Errors Properites 1:DAO Append Delete Refresh Count Append Append Append..Append.
DB Database RE1 Relation RE1 DB.Relations.Append RE1 Delete..Delete Append Refresh..Refresh Count TableDefs.Count Ch5\5-2\Collection.mdb 5-5 5-5 Count 5-5 Load TableDefs
RowSource 1002 1006 Count I 1003 1004 (Attributes) (Name) I TableDefs 0 1 0 1 2 1 3 1003 Attributes Access Msys 1003 Attributes dbsystemobject (And False) 1004 Click MsgBox vbyesno vbyes 1010 1011 Load << >> 5-5 1001 1010 db CurrentDB << >>
5-3 DBEngine Workspace Database Relation DAO DAO ODBCDirect ODBCDirect 5-3-1 DBEngine DAO DAO D ODBCDirect J DAO CompactDatabase(J) DBEngine.CompactDatabase " "," [" ",][" ",][;pwd= ] 21 dblangchinesetraditional dblanggeneral db_encrypt( ) db_decrypt( ) db_ Version10 db_version11 db_version20 db_version30 Access DAO Access + << >> RepairDatabase(J) CompactDatabase CompactDatabase
RegisterDatabase(D,J) ODBC ODBC 32 ODBC DBEngine.RegisterDatabase " ODBC "," Driver", ODBC," " ODBC ODBC True False True( ) ID 5-6 5-6 ODBC 5-6 Ch5\5-3\db.mdb Module1 5-6 RegisterDatabase ODBC InputBox ODBC new_test 32 ODBC 5-7
5-7 RegisterDatabase ODBC 5-7 ODBC ODBC False 5-8 5-8 5-8 5-6 ODBC 5-8 CreateWorkspace(D,J) Workspace Access Workspace Set =DBEngine.CreateWo rkspace(" ","
",", ") Workspace Access (Admin) dbusejet dbuseodbc WorkSpace Jet ODBCDirect 5-9 5-9 ODBCDirect 5-9 1002 CreateWorkspace Workspace 1003 Workspaces 1004 OpenConnection 5-9 DBEngine DAO DBEngine Idle(J) DBEngine.Idle [dbrefreshcache] dbrefreshcache SetOption(J) Jet Windows (Registry) DBEngine.SetOption, Windows Regedit.exe 5-10 Jet 4.0
5-10 Jet SetOption 5-10 DBEngine.SetOption dbsharedasyncdelay, 32 SharedAsyncDelay 32 MaxBufferSize Jet (KB) 0 ( - 12) / 4 + 512 32 MB 5632 KB 16 MB Access Jet SharedAsyncDelay Jet LockRetry Jet ( ) 20 Jet Jet
LockDelay LockRetry LockRetry 100 MaxLocksPerFile Jet 9500 Jet Jet Netware 10000 PageTimeout Jet 5000 ( 5 ) RecycleLVs LV Long Value Jet OLE 0( ) 1( ) Long Value Long Value Long Value Long Value Jet FlushTransactionTimeout 50 0 ExclusiveAsyncDelay SharedAsyncDelay
ExclusiveAsyncDelay SharedAsyncDelay Jet 2000 50 Threads Threads Windows 95 Windows NT Multi-Threads Threads Jet 3 Jet Jet << >> BeginTrans CommitTrans Rollback OpenDatabase CreateDatabase Workspace Workspace LoginTimeout(,D,J) ODBC 20 0 DBEngine. LoginTimeout=100 100 Version(,D,J) DBEngine Database DAO Access 2000 DBEngine.Version 3.6 ACCESS DefaultUser DefaultPassword(,D,J) DAO Wordkspace DBEngine.DefaultUser = johnson 20 ( ) 14 DefaultType (,D,J) DBEngine
dbusejet dbuseodbc Workspace ODBC ODBCDirect inipath(,j) Jet Registry DBEngine.iniPath SOFTWARE\Microsoft\Office\9.0\Access\Jet\4.0 SystemDB(,J) DBEngine.SystemDB="C:\ACCESS \System. Mdw" DAO CreateWorkspace Session SystemDB Workspace 5-3-2 Workspace DBEngine Workspace Workspace ACCESS Workspace Workspace Workspaces Workspaces Delete Close BenginTrans(D,J) Transactions Transactions BeginTrans ( ) Recordset Workspace.BeginTrans << >> BeginTrans Database Recordset
Transactions True False Access CommitTrans(D,J) Transaction BeginTrans Workspace.CommitTrans RollBack(D,J) Transaction Workspace.RollBack (BeginTrans) (RollBack) (CommitTrans) 5-11 5-11 DAO 5-11 Ch5\5-3\Trans.mdb 1.2 5-11 1009 1012 1016 1017 CommitTrans Rollback
Close(D,J) Database QueryDef Recordset.Close Workspaces(0) Access CreateWorkspace Workspace << >> TableDef Database Access CreateDatabase(J) Set Database =Workspace.CreateDatabase(" ",,[" "]) Access 21 dblangchinesetraditional dblanggeneral db_encrypt( ) db_version10 db_version11 db_version20 db_version30 db_version35 5-12 5-12 5-12 InputBox 1002 Dir 1004
1009 1004 1005 n 1006 1006 OLE Access 1009 CreateGroup(J) Workspace User Group ( ) Set Group =Workspace.Create Group([" ",][PID]) CreateUser(J) Workspace Group Set User =Workspace.CreateUser([" ",][PID,][ ]) Group User Users Groups CreateGroup CreateUser User Group OpenDatabase(J) Databases Set Database =Workspace.OpenDatabase(" ",[,][,][ ] ) True Fales True ( ) True False Access FoxPro 2.5 FoxPro 2.5 5-13
5-13 Excel 5-13 Ch5\5-3\OpenExcel.mdb Excel 5-13 XLS sheet 5-13 Sub fname XLS 1001 OpenDatabse XLS XLS 1003 1006 sheet 5-13 XLS MDB DBF Name (,D,J) Data Access Object IsolateODBCTrans(,J) True False ODBC ODBC Transaction Transaction ODBC ODBC Transaction Workspace True BeginTrans Transaction UserName (D,J) Workspace Container Document Workspace
Workspace Append Workspaces Container Document Permissions Type (D,J) DAO Workspace 1(dbUseODBC ) 2(dbUseJet) Workspace 5-3-3 Database Close VBA ODBCDirect D ODBCDirect J DAO Close(D,J) 0 VBA DB.CLOSE CreateProperty(J) Database QueryDef TableDef Field Index Set Property =Database.CreateProperty([" ",][,][,][DDL]) Database Database CreateProperty Append Property Name Property ( DDL) True False( ) DDL(Data Definition Language) 5-14
5-14 AllowByPasskey Access Shift CreateProperty ( 1004) False Shift << >> Access 2000 AllowByPasskey Access AllowByPasskey Access Document Document CreateQueryDef(D,J) QueryDef Set QueryDef =Database.CreateQueryDef([" ",]["SQL "]) QueryDef Name Append QueryDefs SQL 5-15
5-15 5-15 CreateQueryDef Set a = CurrentDb.CreateQueryDef() a.name = "test" a.sql = "select * from " CurrentDb.QueryDefs.Append a 5-15 CurrentDb.QueryDefs.Append a CreateTableDef(J) Set TableDef =Database.CreateTableDef([" ",][,][" ",][" "]) CreateQueryDef Append 5-4 dbattachexclusive 65536 dbattachsavepwd 131072 Connect dbsystemobject -2147483646 dbhiddenobject 1 dbattachedtable 1073741824 ODBC
dbattachedodbc 536870912 ODBC 5-4:TableDef + CreateTableDef ( ) 5-16 5-16 5-16 Ch5\5-3\Link.mdb LinkOK DataPath DataName 5-16 1044 CreateTableDef 1045 1046 1047 1045 1046 << >> 5-16 5-4 1045 Connect CreateRelation(J)
Set Relation =Database.CreateRelation([" ",][" ",][" ",][ ]) 5-5 dbrelationunique 1 dbrelationdontenforce 2 dbrelationinherited 4 dbrelationupdatecascade 256 dbrelationdeletecascade 4096 dbrelationleft dbrelationright 16777216 (LEFT JOIN) 33554432 (RIGHT JOIN ) 5-5 Relation Access 2000 Execute(D,J) Database QueryDef Database.Execute [ ][, ] SQL SQL dbdenywrite 1 dbinconsistent 16 dbconsistent 32 dbsqlpassthrough 64 ODBC
dbfailonerror 128 dbseechanges 512 5-6 A B C B C B C dbconsistent C 4-20 OpenRecordset(D,J) Database TableDef QueryDef Recordset Set Recordset =Database.OpenRecordset(" ",[ ],[ ]) dbopentable dbopendynaset dbopensnapshot dbopenforwardonly Recordset 5-6 dbsqlpassthrough dbseechanges dbdenywrite dbinconsistent dbconsistent dbappendonly( ) dbforwardonly( Recordset MoveNext ) dbdenyread( ) dbreadonly( ) << >> OpenRecordset DAO SQL MakeReplica(J) Database.MakeReplica " "," "[, ]
dbrepmakepartial( ) dbrepmakereadonly( ) Access ( ) Synchronize (J) Database.Synchronize [, ] 5-7 dbrepexportchanges 1 dbrepimportchanges 2 dbrepimpexpchanges 4 dbrepsyncinternet 16 Internet URL 5-7 Synchronize PopulatePartial Database.PopulatePartial NewPassword(J) User Database Database User.NewPassword, User 14 5-17
5-17 5-17 Ch5\5-3\db.mdb Option Compare Binary 1003 1007 Users 1010 1017 << >> NewPassword CollatingOrder(,J) Database Field 21 dbsortchinesetraditional Connect(,D,J) Database TableDef QueryDef Access Access ( ODBC)
DB.CONNECT="dBASE IV;DATABASE= C:\dBASE\TEST.DBF " C:\dBASE TEST.DBF ( ) dbase IV (PWD= ) (UID= ) ODBC ODBC; DATABASE="database ";UID="jo"; PWD="ab"; MDB Name (,D,J) ( ) QueryTimeout(,D,J) Database ODBC Replicable(,J) Database CreateProperty Database Properties T T ReplicaID(,J) DesignMasterID(,J) ReplicaID 16 RecordsAffected(,D,J) Execute Updatable(,D,J) Database TableDef QueryDef Recordset DB.Updatable=True V1xNullBehavior(,J) Access 1.0 Access 2.0 7.0 97 True False Null Access 2.0 7.0 97 Null << >> IsNull Null Null
Version(,D,J) Jet 3.6 5-3-4 Relation Database DAO CreateField Relation Set Field =Relation.CreateField([" "][, ]) Attributes 5-5 Append ForeignTable( ) TabldDef QueryDef Attributes Name ( ) Table Attributes PartialReplica True False PopulatePartial
5-4 TableDef QueryDef Index Field Parameter Field 5-4-1 TableDef DAO CreateField TableDef Index Relation TableDef Set Field =TableDef.CreateField([" ",][,][ ]) Field 5-8 dbtext 1-255 / dbdate 8 dbcuurrency 8 / dbboolean 1 ( ) dbdouble 8 ( ) dbsingle 4 ( ) dblong 4 ( ) dbinteger 2 ( ) dbbyte 1 OLE dblongbinary 0 dbmemo 0 5-8
TableDef CreateField TableDefs << >> Field ( ) CreateIndex TableDef Set Index =TableDef.CreateIndex([" "]) 5-18 5-18 5-18 Ch5\5-5\Ta.mdb Module1 1005 CreateIndex newindex 1007 Index CreateField Index TableDef CreateField RefreshLink TableDef TableDef.RefreshLink
Connect Connect RefreshLink 5-16 1048 << >> CreateProperty OpenRecordset TableDef OpenRecordset re Recordset ta TableDef ta Set re=ta.openrecordset re OpenRecordset TableDef CreateProperty OpenRecordset Attributes TableDef Field Relation 5-4 Append Connect TableDef SourceTablename Attributes 5-16 ConflictTable( ) TableDef Recordset DateCreated( ) TableDef QueryDef Document Recordset LastUpdated( ) Name SourceTablename ( ) TableDef Connect Connect SourceTablename
5-16 RecordCount( ) TableDef Recordset Updatable( ) True False TableDef ValidationRule TableDef Field Recordset Ta.ValidationRule="[ ]>=[ ]*0.9" ValidationText TableDef Field Recordset ValidationRule KeepLocal CreateProperty TableDef Properties T ReplicaFilter True False " Like ' '" True False PopulatePartial TableDef Replicable Database 5-4-2 Index Index TableDef Index Field DAO CreateField Index TableDef
5-18 Set Field =Index.CreateField(" ") CreateProperty Index 5-14 Set Property =Index.CreateProperty([" ",][,][ ] ) Clustered Index True False Index DAO DAO ODBCDirect Index Append Foreign( ) Index True False Clustered DistinctCount( ) Index Index Index IgnoreNulls Index True False Index Null True Null( ) Index Clustered Name ( ) Primary Index True False True Index IgnoreNulls False False Clustered Unique Index
Index True Index True Index Primary Unique True Clustered Required Index Relation Field True ( Null) 5-4-3 QueryDef Field Parameter D ODBCDirect J DAO Execute(D,J) QueryDef.Execute([ ]) 5-6 Execute OpenRecordset OpenRecordset(D,J) Set Recordset =QueryDef.OpenRecordset([,][ ],[ ] ) QueryDef OpenRecord QueryDef 5-9 dbopentable 1 Table ( ) dbopendynamic 16 ( ODBCDirect) dbopendynaset 2 ( )
dbopensnapshot 4 dbopenforwardonly 8 5-9 Recordset 5-9 Recordset 5-6 5-10 dbreadonly 4 dbpessimistic 2 (Edit) dboptimistic 3 (Update) 5-10 << >> CreateProperty QueryDef Close Database ODBCDirect Cancel Connect(,D,J) QueryDef ODBC (;) "Paradox 4.X;Database=C:\PDX\Publish" ODBCTimeout(,D,J) QueryDef ODBC Query ReturnsRecords (,J) QueryDef True( ) False True ODBC False ODBC SQL(,D,J) QueryDef QueryDef SQL 5-19
5-19 SQL 5-19 SQL SQL Access SQL SQL 5-19 1004 1005 SQL Name 1006 QueryDefs 5-19 5-15 Type (,D,J) QueryDef Field Property Parameter Recordset QueryDef 5-11 dbqselect 0 dbqaction 240 dbqcrosstab 16 dbqdelete 32 dbqupdate 48 dbqappend 64 dbqmaketable 80 dbqddl 96 dbqsqlpassthrough 112 dbqsetoperation 128 dbqsptbulk 144 dbqcompound 160
5-11 LogMessages(,J) QueryDef QueryDef CreateProperty QueryDef Properties True False ODBC 0 RecordsAffected(,D,J) Execute Updatable(,D,J) QueryDef Query True MaxRecords(,D,J) 0 << >> QueryDef DateCreated( ) LastUpdated( ) Name( ) KeepLocal( ) Replicable( ) CachSize Prepare StillExecuting ODBCDirect 5-4-4 Parameter QueryDef (Name) (Value) ODBCDirect DAO Name ( ) Parameter Value( ) Parameter Property Field 5-20
5-20 5-20 Ch5\5-4\Ta.mdb Module1 [ ] Parameter 5-20 1004 X 1005 Type ( ) Parameter 5-8 << > Parameter VBA QueryDef CreateParameter Parameters Append SQL PARAMETER SQL QueryDef Parameter ODBCDirect Direction
5-5 DAO Recordset DAO Recordset Recordset Recordset Recordset Field 5-5-1 Recordset Recordset Database TableDef QueryDef OpenRecordset Recordset Table Recordset (ISAM ODBC) SQL Dynaset Recordset Table Snapshot Dynaset OLE Recordset Forward-only Snapshot Dynamic ODBCDirect Dynaset << >> Recordset Snapshot Table Dynaset Recordset
5-5-2 Recordset Recordset Recordsets Append Delete ( Close ) Recordset D ODBCDirect J DAO Dynamic DAO Recordset ODBCDirect AddNew(Dynaset D J,Table J) Recordset RE.AddNew Recordset 5-21 5-21 AddNew 5-21 Ch5\5-5\re.mdb Module1 OpenRecordset Recordset Dynaset( ) 1002 1003 1004
1005 1006 Dlookup X IsNull True Fasle IsNull Fasle 1008 AddNew 1009 1010 Update Update AddNew Update Clone(Dynaset,Table,Snapshot J) Recordset ( Replica) Recordset Set Recordset 1=Recordset 2.Clone Close(Dynaset D J,Table J,Snapshot D J) Recordset CancelUpdate (Dynaset D J,Table J) AddNew Edit Update Recordset CopyQueryDef(Dynaset,Snapshot,Fowardonly D J) Recordset 5-22 5-22 CopyQueryDef CopyQueryDef Recordset Recordset QueryDef 1001 h
QueryDefs ( 1005 h Recordset) 1006 CopyQueryDef QueryDef Delete(Dynaset D J,Table J) Recordset 2 re.delete 2 Edit(Dynaset D J,Table J) 4-24 FillCache(Dynaset J) ODBC Access ODBC Recordset.FillCache [,][ ] CacheSize CacheStart FindFirst FindLast FindNext FindPrevious(Dynaset, Snapshot J) Recordset.FindFirst SQL 5-23 5-23 FindLast 5-23 1004 FindLast m ( ) Recordset
Access 5-23 Move(Dynaset D J,Table J,Snapshot D J) Recordset Recordset.Move [, ] Bookmark MoveFirst MoveLast MoveNext MovePrevious (Dynaset D J,Table D,Snapshot D J) Recordset Fowardonly MoveNext << >> MoveNext Do EOF 4-20 4-24 GetRows(Table D,Dynaset,Snapshot,Fowardonly D J) Recordset Variant 5-24 5-24 GetRows 5-24 1003 GetRows 1004 For 0 0 Top5(0,0) 0( ) 5-24
1003 GetRows Recordset 5-24 GetRows 5 6 ( ) << >> GetRows OLE OpenRecordset(Dynaset,Table,Snapshot J) Recordset A Recordset Requery(Dynaset,Snapshot,Fowardonly D J) Recordset Recordset ( SQL ) Seek(Table J) Index Recordset.Seek " ", = > < 5-25
5-25 Seek 5-25 Table Recordset (Re) Table Recordset Seeek PrimaryKey 1003 Index 1004 Seek 1005 NoMatch (True) (False) << >> Seek Index NoMatch Table Recordset Seek Recordset Snapshot Dynaset Find Recordset Seek Seek Table Recordset Update(Dynaset D J,Table J) Recordset 5-21 NextRecordset Cancel ODBCDirect
Recordset Recordset T TableD DynasetS SnapshotF Fowardonly False AbsolutePosition 0 1-1 24-23 (D:,S: ) BOF True False ( ) << >> EOF Do 5-16 BOF EOF BOF EOF 5-26 BOF=True Record 1 Record 2 Record 3 EOF=True 5-26 BOF EOF 5-26 Record 1 Record3 BOF EOF Recordset BOF EOF True Bookmark 1( Fowardonly Table DAO) 5-27
5-27 Bookmark 5-27 (RND) Recordset 1003 newmark For 1007 Move Int(Rnd * rw.recordcount) Rnd Recordset Int ( ) newmark 1003 Bookmarkable Recordset True(-1) MDB MDB MDB Recordset ( Fowardonly Table DAO) CacheSize RE1.CacheSize=20 20 5-1200 ODBC (D:, ODBCDirect ) CacheStart
RE1.FindFirst " ' '" CacheStart=RE1.Bookmark CacheSize=30 RE1.FillCache Cache 30 (D: ) DateCreated Recordset (T: ) EditMode 5-12 dbeditnone 0 dbeditinprogress 1 Edit dbeditadd 2 AddNew 5-12 EditMode EditMode Update ( ) EOF BOF EOF True False BOF ( ) Filter RE1.Filter ="[ ]>'1/1/94'" Filter OpenRecordset (D:,S:,F DAO) Ch5\5-5\re.mdb _ Filter 5-28
5-28 Filter 5-28 1001 Recordset m 1002 Filter 1003 1004 << >> 5-28 DAO 1004 DAO 1004 Index Seek Indexes Index 5-25 (T: DAO) LastModified Bookmark Recordset (D:,T: DAO ) LastUpdated Recordset (T: DAO ) LockEdits True Page( Access Page ) True Edit ( Fowardonly Table DAO) Name ( ) NoMatch Find Seek
True False 5-25 ( Fowardonly Table DAO) PercentPosition 100 AbsolutePosition ( Fowardonly Table DAO) RecordCount Recordset OpenRecordset Requery ( Table DAO) << >> Recordset Dynetset Snapshot MoveFirst MoveLast RecordCount Restartable True Requery False Requery OpenRecordset (T:False Table DAO) Sort RE1.Sort="[ ] DESC" DESC ASC OpenRecordset (D:,S: DAO) Transactions Recordset Transactions True Workspace BeginTrans CommitTrans Rollback Recordset MDB True Transactions (D:,S:False,T:,F:False DAO) Type Recordset 1 Table 2 Dynaset 4 Snapshot ( ) Updatable True(-1) Recordset TableDef QueryDef (D:,S:False,T: ) ValidationRule Recordset (
Table DAO) ValidationText Recordset ( ) ODBCDirect 5-5-3 Field TableDef Index QueryDef Recordset Relation Field Recordset AppendChunk Recordset Field OLE Recordset![ ].AppendChunk OLE GetChunk GetChunk Recordset Field OLE =Recordset![ ].GetChunk(, ) String Variant Recordset OLE AppendChunk FieldSize GetChunk AppendChunk OLE
<< >> CreateProperty Field Database CreateProperty FieldSize Recordset Field OLE =Recordset![ ].FieldSize Variant Long 65535 bytes OLE AllowZeroLength Index Relation Field True Recordset QueryDef TableDef Attributes( ) Relation Field dbfixedfield 1 dbvariablefield 2 ( ) dbautoincrfield 16 dbupdatablefield 32 dbdescending dbsystemfield 8192 dbhyperlinkfield 32770 5-13 CollatingOrder( ) Index Relation Field 21 dbsortchinesetraditional dbsortgeneral DataUpdatable( ) Recordset Field
True DefaultValue Index Relation Field FD1.DefaultValue=" " OLE QueryDef Recordset Field TableDef FieldSize ( ) Recordset Field OLE ForeignName ( ) Relation Field ( ) Relation ForeignTable ( ) Relation Field ForeignTable Name Field TableDefs OrdinalPosition Index Relation Field Recordset ( 0) FD1 1 FD2 0 FD2 FD1 Refresh QueryDef Recordset Field TableDef Required Index Relation Field True Null ( ) OrdinalPosition Size Index Relation Field 39 7 (1 255) SourceField( ) Index Relation Field SourceTable( ) Index Relation Field Type
5-8 ValidateOnSet( ) Recordset Field True False Update ValidationRule Index Relation Field ( ) QueryDef Recordset Field TableDef ValidationText Index Relation Field ValidationRule ( ) ValidationRule Value( ) re Recordset re("writer") re("writer").value
5-6 User Group DAO User Groups Group Users johnson DAO Workspace johnson User Groups Group Workspace Group Users johnson User 5-6-1 Group CreateUser Set User =Group.CreateUser([" ",]["PID",][" "]) Append User PID Ch5\5-6\User&Grp.mdb PID 5-29
5-29 5-29 1001 1004 1005 gp Group Groups(0) 1007 1008 User Workkspace gp (Group) PID Workspace gp User 1009 1010 Workspace Usres User Name ( ) PID( ) PID 5-6-2 User 5-29
CreateGroup User User Group Set Group =User.CreateGroup([","]["PID"]) 5-29 Workspace User Workspace User Groups User Groups Group Workspace NewPassword User User ( ) User.NewPassword " "," " Name ( ) User Password( ) User ( ) PID( ) User Group PID ( )
5-7 Container Document Property Error 5-7-1 Container Document Access (...) Container Document Container DAO Access Object Container Container Document Database CreateContainer Containers Documents Append Delete Document Containers Container DataAccessPages Databases Forms Modules Relationships Reports Scripts SysRel Tables 0 8 Documents 5-30 5-30
5-30 Ch5\5-7\Con&Doc.mdb Module1 Container Tables 1003 For M 1005 X 1008 << >> Container Forms Container Forms Access Object Forms Container 5-30 1002 Container 5-30 DBEngine Set db=currentdb Set CO = db.containers("tables") CurrentDB Container Set CO = CurrentDB.Containers("tables") Name ( ) Container Owner( ) Container Document Permissions( ) Container Document 5-14 dbsecnoaccess 0 dbsecfullaccess 1048575 dbsecdelete 65536
dbsecreadsec 131072 dbsecwritesec 262144 dbsecwriteowner 524288 Owner dbseccreate 1 Tables ( ) dbsecreaddef 4 Tables dbsecwritedef 65548 Tables dbsecretrievedata 20 Tables Document dbsecinsertdata 32 Tables dbsecreplacedata 64 Tables dbsecdeletedata 128 Tables dbsecdbadmin 8 Databases dbsecdbcreate 1 Databases dbsecdbexclusive 4 Databases dbsecdbopen 2 Databases 5-14 Permission + n.permissions= dbsecwritesec + dbseccreate n Tables Container 5-14 Container MDB AllPermissions ( ) ( UserName ) 5-14 UserName ( ) Permission AllPermissions
Inherit( ) True False Document ( Permissions ) Owner( ) User << >> Access A B Container Set co=db.containers("forms") co db Container Owners ( ) Container Owner ( ) 5-7-2 Document Container Document ( Forms Container ) ( Modules Container ) ( ) CreateProperty Database CreateProperty Container( ) Document Container Document DO1DO1.Container Tables DateCreated( ) Document LastUpdated( ) Document TableDef QueryDef Recordset Name ( ) Document KeepLocal( )
Document CreatePrperty Document Properties T Document Replicable( ) Database Document CreateProperty Document Properties T ( ) T << >> Replicable KeepLocal (Replicable T ) ( Document ) KeepLocal False Replicable T KeepLocal Owner Permissions AllPermissions UserName Container 5-7-3 Property ( ) Properties Document Properties Access 5-31
5-31 5-31 Ch5\5-7\Con&Doc.mdb 5-31 Tables Document Document 1011 1015 ( ) Property Inherited True QueryDef A Recordset A QueryDef (Inherited True ) Name
Type 44 5-8 Value( ) 5-31 5-7-4 Error DAO DAO Err Access VBA Error Err DAO Error 5-32 Error 5-32 5-32 make 1007 1008 Error 3265 << >> DBEngine Errors Error Error Error Error ODBC ODBC
Access (Access ) Error Access Errors Error Error Error Description( ) Error HelpContext( ) Error ID ACCESS ID HelpFile( ) Error MSJETERR.HLP Number( ) Error 5-32 Source( ) DAO.Database Access VBA Excel VBA DAO Excel.Application
5-8 ODBCDirect ODBCDirect DBEngine ODBC DBEngine ODBCDirect DBEngine ODBC DAO ODBCDirect DAO ODBCDirect DAO ODBC ODBCDirect DAO DBEngine ODBC ODBC ODBC DBEngine ODBCDirect VBA ODBC ODBC Table Recordset 5-6 Recordset Dynamic Dynaset Forwardonly Snapshot ODBCDirect Access Execute MoveLast OpenConnection OpenRecordset dbrunasync ODBCDirect Access( ODBCDirect ) ( CacheSize FillCache ) ODBCDirect ODBC ODBCDirect DBEngine ODBC ( Database Connect ) ODBCDirect TableDef ODBCDirect ODBCDirect
5-8-1 ODBCDirect Workspace ODBCDirect DAO 5-33 5-33 ODBCDirect 5-33 Workspace Connections Connection ODBC 5-8-2 ODBCDirect Ch5\5-8\Odir.mdb Autoexec ODBC ODBC ODBC TEST_58 Ch5\5-8\Dataod.mdb ODBCDirect ODBC (Workspace) Access 0 Workspace ODBCDirect Access Workspace 5-34
5-34 ODBCDirect 5-34 Ch5\5-8\Odir.mdb Module1 CreateWorkspace dbuseodbc ODBCDirect 1002 Workspace OpenConnection ODBC 5-34 Con1 Connection ( ) (True False) (Connect) 1003 ODBC Table dbopendynamic Recordset ODBCDirect Dynamic 1004 1006 1008 03 ODBCDirect ODBC (OpenRecordset) DAO ODBCDirect << >> 5-34 1002 dbdrivernoprompt odbc;dsn=winwalk; ODBC dbdriverprompt
Set ck1 = wkodbc.openconnection("con1", dbdriverprompt,, "odbc;") dbdriverprompt ODBCDirect ODBCDirect ODBCDirect ODBCDirect Click Sub ODBCDirect DAO ODBCDirecrt D ODBCDirect Workspace 5-8-3 Workspace ODBCDirect Access Workspace ODBCDirect Workspace 5-34 ODBCDirect Workspace ODBCDirect OpenConnection 5-34 1002 ODBC 5-15 dbdrivernoprompt dbdriverprompt OpenConnection Connect 5-34
dbdrivercomplete dbdrivercompleterequire d dbrunasync Connect ( ) ODBC ODBC ( + ) 5-15 ODBC DefaultCursorDriver( ) ODBCDirect Workspace Cursor Cursor ODBCDirect ODBC ( ACCESS) 5-16 dbusedefaultcursor -1 ODBCDirect Workspace ODBCDirect dbuseodbccursor 1 ODBC ODBCDirect dbuseservercursor 2 ODBCDirect dbuseclientbatchcur sor 3 ODBCDirect dbusenocursor 4 Forwardonly Recordset 5-16 DefaultCursorDriver ODBCDirect ODBC ODBC
ODBC SQL Server dbuseservercursor dbuseodbccursor OpenRecordset SQL 5-35 5-35 DefaultCursorDriver 5-35 SQL Server DefaultCursorDriver dbuseservercursor dbuseodbccursor 1004 OpenRecordset SQL ; ; NextRecordset Recordset Recordset SQL Server Access ODBCDirect DAO Database Connect DAO SQL Server ODBCDirect DAO 5-35 ODBC SQL Server Pubs Employee Jobs Table Pubs LoginTimeout( ) ODBC << >> ODBCDirect Workspace Workspace
5-8-4 Connection DAO ODBCDirect ODBC 5-35 Cancel Connection ODBC Close Connection ODBCDirect 5-36 5-36 Cancel 5-36 1002 dbrunasync ODBCDirect 1003 StillExecuting Recordset Connection True False 1003 con.stillexecuting True 1004( StillExecute False) OpenConnection Close 5-36 wkod.close wkod Workspace Connection
CreateQueryDef OpenRecordset Execute Database( ) Connection Set db=con.database Connection StillExecuting( ) Connection ODBCDirect Recordset True False 5-36 Connect( ) Name(Connection ) QueryTimeout( ) RecordsAffected( ) Transactions( ) Updatable( ) 5-8-5 Recordset ODBCDirect ODBCDirect 5-5 Dynamic ODBCDirect Recordset Fowardonly Snapshot Dynaset Table DAO ODBCDirect Recordset Table ODBCDirect Recordset 5-5 Cancel Recordset OpenRecordset NextRecord Recordset 5-35 True False BatchCollisionCount( Table )
5-37 5-37 BatchCollisionCount 5-37 1002 Workspace DefaultCursorDriver dbuseclientbatchcursor 1005 1012 1013 BatchCollisionCount 0 1015 Update dbupdatebatch True BatchCollisions ( Table ) BatchCollisionCount BatchSize ( Table ) ( ) 15 Connection( Table )
Recordset Connection( ) RecordStatus ( Table ) 5-17 dbrecordunmodified dbrecordmodified dbrecordnew dbrecorddeleted dbrecorddbdeleted 5-17 RecordStatus UpdateOptions( Table ) WHERE UPDATE INSERT DELETE UPDATE 5-18 dbcriteriakey dbcriteriamodvalues dbcriteriaallcols dbcriteriatimestamp dbcriteriadeleteinsert dbcriteriaupdate WHERE WHERE WHERE TimeStamp WHERE DELETE INSERT UPDATE 5-18 UpdateOptions UPDATE SQL UPDATE Orders SET OrderAmount = OrderAmount * 1.1, Freight = Freight * 1.03 WHERE
ShipCountry = 'U.S.A'; SQL StillExecuting 5-5 5-8-6 QueryDef ODBCDirect Cancel CacheSize StillExecuting Prepare Prepare ( ) QueryDef 5-19 dbqprepare dbqunprepare ODBC SQLPrepare API (Stored Procedure) ODBC SQLExecDirect API 5-19 Prepare Stored Procedure SQL Server SQL Prepare QueryDef ( CreateQueryDef ) Stored Procedure SQLPrepare API QueryDef SQL QueryDef Prepare dbqprepare dbqunprepare SQL Prepare 5-38
5-38 Prepare 5-38 1003 QueryDef SQL 1004 Prepare dbqunprepare Stored Procedure 1005 QueryDef SQL Prepare 5-38 1002 Access 5-38 SQL Server ODBC 5-8-7 Parameter Direction( ) Parameter 5-20 dbparaminput dbparaminputoutput dbparamoutput dbparamreturnvalue ODBC ODBC ODBC 5-20 Direction Direction (ODBC )
<< >> ODBCDirect ODBC Access Access ODBCDirect ODBC SQL Server ODBC ODBCDirect SQL Server