穨ac3-6.PDF

6-1 ADO ADO Access 2000 ADO ADOX 6-1-1 ADO ADO Connection Command Recordset Parameter Field Error 6-1 6-1 ADO 6-1 Properties Property Connection ADO (Open ) Recordset Command Command SQL Connection SQL Parameter(s) Command Command Command Recordset Command Command Recordset Field(s) Recordset Error(s) ADO Connection s Recordset Command ADO

<< >> ADO Access 2000 ADO Access 97 2000 Visual Basic ADO 2.1 4-1 6-1-2 ADO ADO Access2000 ADO ADO DAO RDO (Provider) ADO Sub () MsgBox CurrentProject.Connection.Provider End Sub Access 2000 Provider Microsoft.Jet.OLEDB.4.0 ADO Access 2000 ADO Connection 6-1 Provider OLE DB Provider for Microsoft Jet Microsoft.Jet.OLEDB.4.0 Microsoft.Jet.OLEDB.3.51 SQLOLEDB

OLE DB Provider for ODBC MSDASQL ODBC MSDAORA MSIDXS OLE DB Persistence Provider OLE DB Remoting Provider MSPersist MS Remote 6-1 6-1 Provider Access 2000 6-1 ( )

Connection Provider ConnectionString Sub () MsgBox CurrentProject.Connection.ConnectionString End Sub Access 2000 ADO Provider Jet VB 6.0 DataEnvironment VB 6.0 ADO Connection ConnectionString 6-2 6-2 6-2

DataEnvironment Connection ConnectionString ODBC ODBC ADO ODBC ODBC ODBC 32 ODBC 6-3 6-3 6-3 C:\Program File\Common Files\ODBC\Data Source DSN 6-4 6-4 SQL ODBC 6-4 SQL ConnectionString ADO ODBC SQL Server

Connection Connection Connection 6-5 6-5 Connection 6-5 Ch6\6_ALL\ADOSam.mdb Module1 x 1007 ConnectionString SERVER y 6-4 Provider SQL Server 1008 Open 1008 Connection 6-5 6-6 6-6 Connection

6-6 6-5 x Provider 6-6 Provider x ConnectionString Open x ADO Recordset Command Recordset SQL SQL 6-7 6-7 Recordset 6-7 SQL Server 7.0 SQL Server 7.0 Northwind 6-7 6-6 rs ADODB.Recordset 6-7

re ActiveConnection(Connection ) Source(SQL ) LockType( ) CursorType( ) 1013 Open Recordset rs 1022 Open Open ADO Open Command SQL Server 6-8 6-8 Command 6-8 ADO Connection Command Recordset Command 1011 1012 CommandType Command CommandText 1013 1014 Command rs Set rs = cm.execute Execute rs 6-7 1010 1011 << >>

ADO Recordset Command 6-8 1014 rs Open Command 6-7 1022 SQL Command SQL Command Recordset Command Recordset SQL Recordset Command Execute Parameter Command Command 6-8 4-23 6-1-3 Access 2000 ADO Access 2000 Access 2000 ADO ADO Access ( ) DAO ADO ADO ADO Access ADO (ADP) 6-9

6-9 6-9 Microsoft OLE DB Provider for SQL Server Access 2000 ADO Access Access Connection CurrentProject.Connection ADO 4-19 Access 2000 Recordset Ch6\6_ALL \ADOSam.mdb SQL Server Northwind Customers CustomerID Northwind CustOrderHist ADO 6-10

6-10 ADO 6-10 AfterUpdate SQL Server SQLServerName 1018 rs 1019 6-10 ADO 6-11

6-11 ADO 6-11 ADORS ADO.Recordset tname Boolean ro False 6-11 SQL Server 1009 1017 1012 1016 Open ( ) ( ) 1017 rs 6-11 ADO Ch6\6_ALL \ADOSam.mdb _SQL 6-11 6-12

6-12 6-12 _SQL Load 6-11 customers For << >> 6-11 Access 2000 ADO Access 2000 ADO SQL Server SQL Server 7.0 NorthWind << >> ADO Access 2000 DAO ADO DAO

6-2 Connection Help MSDN Access 2000 Connection ADO Open Connection BeginTrans CommitTrans RollbackTrans Connection 6-13 6-13 6-13 1003 1005 X ( ) Cancel State

