0LJUDWLQJIURP'$2WR$'2

8VLQJ$'2ZLWKWKH-HW3URYLGHU $O\VVD+HQU\ 0DUFK

,QWURGXFWLRQ

7KLVGRFXPHQWLVGHVLJQHGDVDJXLGHWRSHUIRUPLQJFRPPRQ0LFURVRIWŠ'DWD$FFHVV 2EMHFWV'$2 SURJUDPPLQJWDVNVZLWKHTXLYDOHQW0LFURVRIW$FWLYH;Š'DWD2EMHFWV $'2 FRGH,WGHWDLOVWKHPDSSLQJEHWZHHQ'$2DQG$'2REMHFWVSURSHUWLHVDQG PHWKRGV,WDOVRKLJKOLJKWVDUHDVZKHUHWKHUHDUHIXQFWLRQDORUVHPDQWLFGLIIHUHQFHV EHWZHHQVLPLODUO\QDPHGPHWKRGVRUSURSHUWLHV

7KLVGRFXPHQWLVDOVRDJXLGHIRUWKRVHZKRDUHZULWLQJQHZDSSOLFDWLRQVXVLQJ$'2 ZLWKWKH2/('%3URYLGHUIRU0LFURVRIW-HW-HW3URYLGHU ,WGHVFULEHVPDQ\IHDWXUHVRI WKH-HW3URYLGHUDQGGHPRQVWUDWHVKRZWRXVHWKHPZLWK$'2%HFDXVHWKH$'2 GRFXPHQWDWLRQZDVGHVLJQHGWREHSURYLGHUQHXWUDOLWODFNVPXFKRIWKLVLQIRUPDWLRQ

