4-1 VBA Access 4-1-1 Access 2000 4-1 4-1 Access 2000 4-1 Visual Basic Access 2000 ( ADO DAO ) Access 2000 VBA Office Access VBA Access 8.0(97 ) DAO Access 2000 DAO ADO 2.1 OLE Automation ADO 2.1 DAO ADO Access 2000 DAO
4-1 DAO 3.6 Access 2000 DAO Set m=currentdb.openrecordset( Select * From ) CurrentDB DAO DAO Set m=dbengine.workspaces(0).opendatabase( c:\test.mdb ) 4-1 DAO 3.6 DBEngine DAO Access 2000 ADO << >> 4-1 4-1 4-1-2 4-1 Access 4-2 4-2 4-2 ADODB.Recordset Recordset 4-2 4-3
4-3 4-3 4-1 4-1 << >> 4-1 MDB MDB 4-1 4-1 MDB Ch4\4-1\ Customer.mdb Function.mdb 3-24( ) Customer.mdb 4-4
4-4 (Load ) (Close ) VBA References Application Application.References Application Access Load CurrentProject.Path \ p lk AddFromFile Customer.mdb Function.mdb Remove Reference Function 4-1-3 Access 2000 Access 2000 DAO ADO ACCESS Access DAO ADO ACCESS ( 4-1 Access 9.0 ) ACCESS Visual Basic Excel VBA ADO DAO Access Access ACCESS Access ACCESS Visual Basic Excel VBA Access
DAO ADO Access << >> VBA Office VBA ( Access) ( Access ) VBA Form ACCESS Access 9.0 Access 4-4 Application.Quit Application ACCESS ACCESS Applicaion VBA Office Application Word VBA Application Word Access VBA Application Access Excel PowerPoint Access Application X=Application.Forms( )![ ] X X=Application.Tables( )![ ] Tables Queries DAO ADO << >> Application
DAO 3.6 ADO 2.1 Access 2000 ADOX 4-1 DAO ADO ADOX X X X X(ADP) X X X X(MDB) X X X X(MDB) / X X X X(MDB) Jet 4.0 Decimal X X SQL X X(MDB) X(MDB) / X / X X X X X X X X 4-1 ADO ADOX DAO
ADO ( ) ADOX ACCESS 2000 ADO DAO DAO ACCESS 2000 Recordset Recordset DAO Recordset Recordset DAO ADO DAO Set m=currentdb.openrecordset( Select * From ) m.addnew m.update CurrentDB DAO ADO Set m = New ADODB.Recordset m.open "SELECT * FROM ", CurrentProject.Connection, adopenkeyset, adlockoptimistic m.addnew m.update DAO ADO DAO OpenRecordset Set m=currentdb.openrecordset( Select * From, dbopendynaset,, dboptimistic) DAO Recordset DAO Recordset DAO ADO DAO ADO ADO adopenkeyset adlockoptimistic
<< >> DAO ADO ADO SQL Server DAO ADO Access 2000 1. ACCESS Application AfterDelConfirm AfterUpdate 2. DAO ADO Private Sub _AfterUpdate() If IsNull(Me![ ])=False Then End If End Sub Me![ ]=Me![ ]*Me![ ]
IsNull Me ( Me.AllowEdits) Private Sub _AfterUpdate() If IsNull(Me![ ])=False Then End If End Sub Forms![ ]![ ]=Me![ ]*Me![ ] Forms![ ]![ ] Me DAO ADO Private Sub _AfterUpdate() If IsNull(Me![ ])=False Then End If Set m=currentdb.openrecordset( Select * From Where Like & Me![ ] & ) If m.recordcount > 0 Then End If End Sub m.edit m( )=Me![ ] m.update Access OpenRecordset SQL
SQL SQL v Me![ ] SQL SQL << >> OpenRecordset Access ACCESS 3. ( ) ( m.update) Requery Me.Requery ( ) Access Requery Requery << >> Access
4-2 Access VBA VBA VBA 4-2-1 Docmd Access VBA Docmd Docmd. 4-5 4-5 Docmd Docmd VBA Application 4-5 4-6 4-6 VBA
4-6 Close VBA 4-6 Close << >> VBA Docmd 4-2-2 VBA Ch4\4-2\ToXLS.mdb Excel 4-7 4-7 4-7 1003 1007 1010 XLS Excel 1010 TransferSpreadsheet ( ) ( ) InputBox
(v) << >> VBA 4-7 acexport TransferSpreadsheet acexport Visual Basic 4-8 4-8 4-8 acexport Enter acexport 1 1 4-7 acexport VBA 4-7 VBA 4-2-3 VBA VBA
VBA VBA? VBA VBA Open DeleteObject Close Save OpenForm OpenReport Open 4-7 1005 OpenTable _ Docmd.Close Docmd.Close acform, Access Save Ch4\4-2\ToXLS.mdb 4-9
4-9 4-9 Tax 4-10 4-10
4-10 1001 Tax acviewdesign 1003 chlocation Close acsaveyes 1063 1061 1062 1063 OpenReport acviewpreview << >> ( 1001 1063) 1001 1063 acviewnormal Access Tax 4-10 TransferDatabase TransferSpreadsheet TransferText Ch4\4-2\Output.mdb 4-11
4-11 4-11 4-12 4-12 4-12 1005 1005 1008 _ 4-11 _ 1009 1013
CurrentProject.Path / C:\ C:\Test 1014 1020 4-11 1 2 XLS 3 DBF << >> DBF DBF DBF Access DBF DBF DBF acimport OutputTo txt xls asp rtf html Maximize Minimize Docmd.Minimize VB Access MoveSize twips 567 twips Access RunCommand Access 4-13
4-13 RunCommand 4-13 Runcommand Access accmd accmdselectallrecords << >> RunCommand 4-13 accmdselectallrecords Access 4-2-4 Docmd VBA Docmd 4-2 VBA VBA Docmd.RunMacro VBA VBA Docmd.MsgBox... MsgBox VBA MsgBox RunApp SendKeys RunCode MsgBox Shell SendKeys
SetValue AddMenu Let D=Now() D StopAllMacros StopMacro 4-2 Docmd << >> Access VBA VBA Docmd
4-3 Current Access 2000 VBA Application Current CurrentData CurrentDb CurrentObjectName CurrentObjectType CurrentProject CurrentUser Access 4-3 Current 4-3 CurrentObjectName CurrentObjectType Access Then If Application.CurrentProject.AllForms( ).IsLoaded=True End If Docmd.Close acform, CurrentProject 4-3-1 CurrentData Table View Query StoreProcedure DatabaseDiagram View StoreProcedure DatabaseDiagram Ch4\4-3\Currernt.adp 4-14
4-14 SQL Server 4-14 SQL Server Ch4\4-3\Currernt.adp 4-15 4-15 4-15 (AfterUpdate) 4-16
4-16 CurrentData 4-16 CurrentData 1002 1004 k 1006 k ; 1007 k RowSource( ) Customers;Orders;OrderDetail ; << >> CurrentData AllTables AllQueries AllViews AllStoreProcedures AllDatabaseDiagrams 4-16 AllTables CurrentData (mdb) AllTables AllQueries (adp) AllTables AllViews AllStoreProcedures AllDatabaseDiagrams CurrentData VBA
CurrentData 4-16 1003 Name Table Table ( ) FullName IsLoaded Parent Properties Type FullName CurrentData Type 0 1 IsLoaded 4-3-2 CurrentProject CurrentProject CurrentData CurrentProject AllForms AllReports AllDataAccessPages AllMacros AllModules FullName IsLoaded Parent Properties Name Type CurrentProject 4-12 1009 CurrentProject.Path Access 2000 ADO Access CurrentProject Connection Connection Ch4\4-3\Tax.mdb Tax Tax Tax Tax Ch4\4-3\Tax.mdb Tax 4-17
4-17 4-17 Tax ( ) 4-18 4-18 4-18 1009 Tax CurrentProject.Connection ADO Execute 1010 1012 CurrentDB Tax ( ) 1011 1012 1013 1019 4-17 X SQL 4-19
4-19 ADO 4-19 1034 g1 ADODB.Recordset (Dim) Set New g1 ADODB.Recordset 1038 1040 ADO g1.open SQL CurrentProject.Connection 1043 g1 printid << >> CurrentProject ADO ADO ( 1038 1040) ADO DAO Tax CurrentProject ADO ADO CurrentProject.Connection ADO
4-3-3 CurrentDB Access 2000 CurrentDB Application Application.CurrentDB Application 4-18 1010 CurrentDB DAO CurrentDB CurrentProject CurrentDB DAO Ch4\4-3\Tax1.mdb DAO 4-20 4-20 DAO 4-20 4-18 19!009 4-20 CurrentDB.Execute SQL 4-20 1036 1038 Set g1=currentdb.openrecordset g1 DAO OpenRecordset 1042 DAO Edit 4-19 ADO Edit
<< >> DAO ADO DAO ADO DAO CurrentDB Access 2000 ADO DAO DAO CurrentProject.Connection ADO ADO DAO << >> Current Current
4-4 4-4-1 ADO ADO Access 2000 Connection Access 2000 Connection ADO 4-19 Connection Connection Recordset Connection Dim ccon As ADODB.Connection Set ccon=new ADODB.Connection ccon.open CurrentProject.Connection Connection ccon Dim re As ADODB.Recordset Set re=new ADODB.Recordset re.open ccon, select * from 1 ADO Connection 4-19 CurrentProject.Connection Dim ccon As ADODB.Connection Set ccon = New ADODB.Connection ccon.open "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=C:\Test.mdb " Open ADO.Connection CurrentProject.Connection.ConnectionString Provider Data Source ( ID ) User ID Password ADO ADO 4-21 4-21 ADO 4-21 g1 ADODB.Recordset SQL (CursorType) (LockType) 4-4 CursorType adopenforwardonly 0 MoveNext ( ) adopenkeyset 1 adopendynamic 2 adopenstatic 3
LockType adlockreadonly 1 ( ) adlockpessimistic 2 adlockoptimistic 3 adlockbatchoptimistic 4 4-4 ADO ADO 4-4-2 DAO DAO 4-20 1038 CurrentDB DAO CurrentDB DAO Access 2000 DAO CurrentDB DAO Access 2000 ADO DAO DAO Access DAO Set m=currentdb.openrecordset( Select * From 1 ) Set db=dbengine.workspaces(0).opendatabase( C:\Test.mdb ) Set m=db.openrecordset( Select * From 1 ) Visual Basic DAO 3.0 Object Library
<< >> DAO 4-22 4-22 DAO 4-22 dbopendynaset dboptimistic 4-4 << >> ADO DAO DAO ADO 4-4-3 Access 4-20 1010 1012 DAO 4-23
4-23 DAO ADO 4-23 Ch4\4-3\Tax.mdb Module1 F5 ADO DAO DAO DAO Access ADO Access 2000 ADO Command Parameter 4-23 1001 1004 1007 Parameter CommandType CommandText SQL 1008( ) 1009( Command ) DAO ADO ADO Command n2 ADO n.execute DAO n.openrecordset DAO Execute CurrentDB.Execute Delete * From 1 1 SQL
Execute ADO Execute << >> ADO DAO DAO DAO ADO
4-5 ADO DAO ADO DAO Access 2000 Acces 2000 4-5-1 DAO DAO ADO 4-19 1038 DAO ADO ADO ADO DAO ADO 4-21 4-5-2 Edit DAO ADO Edit 4-24 4-24
4-24 Edit ADO Edit DAO Edit DAO ADO 4-5-3 Database Access 97 Dim db As Database Set db=currentdb Access 2000 Dim db As Database Access 2000 DAO Database DAO ADO Dim db As Database CurrentDB Database 4-5-4 Form Recordset Access 2000 Form Recordset RecordsetClone Access 97 DAO ADO Ch4\4-3\Tax1.mdb 4-25
4-25 4-25 4-26 4-26 Recordset 4-26 ModiPrint ModiPrint Recordset
Edit Recordset DAO << >> Recordset RecordsetClone Recordset RecordsetClone Requery 4-26 Me![tax_ ].Form.Recordset Me.Recordset << >> ADO DAO DAO ADO Access 2000 DAO ADO Access ADO
4-6 SQL SQL ADO DAO SQL 4-20 1036 4-19 1038 DAO ADO 4-6-1 SQL SQL Access SQL SQL SQL SQL 4-27 4-27 SQL 4-27 SQL SQL SQL SQL Access 4-27 SQL SQL Access SQL
4-6-2 SQL 4-20 1036 4-19 1038 SQL SQL SQL SQL SQL Set m=currentdb.openrecordset( Select * From 1 ) SQL * 1 SQL Set m=currentdb.openrecordset( Select * From 1 Where 1 Like & Chr(39) & Me![ 1] & Chr(39)) Set m=currentdb.openrecordset( Select * From 1 Where 1 = & Me![ 1]) Set m=currentdb.openrecordset( Select * From 1 Where 1 Between # & Me![ 1] & # and # & Me![ 2] & # ) > < = Between And # Access Like Not Like Chr(39)
And Or Access Set m=currentdb.openrecordset( Select * From 1 Where 1 Like & Chr(39) & Me![ 1] & Chr(39) & and 2 = & Me![ 2]) Set m=currentdb.openrecordset( Select * From 1 Where ( 1 Like & Chr(39) & Me![ 1] & Chr(39) & and 2 = & Me![ 2] & ) Or ( 3 = & Me![ 3] & ) ) And Or And Or And Or << >> Me Set m=currentdb.openrecordset( Select * From 1 Where 1 Between # & Date() & # and # & Date()-30 & # ) Date SQL & SQL << >> Select SQL Delete Insert Update SQL