Cancel 6-14 6-14 6-14 1006 1006 1007 1007 State Cancel 6-14 1006 1007 State ADO 6-11 1008 Open 1009 Close Connection Connection Commad Recordset Connection Connection Execute SQL SQL SQL 6-2 adcmdtext adcmdtable SQL SQL Table SELECT

adcmdtabledirect SQL Table adcmdstoredproc adcmdunknown adexecuteasync adfetchasync SQL SQL CacheSize 6-2 CacheSize Recordset 100 CacheSize 30 30 70 Execute SQL SQL Recordset Execute 6-10 1005 SQL 6-15 6-15 Execute 6-15 1001 1008 1009 Connection Execute rs 6-13 1005 6-15 1009 Recordset ( 6-10 1018) Recordset Open 6-15 1008 ADO Open

Connection.Open, ID,, Connection ConnectionString adasyncconnect OpenSchema Recordset Set Recordset =Connection.OpenSchema( [, ][, GUID]) Ch6\6_All\Schema.mdb 6-16 6-16 6-16 Ch6\6_All\Schema.mdb QType QTCriteria ID adschemaasserts CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME ID

Access 2000 OpenSchema Ch6\6_All\Schema.mdb Schema VB6 VB6 Attributes Connection (Transaction) adxactcommitretaining adxactabortretaining CommitTrans RollbackTrans 0 CommandTimeout 30 ConnectionString Provider Connection ConnectionTimeout 15 CursorLocation aduseclient aduseserver ACCESS SQL Server 7.0 ADO

DefaultDatabase Connection SQL Server Catalog Access IsolationLevel Connection 6-2 adxactunspecified -1 Connection adxactchaos 16 adxactbrowse 256 (Dirty ) adxactreaduncom mitted adxactcursorstabi lity adxactreadcommi tted adxactrepeatable Read adxactisolated 256 4096 4096 65536 1048576 adxactserializable 1048576 6-2 IsolationLevel BeginTrans IsolationLevel BeginTrans Mode Connection 6-3 admodeunknown 0( )

admoderead 1 admodewrite 2 admodereadwrite 3 admodesharedenyrea d admodesharedenywri te admodeshareexclusiv e admodesharedenynon e 4 8 12 16 6-3 Mode 6-3 Mode Connection Open 6-3 Mode Connection Connection Provider Connection MSDASQL(ODBC) Provider 6-5 State Connection adstateclosed( ) adstateconnecting( ) adstateopen( ) 6-14 Version ADO << >> Connection Connection

6-3 Recordset Field Recordset 6-3-1 Recordset AddNew Recordset Recordset ( )= Update 6-17 6-17 Addnew 6-17 ( 1012 1014) 1005 LockType CursorType Cancel ADO Connection Command Recordset

6-18 6-18 6-18 1009 adasyncexecute 1011 Do State 1014 1016 Timer( ) h( ) CancelBatch 6-4 adaffectcurrent 1 adaffectgroup 2 Filter adaffectall 4 () 6-4 6-19

6-19 6-19 SQL Server 7.0 Northwind Customers 1009 adlockbatchoptimistic 1017 UpdateBatch CancelBatch CancelUpdate Update CancelUpdate Close Recordset Update Clone Recordset Recordset Set rst =rst.clone( ) LockType Recordset

(adopenforwardonly) Recordset Delete ( 6-19) UpdateBatch CancelBatch 6-5 adaffectcurrent 1 ( ) adaffectgroup 2 Filter 6-5 rs.delete Delete DataGrid deok Move Recordset 6-6 adbookmarkcurrent 0 ( ) adbookmarkfirst 1 adbookmarklast 2 6-6 Recordset (adopenforwardonly) ( 1 2) ( 2 1) MoveFirst MoveLast MoveNext MovePrevious Recordset 6-18 1015 MoveNext Do NextRecordset Recordset SQL select * from Customers;select * from Orders SELECT ; ADO SELECT NextRecordset Recordset SQL SELECT

Recordset 6-20 6-20 NextRecordset 6-20 Ch6\6_ALL\ADOSam.mdb SQL Server NorthWind CustomerID CustomerID 6-20 Click 1004 1007 k select * from Orders Where CustomerID like 'ALFKI SQL SELECT SQL ; SQL Recordset 1020 SELECT SQL SQL Recordet NextRecordset 1027 Recordset 1027 Set