7KLVGRFXPHQWGRHVQRWDWWHPSWWRSURYLGHLQGHSWKGHWDLORQSDUWLFXODUREMHFWV SURSHUWLHVRUPHWKRGV5HIHUWRWKHRQOLQHGRFXPHQWDWLRQSURYLGHGZLWK'$2DQG$'2 IRUVSHFLILFGHWDLOVRQDSDUWLFXODULWHP 7KUHHGLVWLQFWREMHFWPRGHOVLQ$'2WRJHWKHUSURYLGHWKHIXQFWLRQDOLW\IRXQGLQ'$2 7KHVHWKUHHPRGHOVDUH$'20LFURVRIW$'2([WHQVLRQVIRU''/DQG6HFXULW\$'2;  DQG0LFURVRIW-HWDQG5HSOLFDWLRQ2EMHFWV-52 7KHIXQFWLRQDOLW\RI'$2ZDVGLYLGHG DPRQJWKHVHWKUHHPRGHOVEHFDXVHPDQ\DSSOLFDWLRQVZLOOQHHGMXVWRQHRIWKHVH VXEVHWVRIIXQFWLRQDOLW\%\VSOLWWLQJWKHIXQFWLRQDOLW\RXWDSSOLFDWLRQVGRQRWQHHGWR LQFXUWKHRYHUKHDGRIORDGLQJDGGLWLRQDOLQIRUPDWLRQLQWRPHPRU\XQQHFHVVDULO\7KH IROORZLQJVHFWLRQVSURYLGHDQRYHUYLHZRIWKHVHWKUHHREMHFWPRGHOV

$'2'DWD0DQLSXODWLRQ

$'2HQDEOHV\RXUFOLHQWDSSOLFDWLRQVWRDFFHVVDQGPDQLSXODWHGDWDWKURXJKDQ\2/( '%SURYLGHU$'2FRQWDLQVREMHFWVIRUFRQQHFWLQJWRDGDWDVRXUFHDQGUHDGLQJ DGGLQJXSGDWLQJRUGHOHWLQJGDWD ^EPF$'22EMHFW0RGHOEPS`

$'2;'DWD'HILQLWLRQDQG6HFXULW\ 7KH$'2;PRGHOFRQWDLQVREMHFWVIRUGDWDGHILQLWLRQVXFKDVWDEOHVYLHZVDQG LQGH[HV DQGFUHDWLQJDQGPRGLI\LQJXVHUVDQGJURXSV:LWK$'2;DQDGPLQLVWUDWRU FDQFRQWUROGDWDEDVHVFKHPDDQGJUDQWDQGUHYRNHSHUPLVVLRQVRQREMHFWVWRXVHUV DQGJURXSV

 0LJUDWLQJ'$2WR$'2  0LJUDWLQJIURP'$2WR$'2

:LWK$'2DQG$'2;WKH&RQQHFWLRQREMHFWGHILQHVDVHVVLRQIRUDXVHUIRUDGDWD VRXUFH7KH&DWDORJREMHFWLVWKHFRQWDLQHUIRUWKHGDWDGHILQLWLRQFROOHFWLRQV7DEOHV 3URFHGXUHV DQG9LHZV DQGWKHVHFXULW\FROOHFWLRQV8VHUVDQG*URXSV (DFK &DWDORJREMHFWLVDVVRFLDWHGZLWKRQO\RQH&RQQHFWLRQWRDQXQGHUO\LQJGDWDVRXUFH

7KH$'2;PRGHOGLIIHUVVRPHZKDWIURPWKH'$2PRGHO'$2KDVD:RUNVSDFH REMHFWWKDWGHILQHVDVHVVLRQIRUDXVHUEXWGRHVQRWGHILQHWKHGDWDVRXUFH7KH :RUNVSDFHREMHFWLVDOVRWKHFRQWDLQHUIRUWKH8VHUVDQG*URXSVFROOHFWLRQV$ :RUNVSDFHFDQEHFUHDWHGDQGVHFXULW\LQIRUPDWLRQFDQEHUHWULHYHGRUPRGLILHG ZLWKRXWRSHQLQJDGDWDEDVH ^EPF$'2;2EMHFW0RGHOEPS`

(DFKRIWKH7DEOH ,QGH[ DQG&ROXPQREMHFWVDOVRKDVDVWDQGDUG$'23URSHUWLHV FROOHFWLRQ ^EPF$'2;2EMHFW0RGHOEPS`

-525HSOLFDWLRQ

7KH-52PRGHOFRQWDLQVREMHFWVSURSHUWLHVDQGPHWKRGVIRUFUHDWLQJPRGLI\LQJDQG V\QFKURQL]LQJUHSOLFDV,WLVGHVLJQHGVSHFLILFDOO\IRUXVHZLWKWKH-HW3URYLGHU8QOLNH $'2DQG$'2;-52FDQQRWEHXVHGZLWKGDWDVRXUFHVRWKHUWKDQ0LFURVRIW-HW GDWDEDVHV

7KHSULPDU\REMHFWLQWKH-52PRGHOLVWKH5HSOLFDREMHFW7KH5HSOLFDREMHFWLVXVHG WRFUHDWHQHZUHSOLFDVWRUHWULHYHDQGPRGLI\SURSHUWLHVRIDQH[LVWLQJUHSOLFDDQGWR V\QFKURQL]HFKDQJHVZLWKRWKHUUHSOLFDV7KLVGLIIHUVIURP'$2LQZKLFKWKH'DWDEDVH REMHFWLVXVHGIRUWKHVHWDVNV -52DOVRLQFOXGHVD-HW(QJLQHREMHFWIRUWZRVSHFLILF0LFURVRIW-HWGDWDEDVHHQJLQH IHDWXUHVFRPSDFWLQJWKHGDWDEDVHDQGUHIUHVKLQJGDWDIURPWKHPHPRU\FDFKH ^EPF-522EMHFW0RGHOEPS`

*HWWLQJ6WDUWHG

7RUXQWKHFRGHH[DPSOHVLQWKLVGRFXPHQW\RXQHHGUHIHUHQFHVWRWKH$'2$'2; DQG-52W\SHOLEUDULHVLQ\RXUGDWDEDVHRUSURMHFW%\GHIDXOWQHZ0LFURVRIW$FFHVV GDWDEDVHVKDYHDUHIHUHQFHWR$'2+RZHYHUWRUXQWKHVHVDPSOHV\RX OOQHHG WRDGGUHIHUHQFHVWR$'2;DQG-52,I\RXFRQYHUWHGDQH[LVWLQJGDWDEDVHWR$FFHVV RUDUHSURJUDPPLQJLQ0LFURVRIW9LVXDO%DVLFŠRUVRPHRWKHUDSSOLFDWLRQ\RX ZLOOQHHGWRLQFOXGHDOORIWKHUHIHUHQFHV\RXUVHOI 7RDGGWKHVHUHIHUHQFHVLQ$FFHVV  2SHQDPRGXOH

 )URPWKH7RROVPHQXVHOHFW5HIHUHQFHV«

 )URPWKHOLVWVHOHFW0LFURVRIW$FWLYH;'DWD2EMHFWV/LEUDU\

 )URPWKHOLVWVHOHFW0LFURVRIW$'2([WIRU''/DQG6HFXULW\

 )URPWKHOLVWVHOHFW0LFURVRIW-HWDQG5HSOLFDWLRQ2EMHFWV/LEUDU\

 &OLFN2.

7RDGGWKHVHUHIHUHQFHVLQ9LVXDO%DVLF  2SHQDSURMHFW

 )URPWKH3URMHFWPHQXVHOHFW5HIHUHQFHV«

 )URPWKHOLVWVHOHFW0LFURVRIW$FWLYH;'DWD2EMHFWV/LEUDU\

 )URPWKHOLVWVHOHFW0LFURVRIW$'2([WIRU''/DQG6HFXULW\

 )URPWKHOLVWVHOHFW0LFURVRIW-HWDQG5HSOLFDWLRQ2EMHFWV/LEUDU\

 &OLFN2.

