穨ac3-6.PDF

Transcription

1 6-1 ADO ADO Access 2000 ADO ADOX ADO ADO Connection Command Recordset Parameter Field Error 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

2 << >> ADO Access 2000 ADO Access Visual Basic ADO 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

3 OLE DB Provider for ODBC MSDASQL ODBC MSDAORA MSIDXS OLE DB Persistence Provider OLE DB Remoting Provider MSPersist MS Remote Provider Access ( )

4 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

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

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

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

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

9 ADO Recordset Command rs Open Command SQL Command SQL Command Recordset Command Recordset SQL Recordset Command Execute Parameter Command Command Access 2000 ADO Access 2000 Access 2000 ADO ADO Access ( ) DAO ADO ADO ADO Access ADO (ADP) 6-9

10 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

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

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

13 _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

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

15 Cancel State Cancel State ADO Open 1009 Close Connection Connection Commad Recordset Connection Connection Execute SQL SQL SQL 6-2 adcmdtext adcmdtable SQL SQL Table SELECT

16 adcmdtabledirect SQL Table adcmdstoredproc adcmdunknown adexecuteasync adfetchasync SQL SQL CacheSize 6-2 CacheSize Recordset 100 CacheSize Execute SQL SQL Recordset Execute SQL Execute Connection Execute rs Recordset ( ) Recordset Open ADO Open

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

18 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

19 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 adxactserializable IsolationLevel BeginTrans IsolationLevel BeginTrans Mode Connection 6-3 admodeunknown 0( )

20 admoderead 1 admodewrite 2 admodereadwrite 3 admodesharedenyrea d admodesharedenywri te admodeshareexclusiv e admodesharedenynon e 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

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

22 adasyncexecute 1011 Do State Timer( ) h( ) CancelBatch 6-4 adaffectcurrent 1 adaffectgroup 2 Filter adaffectall 4 ()

23 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

24 (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 Recordset (adopenforwardonly) ( 1 2) ( 2 1) MoveFirst MoveLast MoveNext MovePrevious Recordset MoveNext Do NextRecordset Recordset SQL select * from Customers;select * from Orders SELECT ; ADO SELECT NextRecordset Recordset SQL SELECT

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

26 Access Access SELECT SQL SQL Server Open Recordset Recordset.Open [ ][,Connection ][, ][, ] Recordset Source ActiveConnection CursorType LockType Command CommandType Recordset 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

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

28 6-21 Update 6-21 Ch6\6-All\ADOSam.mdb _ SQL Server 6-11 Northind Customer CustomerID CompanyName Update Update Array Array d d1 UpdateBatch AbsolutePage adposunknown -1 Recordset

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

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

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

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

33 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 Recordset

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

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

36 6-25 Attributes 6-25 Re cordset Attributes_fd Attributes 1009 Attributes_fd 1010 Attributes X1 X

37 Auttributes cd ( ) DefinedSize Name Field NumericScale Field OriginalValue Field Precision Field Type Field SQL Server 7.0 ad UnderlyingValue Field Resync OriginalValue Value Field << >>

38 Field Value SQL Server Recordset Fields Access 2000 SQL Server

39 6-4 Command Parameter Command Access Parameter 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 Parameter Direction Access 2000

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

41 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( ) Parameter Command Command Parameters AppendChunk Parameter Attributes adparamlong AppendChunk Parameter. AppendChunk Data GetChunk AppendChunk Parameter AppendChunk Attributes Parameter

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

43 6-5 Error( ) Property( ) Error ADO Connection Error 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

44 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 Attributes Property

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

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

47 Class_Initialize cusquery DB ADO Connection Recordset Public WithEvents DB cusquery DB InfoMessage WithEvents DB cusquery

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

49 TransactionLevel Transaction 1 2 perror Error adstatus 6-21 adstatuscancel 4 adstatuscantdeny 3 adstatuserrorsoccured 2 adstatusok 1 adstatusunwantedevent 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

50 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

51 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 RecordChangeComplete adreason crecords perror Error adstatus 2 adstatus 6-21 precordset EndofRecordset WillChangeRecord RecordChangeComplete WillChangeRecord RecordChangeComplete

52 RecordChangeComplete perror MoveComplete adreason adstatus precordset adreason 6-23 adrsnmovefirst 12 adrsnmovelast 15 adrsnmovenext 13 adrsnmoveprevious 14 adrsnmove 10 Move adrsnrequery 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 RecordsetChangeComplete adreason WillChangeRecordset adreason adstatus precordset adreason 6-24 adstatus precordset 6-21 EndofRecordset

53 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