Access Access SELECT SQL SQL Server Open Recordset Recordset.Open [ ][,Connection ][, ][, ] Recordset Source ActiveConnection CursorType LockType Command CommandType Recordset 6-19 1020 Recordset Open Open Recordset Recordset Requery Recordset Source Close Open Resync Requery Resync 6-7 adaffectcurrent 1 adaffectgroup 2 Filter adaffectallchanges 4 ( ) 6-7 Save Recordset Recordset.Save, Save adpersistadtg

newre.open "c:\tet1", newcn,,,adcmdfile adcmdfile Recordset Save Save Supports True False =Recordset.Supports( ) 6-8 adaddnew 16778240 AddNew adapproxposition 16384 AbsolutePosition AbsulutePage adbookmark 8192 Bookmark addelete 16779264 Delete adholdrecords 256 admoveprevious 512 MoveFirst MovePrevious adresync 131072 Resync adupdate 16809984 Update adupdatebatch 65536 6-8 Supports 6-8 True admoveprevious (adopenforwardonly) MoveFirst MovePrevious Supports admoveprevious False Update 6-21

6-21 Update 6-21 Ch6\6-All\ADOSam.mdb _ SQL Server 6-11 Northind Customer CustomerID CompanyName Update 1008 1010 1011 Update 1005 1006 Array Array d d1 UpdateBatch 6-4 6-18 1018 AbsolutePage 1 6-9 adposunknown -1 Recordset

adposbof -2 Recordset adposeof -3 Recordset 6-9 AbsolutePage PageCount AbsolutePosition 1 (RecordCount ) 6-9 ActiveConnection Recordset Connection Connection Recordset 6-6 1009 BOF EOF True False( ) True (BOF) (EOF) True 6-19 1022 Bookmark Recordset (CursorType ) ForwardOnly Dim m1 as ADODB.Recordset k1=m1.bookmark m1.bookmark=k1 Bookmark m1.bookmark=k1 k1 CacheSize 1 0 5 5 ADO CursorLocation

Recordset 6-10 aduseclient 3 aduseserver 2 ( ) 6-10 CursorType Rcordset 6-11 adopenforwardonly 0 MoveNext ( ) adopenkeyset 1 adopendynamic 2 adopenstatic 3 6-11 ADO Recordset 6-11 EditMode 6-12 adeditnone 0 adeditinprogress 1 adeditadd 2 AddNew adeditdelete 3 6-12 EditMode Filter Recordset SQL Where 6-13

adfilternone 0 adfilterpendingrecords 1 CancelBatch adfilteraffectedrecords 2 Delete Resync UpdateBatch adfilterfetchedrecords 3 adfilterconflictingrecords 5 6-13 Filter 6-13 Where 6-22 6-22 Filter 6-22 1011 1014 Filter 1015 Recordset

LockType Recordset 6-14 adlockreadonly 1 ( ) adlockpessimistic 2 adlockoptimistic 3 adlockbatchoptimistic 4 6-14 LockType MarshalOptions admarshalall( ) admarshalmodifiedonly( ) Update MaxRecords Recordset 0 PageCount Recordset -1 PageSize PageSize 10 10 PageSize PageCount Ab solutepage RecordCount Recordset (adopenforwardonly) -1 Sort Recordset, Source Recordset Command SQL Recordset 6-7 1010