,I\RXLQFOXGHUHIHUHQFHVWRERWK$'2DQG'$2LQWKHVDPHSURMHFW\RXQHHGWR H[SOLFLWO\VSHFLI\ZKLFKOLEUDU\WRXVHZKHQGHFODULQJREMHFWVEHFDXVH'$2DQG$'2 LQFOXGHVHYHUDOREMHFWVZLWKWKHVDPHQDPHV)RUH[DPSOHERWKPRGHOVLQFOXGHD 5HFRUGVHWREMHFWVRWKHIROORZLQJFRGHLVDPELJXRXV Dim rst as Recordset 7RVSHFLI\ZKLFKREMHFWPRGHO\RXZDQWWRXVHLQFOXGHDTXDOLILHUDVVKRZQ Dim rstADO As ADODB.Recordset Dim rstDAO As DAO.Recordset ,IWKHTXDOLILHULVRPLWWHG9LVXDO%DVLFIRU$SSOLFDWLRQVZLOOFKRRVHWKHREMHFWIURPWKH PRGHOWKDWLVUHIHUHQFHGILUVW6RLI\RXUOLVWRIUHIHUHQFHVZHUHRUGHUHGDVIROORZVLQ WKH5HIHUHQFHVGLDORJER[DQREMHFWGHFODUHGDV5HFRUGVHWZLWKQRTXDOLILHUZRXOGEH D '$25HFRUGVHW Visual Basic For Applications Microsoft DAO 3.6 Object Library Microsoft ActiveX Data Objects 2.1 Library Microsoft ADO Ext. 2.1 for DDL and Security Microsoft Jet and Replication Objects 2.1 Library

2SHQLQJD'DWDEDVH

*HQHUDOO\RQHRIWKHILUVWVWHSVLQZULWLQJDQDSSOLFDWLRQWRDFFHVVGDWDLVWRRSHQWKH GDWDVRXUFH:KHQXVLQJWKH0LFURVRIW-HWGDWDEDVHHQJLQH\RXFDQRSHQ0LFURVRIW-HW GDWDEDVHVRWKHUH[WHUQDOGDWDVRXUFHVVXFKDV0LFURVRIW([FHO3DUDGR[DQGG%$6( ZLWK0LFURVRIW-HW V,6$0FRPSRQHQWVDQG2'%&GDWDVRXUFHV

0LFURVRIW-HW'DWDEDVHV

7KH-HW3URYLGHUFDQRSHQ0LFURVRIW-HWGDWDEDVHVDVZHOODVGDWDEDVHVFUHDWHG ZLWKSUHYLRXVYHUVLRQVRIWKH-HWGDWDEDVHHQJLQH

7KHIROORZLQJFRGHGHPRQVWUDWHVKRZWRRSHQD0LFURVRIW-HWGDWDEDVHIRUVKDUHG XSGDWDEOHDFFHVV7KHQWKHFRGHLPPHGLDWHO\FORVHVWKHGDWDEDVHEHFDXVHWKLVFRGHLV IRUGHPRQVWUDWLRQSXUSRVHV '$2

Sub DAOOpenJetDatabase() Dim db

As DAO.Database

Set db = DBEngine.OpenDatabase("C:\Nwind.mdb") db.Close End Sub $'2 Sub ADOOpenJetDatabase() Dim cnn

As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb;" cnn.Close End Sub 7KHVHWZRFRGHOLVWLQJVIRURSHQLQJDGDWDEDVHORRNVRPHZKDWGLIIHUHQWEXWDUHQRWDOO WKDWGLVVLPLODU$VLGHIURPWKHIDFWWKDWWKHREMHFWVKDYHGLIIHUHQWQDPHVWKHPDMRU GLIIHUHQFHLVWKHIRUPDWRIWKHVWULQJSDVVHGLQWRWKHRSHQPHWKRG 7KH$'2FRQQHFWLRQVWULQJLQWKLVH[DPSOHKDVWZRSDUWVWKHSURYLGHUWDJDQGWKH GDWDVRXUFHWDJ7KHSURYLGHUWDJLQGLFDWHVZKLFK2/('%SURYLGHUWRXVHDQGWKH GDWDVRXUFHWDJLQGLFDWHVZKLFKGDWDEDVHWRRSHQ:LWK'$2LWLVDVVXPHGWKDW\RX ZDQWWRXVH0LFURVRIW-HWZKHUHDVZLWK$'2\RXPXVWH[SOLFLWO\VSHFLI\WKDW\RXZDQW WRXVH0LFURVRIW-HW %\GHIDXOWERWK'$2DQG$'2RSHQDGDWDEDVHIRUVKDUHGXSGDWDEOHDFFHVVZKHQ XVLQJWKH-HW3URYLGHU+RZHYHUWKHUHPD\EHWLPHVZKHQ\RXZDQWWRRSHQWKH GDWDEDVHH[FOXVLYHO\RULQUHDGRQO\PRGH 7KHIROORZLQJFRGHOLVWLQJVVKRZKRZWRRSHQDQGWKHQFORVH DVKDUHGUHDGRQO\ GDWDEDVHXVLQJLQ'$2DQG$'2 '$2

Sub DAOOpenJetDatabaseReadOnly() Dim db

As DAO.Database

’ Open shared, read-only. Set db = DBEngine.OpenDatabase("C:\nwind.mdb", False, True) db.Close End Sub $'2

Sub ADOOpenJetDatabaseReadOnly() Dim cnn

As New ADODB.Connection

’ Open shared, read-only cnn.Mode = adModeRead cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;" cnn.Close End Sub ,QWKH'$2OLVWLQJWKHVHFRQGWZRSDUDPHWHUVWRWKH2SHQ'DWDEDVHPHWKRGLQGLFDWH H[FOXVLYHDQGUHDGRQO\DFFHVVUHVSHFWLYHO\,QWKH$'2OLVWLQJWKH&RQQHFWLRQREMHF 0RGHSURSHUW\LVVHWWRWKHUHDGRQO\FRQVWDQWDG0RGH5HDG %\GHIDXOW$'2 FRQQHFWLRQVDUHRSHQHGIRUVKDUHGXSGDWDEOHDFFHVVXQOHVVDQRWKHUPRGHLVVHWIRU H[DPSOHDG0RGH6KDUH([FOXVLYH 

$OWHUQDWLYHO\WKH$'2OLVWLQJFRXOGKDYHEHHQZULWWHQLQDVLQJOHOLQHRIFRGHDV IROORZV Sub OpenJetDatabaseExclusive() Dim cnn

As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\nwind.mdb;Mode=" & adModeRead cnn.Close End Sub ,QWKLVOLVWLQJWKH0RGHSURSHUW\ZDVVSHFLILHGDVDSDUWRIWKHFRQQHFWLRQVWULQJWR WKH2SHQPHWKRGUDWKHUWKDQDVDSURSHUW\RIWKH&RQQHFWLRQREMHFW,Q$'2\RX FDQVHWFRQQHFWLRQSURSHUWLHVDVDSURSHUW\RUVWULQJWKHPWRJHWKHUZLWKRWKHU SURSHUWLHVWRFUHDWHWKHFRQQHFWLRQVWULQJ(YHQSURYLGHUVSHFLILFSURSHUWLHVSUHIL[HG E\-HW2/('%IRU-HWVSHFLILFSURSHUWLHV FDQEHVHWDVSDUWRIWKHFRQQHFWLRQVWULQJ RUZLWKWKH&RQQHFWLRQREMHFW V3URSHUWLHVFROOHFWLRQ)RUDGHVFULSWLRQRIWKH DYDLODEOHSURSHUWLHVVHH$SSHQGL[%3URSHUWLHV5HIHUHQFHODWHULQWKLVGRFXPHQW 7KH0LFURVRIW-HWGDWDEDVHHQJLQHH[SRVHVDQXPEHURIVHWWDEOHRSWLRQVWKDWZLOO GLFWDWHKRZWKHHQJLQHZLOOEHKDYH7KHVHRSWLRQVRIWHQKDYHDGLUHFWLPSDFWRQ SHUIRUPDQFH%\GHIDXOWZKHQWKH-HWGDWDEDVHHQJLQHLVLQLWLDOL]HGLWXVHVWKHYDOXHV VHWLQWKH:LQGRZVUHJLVWU\XQGHUWKH ?+.(@  DURXQGWKHQDPH

([HFXWLQJD3DUDPHWHUL]HG6WRUHG4XHU\ $ SDUDPHWHUL]HGVWRUHGTXHU\LVDQ64/VWDWHPHQWWKDWKDVEHHQVDYHGLQWKH GDWDEDVHDQGUHTXLUHVWKDWDGGLWLRQDOYDULDEOHLQIRUPDWLRQEHVSHFLILHGLQRUGHUWR H[HFXWH7KHFRGHEHORZVKRZVKRZWRH[HFXWHVXFKDTXHU\ '$2

Sub DAOExecuteParamQuery() Dim db Dim qdf

As DAO.Database As DAO.QueryDef

Dim rst

As DAO.Recordset

Dim fld

As DAO.Field

’ Open the database Set db = DBEngine.OpenDatabase("C:\nwind.mdb") ’ Get the QueryDef from the QueryDefs collection Set qdf = db.QueryDefs("Sales by Year") ’ Specify the parameter values qdf.Parameters("Forms!Sales by Year Dialog!BeginningDate") _ = #8/1/1993# qdf.Parameters("Forms!Sales by Year Dialog!EndingDate") = #8/31/1993# ’ Open the Recordset Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly) ’ Display the records in the debug window While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Wend ’Close the recordset rst.Close

End Sub $'2 Sub ADOExecuteParamQuery() Dim cnn

As New ADODB.Connection

Dim cat Dim cmd

As New ADOX.Catalog As ADODB.Command

Dim rst

As New ADODB.Recordset

Dim fld

As ADODB.Field

’ Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;" ’ Open the catalog cat.ActiveConnection = cnn ’ Get the Command object from the Procedure Set cmd = cat.Procedures("Sales by Year").Command ’ Specify the parameter values cmd.Parameters("Forms!Sales by Year Dialog!BeginningDate") _ = #8/1/1993# cmd.Parameters("Forms!Sales by Year Dialog!EndingDate") = #8/31/1993# ’ Open the recordset rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc ’ Display the records in the debug window While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Wend ’Close the recordset rst.Close End Sub

$OWHUQDWLYHO\WKH$'2H[DPSOHFRXOGEHZULWWHQPRUHFRQFLVHO\E\VSHFLI\LQJWKH SDUDPHWHUYDOXHVXVLQJWKH3DUDPHWHUVSDUDPHWHUZLWKWKH&RPPDQGREMHFW V ([HFXWHPHWKRG7KHIROORZLQJOLQHVRIFRGH ’ Specify the parameter values cmd.Parameters("Forms![Sales by Year Dialog]!BeginningDate") = & _ #8/1/93# cmd.Parameters("Forms![Sales by Year Dialog]!EndingDate") = #8/31/93# ’ Open the recordset rst.Open cmd, , adOpenForwardOnly, adLockReadOnly FRXOGEHUHSODFHGE\WKHVLQJOHOLQH ’ Execute the Command, passing in the values for the parameters Set rst = cmd.Execute(, Array(#8/1/93#, #8/31/93#)) ,QRQHPRUHYDULDWLRQRIWKH$'2FRGHWRH[HFXWHDSDUDPHWHUL]HGTXHU\WKHH[DPSOH FRXOGEHUHZULWWHQWRQRWXVHDQ\$'2;FRGH Sub ADOExecuteParamQuery2() Dim cnn

As New ADODB.Connection

Dim cmd Dim rst

As New ADODB.Command As New ADODB.Recordset

Dim fld

As ADODB.Field

’ Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;" ’ Create the command Set cmd.ActiveConnection = cnn cmd.CommandText = "[Sales by Year]" ’ Execute the Command, passing in the values for the parameters Set rst = cmd.Execute(, Array(#8/1/93#, #8/31/93#), adCmdStoredProc) ’ Display the records in the debug window While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Wend

’Close the recordset rst.Close End Sub

([HFXWLQJ%XON2SHUDWLRQV

7KH$'2&RPPDQGREMHFW V([HFXWHPHWKRGFDQEHXVHGIRUURZUHWXUQLQJTXHULHV DVVKRZQLQWKHSUHYLRXVVHFWLRQDVZHOODVIRUQRQURZUHWXUQLQJTXHULHV²DOVRNQRZQ DVEXONRSHUDWLRQV7KHIROORZLQJFRGHH[DPSOHVGHPRQVWUDWHKRZWRH[HFXWHDEXON RSHUDWLRQLQERWK'$2DQG$'2 '$2

Sub DAOExecuteBulkOpQuery() Dim db

As DAO.Database

’ Open the database Set db = DBEngine.OpenDatabase("C:\nwind.mdb") ’ Execute the query db.Execute "Update Customers Set Country = ’United States’ " & _ "WHERE Country = ’USA’" Debug.Print "Records Affected = " & db.RecordsAffected ’ Close the database db.Close End Sub $'2 Sub ADOExecuteBulkOpQuery() Dim cnn

As New ADODB.Connection

Dim iAffected

As Integer

’ Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;" ’ Execute the query cnn.Execute "Update Customers Set Country = ’United States’ " & _ "WHERE Country = ’USA’", iAffected, adExecuteNoRecords Debug.Print "Records Affected = " & iAffected

’Close the connection cnn.Close End Sub 8QOLNH'$2ZKLFKKDVWZRPHWKRGVIRUH[HFXWLQJ64/VWDWHPHQWV2SHQ5HFRUGVHW DQG([HFXWH $'2KDVDVLQJOHPHWKRG([HFXWH WKDWH[HFXWHVURZUHWXUQLQJDVZHOO DVEXONRSHUDWLRQV,QWKH$'2H[DPSOHWKHFRQVWDQWDG([HFXWH1R5HFRUGV LQGLFDWHVWKDWWKH64/VWDWHPHQWLVQRQURZUHWXUQLQJ,IWKLVFRQVWDQWLVRPLWWHGWKH $'2FRGHZLOOVWLOOH[HFXWHVXFFHVVIXOO\EXW\RXZLOOSD\DSHUIRUPDQFHSHQDOW\:KHQ DG([HFXWH1R5HFRUGVLVQRWVSHFLILHG$'2ZLOOFUHDWHD5HFRUGVHWREMHFWDVWKH UHWXUQYDOXHIRUWKH([HFXWHPHWKRG&UHDWLQJWKLVREMHFWLVXQQHFHVVDU\RYHUKHDGLI WKHVWDWHPHQWGRHVQRWUHWXUQUHFRUGVDQGVKRXOGEHDYRLGHGE\VSHFLI\LQJ DG([HFXWH1R5HFRUGVZKHQ\RXNQRZWKDWWKHVWDWHPHQWLVQRQURZUHWXUQLQJ

&UHDWLQJDQG9LHZLQJ'DWDEDVH 6FKHPD &UHDWLQJD'DWDEDVH

7KHIROORZLQJFRGHFUHDWHVDQGRSHQVDQHZ0LFURVRIW-HWGDWDEDVH '$2

Sub DAOCreateDatabase() Dim db

As DAO.Database

Set db = DBEngine.CreateDatabase("C:\new.mdb", dbLangGeneral) End Sub $'2; Sub ADOCreateDatabase() Dim cat

As New ADOX.Catalog

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\new.mdb;" End Sub ,QWKH'$2FRGHDERYHWKH/RFDOHSDUDPHWHULVVSHFLILHGDVGE/DQJ*HQHUDO ,QWKH $'2;FRGHORFDOHLVQRWH[SOLFLWO\VSHFLILHG7KHGHIDXOWORFDOHIRUWKH-HW3URYLGHULV HTXLYDOHQWWRGE/DQJ*HQHUDO 8VHWKH$'2/RFDOH,GHQWLILHUSURSHUW\WRVSHFLI\D GLIIHUHQWORFDOH

,Q'$2&UHDWH'DWDEDVHDOVRFDQWDNHDWKLUG2SWLRQVSDUDPHWHUVSHFLI\LQJ LQIRUPDWLRQIRUHQFU\WLRQDQGGDWDEDVHYHUVLRQ)RUH[DPSOHWKHIROORZLQJOLQHLVXVHG WRFUHDWHDQHQFU\SWHGYHUVLRQ0LFURVRIW-HWGDWDEDVH Set db = DBEngine.CreateDatabase("C:\new.mdb", dbLangGeneral, _ dbEncrypt + dbVersion11) ,Q$'2HQFU\SWLRQDQGGDWDEDVHYHUVLRQLQIRUPDWLRQLVVSHFLILHGE\SURYLGHUVSHFLILF SURSHUWLHV:LWKWKH-HW3URYLGHUXVHWKH(QFU\SW'DWDEDVHDQG(QJLQH7\SH SURSHUWLHVUHVSHFWLYHO\7KHIROORZLQJOLQHRIFRGHVSHFLILHVWKHVHYDOXHVLQWKH FRQQHFWLRQVWULQJWRFUHDWHDQHQFU\SWHGYHUVLRQ0LFURVRIW-HWGDWDEDVH cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\new.mdb;" & _ "Jet OLEDB:Encrypt Database=True;" & _

5HWULHYLQJ6FKHPD,QIRUPDWLRQ "Jet OLEDB:Engine Type=2;"

%RWK'$2DQG$'2;FRQWDLQFROOHFWLRQVRIREMHFWVWKDWFDQEHXVHGWRUHWULHYH LQIRUPDWLRQDERXWWKHGDWDEDVH VVFKHPD,QIRUPDWLRQDERXWWKHVFKHPDFDQEH UHWULHYHGUHODWLYHO\HDVLO\E\LWHUDWLQJWKURXJKWKHREMHFWVLQHDFKRIWKHFROOHFWLRQV

7KHIROORZLQJFRGHGHPRQVWUDWHVKRZWRSULQWWKHQDPHRIHYHU\WDEOHLQWKHGDWDEDVH E\ORRSLQJWKURXJKWKH'$27DEOH'HIVFROOHFWLRQDQGWKH$'2;7DEOHVFROOHFWLRQ '$2

Sub DAOListTables() Dim db

As DAO.Database

Dim tbl

As DAO.TableDef

’ Open the database Set db = DBEngine.OpenDatabase("C:\nwind.mdb") ’ Loop through the tables in the database and print their name For Each tbl In db.TableDefs Debug.Print tbl.Name Next End Sub $'2; Sub ADOListTables() Dim cat

As New ADOX.Catalog

Dim tbl

As ADOX.Table

’ Open the catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\nwind.mdb;" ’ Loop through the tables in the database and print their name For Each tbl In cat.Tables If tbl.Type "VIEW" Then Debug.Print tbl.Name Next End Sub :LWK'$2WKH7DEOH'HIREMHFWUHSUHVHQWVDWDEOHLQWKHGDWDEDVHDQGWKH7DEOH'HIV FROOHFWLRQFRQWDLQVD7DEOH'HIREMHFWIRUHDFKWDEOHLQWKHGDWDEDVH7KLVLVVLPLODUWR $'2LQZKLFKWKH7DEOHREMHFWUHSUHVHQWVDWDEOHDQGWKH7DEOHVFROOHFWLRQFRQWDLQV DOOWKHWDEOHV

+RZHYHUXQOLNH'$2WKH$'27DEOHVFROOHFWLRQPD\FRQWDLQ7DEOHREMHFWVWKDW DUHQ WDFWXDOWDEOHVLQ\RXU0LFURVRIW-HWGDWDEDVH)RUH[DPSOHURZUHWXUQLQJQRQ SDUDPHWHUL]HG0LFURVRIW-HWTXHULHVFRQVLGHUHG9LHZVLQ$'2 DUHDOVRLQFOXGHGLQ WKH7DEOHVFROOHFWLRQ7RGHWHUPLQHZKHWKHURUQRWWKH7DEOHREMHFWUHSUHVHQWVD WDEOHLQWKHGDWDEDVHXVHWKH7\SHSURSHUW\7KHIROORZLQJWDEOHOLVWVWKHSRVVLEOH YDOXHVIRUWKH7\SHSURSHUW\ZKHQXVLQJ$'2ZLWKWKH-HW3URYLGHU 7\SH

'HVFULSWLRQ

/,1.

7KH7DEOHLVDOLQNHGWDEOHIURPDQRQ2'%&GDWDVRXUFH

$&&(667$%/(

7KH7DEOHLVDQ$FFHVVV\VWHPWDEOH

3$667+528*+

7KH7DEOHLVDOLQNHGWDEOHIURPDQ2'%&GDWDVRXUFH

6