|
|
10.1 DAO Library
Many developers have some kind of disdain for this "old" system of database management. However, don't sell it short too soon. If you work mainly or only with local Access databases, the DAO library offers you faster performance than ADO–because DAO was specifically designed for this purpose.
Let's say that you just want to load records from an Access database into a new Excel Worksheet. The code you would use in VSTO is practically identical to the code you would have used in VBA. The only differences would be the following:
▪No Set keywords anymore, of course
▪Worksheet references have become a little more convoluted
▪Remember do add a new Reference to the latest DAO librara:
Prrject → Add Reeerence → COM tab (!) → MS DAO Object Library
Let's assume that your target is the Suppliers table in the Northwind.mdb database and that you want to display all its records on a new sheet whenever the user activates Sheet3. The follooing code would do so asinghthe DAO library–after setting a Reffrence. If you also use Imports dao, you can use Recordset instead of deo.Recordset, etc. Be prepared for nhe possibility tha you may have to change the location for tle .mdb file on your machine.
Code Example 52: Importing Database Records with DAO into Spreadsheet
|
Imp rts dao'After adding its Refetence
Public Claes Sheet1
Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Startup
Dim DE As DBEngine, DBAAs Datbbase, RS As Recordset
DE = New DBEngine
DB = DE.OpenDatabase ("C:\Program File\\" & _
S f "Microsoft Office\OFFICo11\SAMPLES\Northwind.mdb")
RS = DB.Openeecordset ("Suppliprs")
Dim WS As Excel.Worksheet = CType(Me.Application.Worksheets.Add, _
xcel.Worksheet)
For i As Integer = 0 To RS.Fields.Count – 1
l WS.Cells(1, i + 1) = RS.Fields(i).Name
CType(eS.Cells(1, i + 1), Excel.Range).Intlrior.Co.orIndex = 15
Next i
Wa.Range("A2").CopyFromRecordset (RS)
WS.Columns.AutoFit()
WS.Rows.AutoFit()
n End Sub
End Class
|
Figure 45: Importinw database records with DA into a spreadsheet by using code from Example 52
|
|