State Recordset Recordset adstateclosed() adstateexecuting ( ) adstatefetching( ) adstateconnecting ( ) adstateopen( Status 6-15 adrecok 0 adrecnew 1 adrecmodified 2 adrecdeleted 4 adrecunmodified 8 adrecinvalid 16 adrecmultiplechanges 64 adrecpendingchanges 128 adreccanceled 256 adreccantrelease 1024 adrecconcurrencyviolati on 2048 adrecintegrityviolation 4096 ( ) adrecmaxchangesexceed ed adrecobjectopen adrecoutofmemory adrecpermissiondenied adrecschemaviolation adrecdbdeleted 8192 16384 32768 65536 131072 262144 6-15 Recordset

Status Status << >> Recordset Access 2000 DAO ADO Access 2000 Access 6-3-2 Field Recordset Field GetChunk AppendChunk (GetChunk) (AppendChunk) Access OLE SQL Server Image ActualSize 255 10 10 Attributes Field 6-16 adfldmaydefer 2 adfldupdatable 4 adfldunknownupdatab le 8 adfldfixed 16

adfldisnullable 32 Null adfldmaybenull 64 Null adfldlong 128 GetChunk AppendChunk adfldrowid 256 adfldrowversion 512 adfldcachedeferred 4096 6-16 Field Attributes Attributes Attributes Attributes 6-16 Ch6\6_All\ADOSam.mdb 6-16 Attributes_fd Attributes 6-24 6-24 Attributes 6-24 Attributes Attributes Click 6-25

6-25 Attributes 6-25 Re cordset Attributes_fd 1005 1006 1007 Attributes 1009 Attributes_fd 1010 Attributes X1 X 1011 6-16 6-26

6-26 6-26 Auttributes 2 6-1 2 cd ( ) 6-25 6-26 6-24 6-25 1012 1018 DefinedSize Name Field NumericScale Field 0-256 OriginalValue Field Precision Field 0-256 Type Field SQL Server 7.0 ad UnderlyingValue Field Resync OriginalValue Value Field << >>

Field Value SQL Server Recordset Fields Access 2000 SQL Server

6-4 Command Parameter Command Access Parameter 6-4-1 Command SQL Recordset SELECT Recordset UPDATE DELETE Cancel Command Execute adexecuteasync Execute Command Command CommandText Connection Execute CreateParameter Command Parameter Set Parameter =Command.CreateParameter([ ][, ][, ][, ][, ]) 4-23 SQL Server 7.0 ad adinteger 6-17 adparamunknown 0 adparaminput 1 ( ) adparamoutput 2 adparaminputoutput 3 adparamreturnvalue 4 6-17 6-17 Parameter Direction Access 2000

adparaminput ActiveConnection Command Connection ( ) Connection Command Command Connaection CommandText SQL Command 6-8 1011 Co mmandtimeout Command 30 Connection CommandTimeout Command CommandTimeout Connection CommandTimeout CommandType CommandText 6-7 1012 6-18 adcmdtext 1 (SQL ) adcmdtable 2 Table SELECT adcmdtabledirect 512 Table adcmdstoredproc 4 adcmdunknown 8 adexecutenorecords 128 CommandText adcmdfile 256 6-18 CommandType 6-18 adcmdunknown Command CommandType adcmdunknown

adexecutenorecords adcmdtext adcmdtext+adexecutenorecords CommandText SQL adexecutenorecords ADO adcmdfile Recordset Recordset Save Prepared True False( ) True Command Comaand Command False True Command State Command Command adstateclosed( ) adstateexecuting ( ) adstatefetching( adstateopen( ) 6-4-2 Parameter 6-8 4-23 Command Command Parameters AppendChunk Parameter Attributes adparamlong AppendChunk Parameter. AppendChunk Data GetChunk AppendChunk Parameter AppendChunk Attributes Parameter

6-19 adparamsigned 16 adparamnullable 64 Null adparamlong 128 6-19 Parameter Attributes Direction 6-17 Name NumericScale Parameter 0-256 Precision Parameter 0-256 Size Parameter Command Size Type Parameter SQL Server 7.0 ad 4-23 Value Parameter Command 6-8 1013 Value m.parameters(1).value= 1234 << >> Command Parameter Command SQL SQL Command Command

6-5 Error( ) Property( ) 6-5-1 Error ADO Connection Error 6-9 1021 Error Description Error NativeError ADO ( ADO) 0 Number Error Source Connection ODBC Microsoft OLE DB Provider for ODBC Drivers SQLState ANSI ANSI SQL << >> Error ADO ODBC ADO Error ADO Error Connection BeginTransComplete CommitTransComplete ConnectComplete ExecuteComplete RollbackTransComplete Error perror Error perror

6-5-2 Property ADO. Connection ConnectionString Connection Properties Property ODBC Connection 88 Property Recordset 75 Property Property Property Name( ) Type( ) Value( ) Property Attributes Property 6-20 adpropnotsupported 0 adproprequired 1 adpropoptional 2 AdPropRead 512 AdPropWrite 1024 6-20 Attributes Property 6-20 6-27

6-27 Connection Property 6-27 Ch6\6_All\ADOSam.mdb Prperty 6-26 6-27 Connection ( 1007) 1009 PropertyValue 6-27 Connection << >> Error Property ADO

6-6 ADO ADO Access 2000 Access Form( ) Report( ) Access Propery Sub Function Connection Recordset ADO ADO Ch6\6_All\ADOSam.mdb clscustomerquery 6-6-1 ADODB.Connection ADO.Recordset 6-28

6-28 6-28 Class_Initialize cusquery DB ADO Connection Recordset Public WithEvents DB cusquery 6-29 6-29 6-29 DB InfoMessage WithEvents 6-6-2 DB cusquery 6-27 6-30

6-30 6-30h6\6_All\ADOSam.mdb _SQL1 Load k clscustomerquery 6-29 DB clsquery 6-28 6-30 Set k=new clscustomerquery clscustomerquery 6-28 Initialize << >> 6-29 clscustomerquery Sub Property ( ) ( 6-6-3 ADO ADO 6-28 Connection Connection Command BeginTransComplete (Transaction) TransactionLevel perror adstatus pconnection

TransactionLevel Transaction 1 2 perror Error adstatus 6-21 adstatuscancel 4 adstatuscantdeny 3 adstatuserrorsoccured 2 adstatusok 1 adstatusunwantedevent 5 6-21 adstatus Status ADO Status 6-21 pconnection Transaction Connection CommitTransComplete CommitTrans Transaction perror adstatus pconnection BeginTransComplete RollbackTransComplete Rollback CommitTransComplete << >> Transaction BeginTrans CommitTrans RollbackTrans WillConnect Connection ConnectionString UserID Password Options adstatus ConnectionString UserID Password Options Command CommandType adcmdtext adcmdtable adcmdtabledirect adcmdstoredproc adcmdunknown adstatus 6-21

ConnectComplete Connection perror adstatus pconnection BeginTransComplete Disconnect Close Connection adstatus pconnection BeginTransComplete ExecuteComplete Connection Command RecordsAffected perror adstatus pcommand precordset pconnectioon RecordsAffected Command Command Command perror Error adstatus 6-21 pcommand Command precordset Recordset pconnectioon Command Connection WillExecute Connection Command Source CursorType 6-11LockType( 6-14) Options pcommand precordset pconnection Source Command SQL CursorType 6-11 LockType Command 6-14 Options Command adcmdtext adcmdtable adcmdtext adcmdtabledirect adcmdstoredproc adcmdunknown adstatus 6-21 Command Recordset Connection InfoMessage Connection ( ) perror( ) adstatus( 6-21) pconnection( ) Recordset EndofRecordset (EOF) fmoredata adstatus precordset

fmoredata True False True adstatus 6-21 precordset Recordset EndofRecordset FieldChangeComplete cfields Fields perror adstatus precordset cfields Fields perror Error adstatus 2 adstatus 6-21 precordset EndofRecordset WillChangeField FieldChangeComplete FieldChangeComplete WillChangeField FieldChangeComplete perror RecordChangeComplete adreason crecords perror adstatus precordset adreason 6-21 adrsnaddnew 1 adrsndelete 2 adrsnupdate 3 adrsnundoupdate 4 adrsnundoaddnew 5 adrsnundodelete 6 adrsnfirstchange 11 6-22 RecordChangeComplete adreason crecords perror Error adstatus 2 adstatus 6-21 precordset EndofRecordset WillChangeRecord RecordChangeComplete WillChangeRecord RecordChangeComplete

RecordChangeComplete perror MoveComplete adreason adstatus precordset adreason 6-23 adrsnmovefirst 12 adrsnmovelast 15 adrsnmovenext 13 adrsnmoveprevious 14 adrsnmove 10 Move adrsnrequery 7 6-23 MoveComplete adreason adstatus 6-21 precordset EndofRecordset WillMove adreason adstatus precordset MoveComplete RecordsetChangeComplete Requery adreason perror adstatus precordset adreason 6-24 adrsnrequery 7 adrsnresynch 8 adrsnclose 9 6-24 RecordsetChangeComplete adreason WillChangeRecordset adreason adstatus precordset adreason 6-24 adstatus precordset 6-21 EndofRecordset

Command Command Recordset EndofRecordset MoveComplete RecordChangeComplete RecordsetChangeComplete Fie ldchangecomplete WillChangeField WillChangeRecord WillChangeRecordset WillMove Command Recordset FetchProgress Progress MaxProgress precordset Progress MaxProgress precordset Recordset FetchComplete perror(error ) adstatus( 6-21) precordset( ) << >> ADO ADO Will WillMove MoveComplete Complete adstatus adreason precordset